DB2 to UDB

The best way to transfer data between mainframe DB2 and UDB on Unix or Linux is to use the UDB native features for data movement. Sometimes these features may not be used (i.e. the mainframe and the Unix system are bot physically connected) and therefore the XFRAME utilities for data migration may be a good solution for the data transfer.

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 UDB workspace

In order to replicate DB2 DB you have to create user and 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.

Submit all *.sql file produced by xddlconv utility to DB2 using the user just created. Now all table are replicated empty under UDB 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

ASCII files must be loaded in the databases with the UDB db2 command .For more detailed information about load see UDB Guide.

Examples

Source DDL

CREATE TABLE    SCHEMA.TABLE
  (IDESNELAV DECIMAL(6)                         NOT NULL,
   NUMPAG DECIMAL(4)                            NOT NULL,
   INIDDVPAG DECIMAL(10)                        NOT NULL,
   FINDDVPAG DECIMAL(10)                        NOT NULL)
      IN LZPNC30A.TC30A066
      AUDIT NONE
      CCSID EBCDIC;
COMMENT ON TABLE SCHEMA.TABLE IS 'This is a comment';
ALTER TABLE SCHEMA.TABLE FOREIGN KEY C30SNE18
     (IDESNELAV                 )
      REFERENCES SCHEMA.TABLE1
     (IDESNELAV                 )
      ON DELETE CASCADE;

DDL

DROP TABLE SCHEMA.TABLE;
CREATE TABLE SCHEMA.TABLE (
      IDESNELAV                DECIMAL (6, 0) NOT NULL  ,
      NUMPAG                   DECIMAL (4, 0) NOT NULL  ,
      INIDDVPAG                DECIMAL (10, 0) NOT NULL  ,
      FINDDVPAG                DECIMAL (10, 0) NOT NULL
);

Index file

ALTER TABLE SCHEMA.TABLE ADD CONSTRAINT SCHEMA.TABLE_C30SNE18 FOREIGN KEY (
       IDESNELAV
) REFERENCES SCHEMA.DSNELAV (
       IDESNELAV
) ON DELETE CASCADE;

Copy

      01  TABLE.
          03  IDESNELAV           PIC S9(6) COMP-3.
          03  NUMPAG              PIC S9(4) COMP-3.
          03  INIDDVPAG           PIC S9(10) COMP-3.
          03  FINDDVPAG           PIC S9(10) COMP-3.

Load

--
-- generated by xddlconv $Revision: 1.20 $
-- on Wed Oct 12 12:46:52 MDT 2005
--
CONNECT TO @DBNAME USER @USER USING @PASSWORD;
LOAD @CLIENT FROM "@PATH_TABLESCHEMA.TABLE.ASC" OF ASC
MODIFIED BY BINARYNUMERICS NULLINDCHAR=? PACKEDDECIMAL RECLEN=19 STRIPTBLANKS
METHOD L ( 1 4, 5 7, 8 13, 14 19)
NULL INDICATORS ( 0, 0, 0, 0)
MESSAGES "@PATH_LOGSCHEMA.TABLE.log
REPLACE INTO SCHEMA.TABLE
( IDESNELAV, NUMPAG, INIDDVPAG, FINDDVPAG)
COPY NO INDEXING MODE AUTOSELECT;
CONNECT RESET;
;