ruban.de: Loading DB2 z/OS data into DB2 LUW in internal format

ruban.de: Loading DB2 z/OS data into DB2 LUW in internal format

Loading data of DB2 z/OS internal format into a DB2 Linux/Unix/Windows database became very easy.

Either you use DB2 Connect to connect from any DB2 Linux/Unix/Windows to DB2 for z/OS. But this costs you a separate license fee. For a distinct class of DB2 LUW server (e.g. on AIX) IBM supplies the DB2 Connect Enteprise license, which costs even more.
Or just use existing infrastructure components: UNLOAD or DSNTIAUL your data at z/OS, transport (e.g. SCP, SFTP ) your data in binary format and LOAD, IMPORT or INGEST data at DB2 for Linux/Unix/Windows. Following sample will show you how it works.
Assume following DB2 structrure of a sample table:

DB2 DDL of sample table used in this exampleDefault

CREATE TABLE TB_SAMPLE (                                               
   LOCATION    DECIMAL(5) NOT NULL                        
  ,CHANGEDTS   TIMESTAMP NOT NULL WITH DEFAULT            
  ,DB2_USERID  CHAR(8) NOT NULL WITH DEFAULT       
  ,POSNR       DECIMAL(1) NOT NULL                 
  ,ZIP         CHAR(8) NOT NULL WITH DEFAULT        
  ,SUBCODE     CHAR(10)                           
  ,LOCNAME     VARCHAR(28) NOT NULL                   
  ,LOCFRACTION VARCHAR(28) NOT NULL WITH DEFAULT  
  ,STREET      VARCHAR(28) NOT NULL                   
  ,VERSION     SMALLINT NOT NULL WITH DEFAULT        
);

Then unload your DB2 z/OS data using DSNTIAUL. SYSPUNCH will show you the columns locations you will need later:

SYSPUNCH created by DSNTIAULDefault

LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE                            
    QUALIFR.TB_SAMPLE                                                    
 (                                                                    
 LOCATION        POSITION(       1:       3)    DECIMAL, 
 CHANGEDTS       POSITION(       4         )    TIMESTAMP EXTERNAL(26),
 DB2_USERID      POSITION(      30         )    CHAR(   8),
 POSNR           POSITION(      38:      38)    DECIMAL,
 ZIP             POSITION(      39         )    CHAR(   8),
 SUBCODE         POSITION(      47         )    CHAR(  10)   
                 NULLIF(      57)='?',  
 LOCNAME         POSITION(      58         )    VARCHAR,
 LOCFRACTION     POSITION(      88         )    VARCHAR,
 STREET          POSITION(     118         )    VARCHAR,
 VERSION         POSITION(     148         )    SMALLINT
 )
/* 
//*(SYSPUNCH output edited for better reading)

Then transport SYSRECnn dataset to Linux/Unix/Windows in binary format. Use SCP or SFTP for example.

Then LOAD sample data into your DB2 Linux/Unix/Windows database. Specifiy codepage of origin, e.g. 819 or 237. Also specify optional keywords BINARYNUMERICS and PACKEDDECIMAL to allow loading data in IBM proprietary data formats. If you are using NULL indicators you have to specify as NULLINDCHAR.

Positional LOAD considering codeopageDefault

LOAD FROM /home/myuser/in/TB_SAMPLE.asc OF ASC
     MODIFIED BY CODEPAGE=819
     RECLEN=150 NULLINDCHAR=? STRIPTBLANKS
     BINARYNUMERICS
     NOCHECKLENGTHS 
     PACKEDDECIMAL
     METHOD L ( 1  3,
                4  29,              
                30 37,              
                38 38,
                39 46,
                47 56,
                60 77,       
                90 117, 
                120 147, 
                148 149) 
     NULL INDICATORS (0,0,0,0,0,57,0,0,0,0)
     REPLACE INTO TB_SAMPLE
   --FOR EXCEPTION TB_SAMPLE_EXCP
     STATISTICS USE PROFILE
     NONRECOVERABLE
;

Restrictions may exist for CLOB, XML and other ‚advanced‘ data types.

27. März 2014 / code for Linux/Unix/Windows, DB2 for Linux/Unix/Windows, DB2 for z/OS, z/OS

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s