Loading DB2 z/OS data into DB2 LUW in internal format 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                        
  ,POSNR       DECIMAL(1) NOT NULL                 
  ,ZIP         CHAR(8) NOT NULL WITH DEFAULT        
  ,SUBCODE     CHAR(10)                           
  ,LOCNAME     VARCHAR(28) NOT NULL                   
  ,STREET      VARCHAR(28) NOT NULL                   

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                            
 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
     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)

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

Stayin’ alive: Ten years of Linux on the mainframe • The Register

Stayin’ alive: Ten years of Linux on the mainframe • The Register
Just to show you how different IBM was back then, the company had $41.6bn in hardware sales that year, and pushed PCs, printers, disks, minicomputers, telecom and networking gear, retail systems – you name it. And those mainframes had wickedly high profit margins and drove mainframe software sales where nearly all the money dropped to the bottom line – and helped pay the bill for all of those unprofitable hardware businesses, by the way.

DB2 packages: Concepts, examples, and common problems by John Chun + Paolo Cirone

DB2 packages: Concepts, examples, and common problems by John Chun + Paolo Cirone
What are packages?Packages in DB2 are control-structure database objects that contain executable forms of SQL statements or placement holders for executable forms. In DB2 for Linux©, UNIX©, and Windows©, packages may also be referred to as access plans. Packages are stored in the database system catalog tables.

If an application intends to access a database using static SQL, the application developer must embed the appropriate SQL statements in the program source code. When the program source code is converted to an executable object (static SQL) or executed (dynamic SQL), the strategy for executing each embedded SQL statement is stored in a package as a single section. Each section is a bound form of the embedded SQL statement, and this form contains information such as which index to use and how to use the index (basically an access plan).

DB2 packages are used to execute SQL statements on the database server.

DB2 ” Honza’s perspective

DB2 ” Honza’s perspective
The whole problem is in the format of the passwords in /etc/shadow. DB2 doesn’t seem to like the passwords generated when changing password using the standard passwd command. In Fedora Core 10 the password is hashed using sha-512.

DB2 works fine with passwords hashed with standard crypt function. Password in the desired format can be obtained by calling openssl passwd desiredPassword. The output of openssl can be passed to usermod –password