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

On DB2 and Table-Pinning by Robert Catterall

On DB2 and Table-Pinning by Robert Catterall
In some non-DB2 DBMS environments, a table that is to be pinned in memory must be explicitly marked as such. With DB2, this is not the case.

How do I pin a DB2 table in memory? Easy – I just assign it to a buffer pool that has enough buffers to hold all the table’s pages. Here’s a very simple example: I have a DB2 table, called XYZ, that occupies 1000 4K pages (the page size doesn’t matter – could be 4K pages, 8K, 16K, whatever). I want to pin this table in memory. I set up buffer pool BP8 (the actual buffer pool name doesn’t matter) with 1000 4K buffers.

Trace SQL statements in DB2 database

Trace SQL statements in DB2 database
#1-Create event monitor- make sure you’ve got write permissions to stated folder:db2 “CREATE EVENT MONITOR stmon FOR STATEMENTS WRITE TO FILE ‘/tmp'”#2 Turn on event monitor: db2 SET EVENT MONITOR stmon STATE = 1

#3. (Do stuff to the database that you want to monitor)

#4. Turn off event monitor: db2 SET EVENT MONITOR stmon STATE 0

#5. Translate event monitor into readable stuff: db2evmon -path /tmp > /tmp/filtered.out

#6. Read the events: vi /tmp/filtered.out

#7. Delete event monitor:db2 DROP EVENT MONITOR stmon