Donnerstag, 17 of April of 2014

Category » code for Linux/Unix/Windows

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:

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

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.

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

SQL playing with user id’s

Does your application has to check cerrtain authorzation id’s for certain access rights needed? Following SQL shows some ways how to proceed:

In this sample the SQL’s have to check incoming userid’s for having ..

  • L0nnnnnn or F0nnnnnn
  • where n=0-9
  • except first letter should not have any other letter or special character

Any comment? Do not hesitate to provide YOUR solution! Send your suggestions to!

Shell to send mails via sendmail

The following script references “Perl module – function package used by DBA Perl scripts”

The following very simple script is just a small frame for sending mail via network from command level or as a service for other scripts.

For more information how to send emails via sendmail (on AIX) see here: “Fun with Sendmail on AIX” (IBM developerWorks 2010, PDF).


Shell script to execute commands at all DB2 instances

Do you need to execute one ore more commands at all of your DB2 instances? Then you should deploy!

This script …

  • locates all DB2 instances using profiles.reg (DB2 9.1) or global.reg (9.7 or later)
  • executes commands passed as arguments

(Some comments are in german language. If you need assistence, please contact!)

This script may helps administrators to …

  • first stop all application services
  • then stop all DB2 instances
  • to do their maintenance, e.g. full system backups, hardware or software maintenance
  • use it in rc2.d to start/stop DB2, so help starting/stopping a server

Starting and stopping an CLI and JDBC trace in a shell environment




Perl Script to generate useful tablespace statements provides following functions:

  • creates CREATE TABLESPACE statements for existing database
  • creates REDIRECTED RESTORE statements for a database you possibly want to or need to recover at a remote site
  • creates ALTER TABLESPACE statements to reduce tablespace size and/or to lower the high water mark. Action depends on type of tablespace: Automatic storage, SMS and DMS tablespaces are supported.

Options supported: (Invoke script w/o parms will show all possible parameters)

[perl] -d db [-u parm -p parm] -f file [-t {RESTORE|CREATE|AUTOSIZE} -o {CURRENT|HWM}] [-g nn] [-s pattern] [-ns pattern]

  • -d database
  • -u User (opt. if exists in environment 1)
  • -p Password (opt. if exists in environment 1)
  • -f output file receiving all the generated statements
  • -t type of statement to generate (opt)
  • -o current allocation or HWM (High Water Mark) (opt.)
  • -g fill-meter (AUTOSIZE target usage %) or growth (additional uplift % on HWM) (opt.)
  • -s tablespace name pattern (opt.)
  • -ns single tablespace name exclude pattern (opt.),

Using patterns, examples and usage comments:

  • -ns patt Do not use replace chars like ‘*’ or ‘%’\n”.
  • -s ‘patt1|patt2′ Imbed multiple patterns in (double) quotes
  • -ns patt1 -s patt2 Paired patterns work like OR condition

1 See library for details on how to optionally deposit user and password, used in perl scripts, in user’s profile.


Perl Script to list or terminate DB2 connections provides following functions …

  • lists all databases (local and remote) at current DB2 instance
  • lists all connections at the DB2 instance, or only those connected to a specified database
  • terminates all connections, or only those connected to a specified database, or a specific thread id

Options supported:

  • -l: (deflt. option) display all threads
  • -d {dbname|*}: display threads on distinct or all databases
  • -t {id|*}: terminate distinct thread id or all threads (on database or instance)
  • -a LOCKS: display locks