Step-by-Step Guide: Migrating DB2 v9.7 PHSDB to K-DB11G

Database Object Migration

The DB2 schema PHS is mapped to a K‑DB user with the same name. A full analysis of tables, primary keys, indexes, foreign keys, sequences, views, functions, column defaults and identity columns is performed with SQL Developer. Generated DDL scripts create the K‑DB user, tables, constraints, default values and grant the required system privileges ( CREATE SESSION, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE SYNONYM). Tablespaces use the user tablespace and temporary tables use the temp tablespace.

Data‑type conversion

The following diagram shows the mapping between DB2 and K‑DB data types:

Constraint conversion

DB2 supports both ON DELETE and ON UPDATE actions; K‑DB supports only ON DELETE. The migration tool rewrites constraints accordingly.

Example DB2 foreign‑key definition:

ALTER TABLE "PHS"."P_PACKAGE_GROUP"
ADD CONSTRAINT "FK2_P_PACKAGE_GROUP" FOREIGN KEY ("PACKAGEID")
REFERENCES "PHS"."P_PACKAGE" ("PACKAGEID")
ON UPDATE RESTRICT
ON DELETE RESTRICT;

Converted K‑DB syntax:

ALTER TABLE P_PACKAGE_GROUP
ADD CONSTRAINT P_PG_PACK_FK FOREIGN KEY (PACKAGEID)
REFERENCES P_PACKAGE (PACKAGEID)
ENABLE;

Column default conversion example:

-- DB2
ALTER TABLE "PHS"."P_INSTANCE" ALTER COLUMN "PERSONID" SET WITH DEFAULT -1 ;
ALTER TABLE "PHS"."CHRONIC_TEMPLATE" ALTER COLUMN "MEDEVENT" SET WITH DEFAULT 8 ;

-- K‑DB
ALTER TABLE "PHS"."CHRONIC_TEMPLATE" MODIFY "MEDEVENT" DEFAULT 8 ;
ALTER TABLE "PHS"."P_INSTANCE" MODIFY "PERSONID" DEFAULT -1 ;

Identity column emulation

DB2 identity columns are emulated in K‑DB using a sequence and a BEFORE INSERT trigger. The generated DDL creates the table, the sequence, and a trigger that assigns the next sequence value to the identity column, synchronising the sequence with the maximum existing ID on the first insert.

CREATE TABLE BATCH_TASK_RECORDS (
  ID NUMBER(10,0) NOT NULL,
  STATUS NUMBER(10,0),
  JOBSTARTTIME TIMESTAMP(9),
  JOBENDTIME TIMESTAMP(9),
  SDATE TIMESTAMP(9),
  EDATE TIMESTAMP(9),
  TYPE VARCHAR2(50 CHAR)
);

CREATE SEQUENCE BATCH_TASK_RECORDS_ID_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 NOCYCLE;

CREATE OR REPLACE TRIGGER BATCH_TASK_RECORDS_ID_TRG BEFORE INSERT ON BATCH_TASK_RECORDS
FOR EACH ROW
DECLARE
  v_newVal NUMBER(12) := 0;
  v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.ID IS NULL THEN
    SELECT BATCH_TASK_RECORDS_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    IF v_newVal = 1 THEN
      SELECT NVL(MAX(ID),0) INTO v_newVal FROM BATCH_TASK_RECORDS;
      v_newVal := v_newVal + 1;
      LOOP
        EXIT WHEN v_incval >= v_newVal;
        SELECT BATCH_TASK_RECORDS_ID_SEQ.NEXTVAL INTO v_incval FROM DUAL;
      END LOOP;
    END IF;
    db2_utilities.identity := v_newVal;
    :new.ID := v_newVal;
  END IF;
END;

Sequences, views and functions

Objects such as sequences, views and functions cannot be automatically converted. They must be exported with IBM IDMT, then manually rewritten to K‑DB syntax. An example of a sequence conversion is shown below:

After generating the K‑DB DDL scripts, they are executed sequentially on the target database to complete the object migration.

Data Migration

Data migration is performed with two shell scripts: Db2_data.sh – exports data from DB2 using the db2 export command with custom column and row delimiters. Import_kdb.sh – loads the exported files into K‑DB with tbloader.

Export example (DB2):

db2 export to data/PHS_APPROLE.TXT of DEL modified by coldel"#" datesiso nochardel \
"select \"ID\",'<EOFD>',\"ROLENAME\','<EOFD>',\"DESCRIPTION\', '<EORD>' from PHS.\"APPROLE\""

# Additional export commands for other tables follow the same pattern.

The import script invokes tbloader with control files generated by batch_ctl.sh:

tbloader userid=$username/$password@$tnsname control=control/PHS.APPROLE.ctl log=log/PHS.APPROLE.log

batch_ctl.sh

scans the target K‑DB schema and creates a control file for each table. Example control file for PHS.APPROLE:

# load data by tbloader
load data
infile 'PHS.APPROLE.dat'
logfile 'log/PHS.APPROLE.log'
badfile 'log/PHS.APPROLE.bad'
append
into table PHS.APPROLE
fields terminated by '#<EOFD>#'
LINES TERMINATED BY '<EORD>
'
(
  ID,
  ROLENAME,
  DESCRIPTION
)

Before loading, foreign‑key constraints are disabled and nullable columns are allowed using helper scripts ( Pro_load.sh and Modify_null.sh). After the load, constraints are re‑enabled and NOT‑NULL attributes are restored with Post_load.sh and Modify_not_null.sh. Finally, gather_data.sh validates row counts and data integrity to confirm a successful migration.

When all scripts have run without errors, the migration from DB2 v9.7 (PHSDB) to K‑DB 11g is complete.