Databases - IBDataPump Analysis

This is a partial analysis related to importing an MS Access database into Firebird using the IBDataPump.

In this analysis, I have added spaces and made other editorial changes to improve readability.

Basic Table Conversion

This worked pretty good - no complaints.

SET SQL DIALECT 3;

/* Original table name is "Batch_Preparation_tbl" */
CREATE TABLE "Batch_Preparation_tbl" (
  "ID"           INTEGER NOT NULL /* "ID"           */ ,
  "Batch_ID"     INTEGER          /* "Batch_ID"     */ ,
  "SOP_Step"     VARCHAR(10)      /* "SOP_Step"     */ ,
  "Performed_By" CHAR(5)          /* "Performed_By" */ ,
  "DateTime"     TIMESTAMP        /* "Date/Time"    */ ,
  "Comments"     BLOB SUB_TYPE 1  /* "Comments"     */
);

Indices

Indices are a general problem

Generated names tend to be too long (more than 31 characters)
Some of the indices don't make sense

In MS Access, the Batch_Preparation_tbl table had only 2 indices

Batch_ID - used to improve speed
PrimaryKey - unique record ID

Most MS Access tables contain a PrimaryKey index ... mainly because Access prompts you to create one each time you create a table. As a result, you will get multiple

    /* !  Warning: Duplicated Index Name Found - PrimaryKey , 
          Replaced To "tablename_PrimaryKey" */

warnings when you run IBDataPump. When these are too long (more than 31 characters), you will need to manually shorten them. (I provide a program to semiautomate this task.) This example is after running Shorten_SQL_Names.exe and manually removing the extra comment delimiters.

    /* Indexes for table "Batch_Preparation_tbl" */
    /* Found Index "PrimaryKey", Options [ixPrimary,ixUnique] */
    /* !  Warning: Duplicated Index Name Found - PrimaryKey , 
          Replaced To "Batch_Preparation_tbl_PrimKey" 
      shortened from - Batch_Preparation_tbl_PrimaryKey  */

    ALTER TABLE "Batch_Preparation_tbl" ADD CONSTRAINT "Batch_Preparation_tbl_PrimKey" 
          /* shortened from - Batch_Preparation_tbl_PrimaryKey */ PRIMARY KEY ("ID");
    /* Found Index "Batch_ID", Options [] */
    CREATE INDEX "Batch_ID" ON "Batch_Preparation_tbl" "Batch_ID";
    /* Found Index "Batches_tblBatch_Preparation_tbl", Options [] */
    CREATE INDEX "Batches_tblBatch_Preparation_tbl" ON "Batch_Preparation_tbl" "Batch_ID";

I see no reason for the Batches_tblBatch_Preparation_tbl index.
(Perhaps that is just my ignorance showing.)

Both Batch_ID and Performed_By are associated with entries in other tables ... but those associations are not included in the generated sql file.

Referential integrity is defined between Batches_tbl and Batch_Preparation_tbl ... perhaps that produced the Batches_tblBatch_Preparation_tbl index.

Generator

The "ID" field is defined in MS Access as autoincrement - thus the datapump automatically produced a generator.

The current maximum ID value was 4 - so the generator was properly initialized.

    /* Generators for AutoInc fields for table "Batch_Preparation_tbl" */
    /* SELECT  max(Batch_Preparation_tbl.ID) FROM Batch_Preparation_tbl */

    CREATE GENERATOR "GEN_Batch_Preparation_tbl_ID";
       SET GENERATOR "GEN_Batch_Preparation_tbl_ID" TO 4;

    SET TERM ^;
    CREATE TRIGGER "TRIG_Batch_Preparation_tbl_BI" FOR "Batch_Preparation_tbl" BEFORE INSERT
    AS BEGIN
      IF(NEW."ID" IS NULL) THEN NEW."ID" = GEN_ID("GEN_Batch_Preparation_tbl_ID",1);
    END ^
    SET TERM ;^

Notice that the trigger is not marked as active ... this allows the datapump to copy existing records to the table without calling this function. However, because of the null test, there would not be a big problem.

Author: Robert Clemenzi - clemenzi@cpcug.org