DB2 to ORACLE

Encoding data conversion is defined as the changing of character-set in data from EBCDIC to ASCII. The easiest way to perform this task for DB2 EBCDIC data is download its in flat file and converts its as common EBCDIC flat file through programs conversion. At the end of conversion reload ASCII data on Oracle database. In order to replicate DB2 objects schema is mandatory obtain from customer the up-to-date DDLs. xddlconv analyzing ddls produce 3 different source.

Analyze DDLs

DDLs have to be processed by xddlconv utility. See more detailed information in XCONV book.

Create Oracle workspace

In order to replicate DB2 DB you have to create user and tablespace. In appendix B can find an example how to create a oracle user and related tablespace. Tablespaces size depends on amount of data. After tablespace and user definition, tables have to be created. In order to upload data quickly and without hierarchy priority problem we suggest to create index at the end of uploading step. In fact xddlconv utility separates index and table source.

Process all *.sql file produced by xddlconv utility to sqlplus using user created.

sqlplus MYSQL/MYSQL@MYDB @tabella.sql

Now all table are replicated empty under Oracle DB. Aftert data conversion you can proceed with upload step.

Data conversion

Data files coming from mainframe DB2, downloaded according to the precedure described in the previous chapter, are flat file and therefore they must be converted as any other sequential file.

For this reason xddlconv generates the COBOL copybooks that represent the record structure of the files coming from the mainframe. These copybooks must then be processed by cpy2xml and then by xmlconverter to generate all the necessary conversion programs, to be compiled and run to get the converted file.

Please refer to the file migration guide to get more information about flat file translation. Just remember that  copybooks generated by xddlconv have no redefined structures or overlapping data: therefore data conversion for these will be 100% automatic, without any manual intervention.

As example, let's suppose TABLE1.cpy being generated by xddlconv for table TABLE1. You have to build the XML descriptor for the table and to produce the COBOL conversion program.

cpy2xml -o TABLE1.xml TABLE.cpy
xmlconverter -o TABLE1.cbl TABLE1.xml

Now you have to compile TABLE1.cbl with your COBOL compiler and run it, according to the instructions provided in the File Migration guide.

Data upload

Once file is converted from EBCDIC to ASCII it must be uploaded in the database with the sqlldr Oracle utility. To perform this step you need the control files generated by xddlconv step. Perform this command from directory where ASCII files were created:

sqlldr userid=MYSQL/MYSQL@MYDB control=$CONTROL_PATH/TABELLA.ctl

For more detailed information about sqlldr see Oracle Utilities Guide.

Examples

Source DDL

CREATE TABLE SCHEMA.TABLE1
   (
     NAME                    CHAR(3) NOT NULL,
     SURNAME                 CHAR(2) NOT NULL,
     ADDRESS                 CHAR(35) NOT NULL,
     STATE                   CHAR(34) NOT NULL,
     ZIPCODE                 DECIMAL(5, 0) NOT NULL,
     BIRTHDAY                DATE NOT NULL
   )

Target DDL

DROP TABLE TABLE1;
CREATE TABLE TABLE1 (
            NAME                     CHAR (3)  NOT NULL ,
            SURNAME                  CHAR (2)  NOT NULL ,
            ADDRESS                  CHAR (35)  NOT NULL ,
            STATE                    CHAR (34)  NOT NULL ,
            ZIPCODE                  DECIMAL (5, 0)  NOT NULL ,
            BIRTHDAY                 DATE   NOT NULL
);
COMMIT;
EXIT;

COBOL Copybook

  01  TABLE1.
      03  NAME                          PIC X(3).
      03  SURNAME                       PIC X(2).
      03  ADDRESS                       PIC X(35).
      03  STATE                         PIC X(34).
      03  ZIPCODE                       PIC S9(5) COMP-3.
      03  BIRTHDAY                      PIC X(10).

Control file

--
-- generated by xddlconv $Revision: 1.20 $
-- on Wed Oct 19 16:25:43 MEST 2005
--
LOAD DATA
INFILE 'TABLE1.ASC'  " fix 87 "
BADFILE 'TABLE1.BAD'
REPLACE
PRESERVE BLANKS
INTO TABLE TABLE1
(
NAME      POSITION (    1     ) CHAR       (    3)  ,
SURNAME   POSITION (    4     ) CHAR       (    2)  ,
ADDRESS   POSITION (    6     ) CHAR       (   35)  ,
STATE     POSITION (   41     ) CHAR       (   34)  ,
ZIPCODE   POSITION (   75     ) DECIMAL    ( 5, 0)  ,
BIRTHDAY  POSITION (   78     ) DATE  'YYYY-MM-DD'  )