JCL to find if a dataset is empty

In your batch job, if there is a need to check for empty dataset, you can do it using IDCAMS.

//*-------------------------------------------------------------------*/
//* This JCL is to find if a file is empty or not. If there are no    */
//* records, the return-code will be 4.                               */
//*-------------------------------------------------------------------*/
//STEP01   EXEC PGM=IDCAMS
//SYSPRINT  DD SYSOUT=*
//IN1       DD DISP=SHR,DSN=your.dataset.name
//SYSIN     DD *
  PRINT INFILE(IN1) COUNT(001)
/*

If you have ICETOOL at your site, you can use that as well.

//*-------------------------------------------------------------------*/
//* This JCL is to find if a file is empty or not using ICETOOL.      */
//* If there are no records, the return-code will be 12               */
//*-------------------------------------------------------------------*/
//STEP01  EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//IN       DD DISP=SHR,DSN=your.dataset.name
//TOOLIN   DD *
COUNT FROM(IN) EMPTY
/*

Gmail Tips

I know, this post has nothing to do with mainframes, but I just thought I would pass this around in case anyone finds it useful.

I've started using my gmail id very frequently because of all the good features it offers. However there are a few things that is missing from gmail. One of them is, you cannot sort the emails by Name, Subject, etc. But I found a way to do something to achieve similar results. Being a search giant, google seems to handle emails like another search item.

To sort on label (folder) "DB2", type label:DB2 in the search field and click on Search Mail

To sort on label (folder) "DB2" and Subject "DDL Lookup", type label:DB2 subject:DDL Lookup in the search field and click Search Mail

To get all emails from "Aji", type From:Aji in the search field and click Search Mail

UNIQUE WHERE NOT NULL - Explained

I always had confusion over the meaning of indexes created with UNIQUE WHERE NOT NULL clause on DB2 for z/OS. The manuals don't seem to do a good job explaining it. I understand it now and here is how it works.

It means its ok to have multiple rows with null values in columns included in the index, but its not ok to have rows where the combination of the columns included in the index is not unique and all the columns are not null. I tested this on a three column index. I was able to insert rows rows shown below just fine, the only duplicate exception I got was where column a, b, c were the same value for two different rows and none of the 3 columns contained a null value.

  COL1  COL2  COL3
  ----  ----  ----
   A     B     C
   A     B     -
   A     B     -
   A     -     -
   A     -     -
   -     -     -
   -     -     -

CREATE UNIQUE WHERE NOT NULL INDEX PRE.IDX1
       ON PRE.TEST
       (COL1   ASC
       ,COL2   ASC
       ,COL3   ASC)
       USING STOGROUP ASTERISK
             PRIQTY 12
             SECQTY 10800
             ERASE NO
       FREEPAGE 0
       PCTFREE 0
       BUFFERPOOL BP1
       CLOSE YES
       PIECESIZE 2097152  K;

CREATE TABLE PRE.TEST
       (COL1     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       ,COL2     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       ,COL3     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       )
       IN DB.TS;

SMP/E Errors

Well, I've been asked to install Omegamon XE for DB2 PM in our shop. I downloaded the required components from the IBM Shop z website and did a SMP/E RECEIVE. When I started the SMP/E APPLY (with CHECK option), I started getting all kinds of errors. I'm new to SMP/E, so I'll try to document what I find from my experience. It might be useful for those novices who have starting doing SMP/E installs.

These are the steps I follow when I install a product or apply maintenance(PTF) on an existing product:

1. If you are applying maintenance on an existing product, use ADRDSSU to backup ALL the datasets related to the product

2. Download the product CBDBO/PTF from IBM Shop z series website to your PC

3. FTP the product/PTF to mainframe

4. If the PTF is in tersed (IBM way of compressing) format, UNTERSE using TRSMAIN program with UNPACK option - if you need a sample job to run TRSMAIN, please put your request in the comment section

5. For new product installs, allocate SMP/E global zone and target/distribution datasets and all other related SMP/E datasets like SMPLOG, SMPPTS, SMPLTS, SMPMTS etc. Usually when you download the product,you'll get a sample job that allocates these datasets and defines them to the appropriate DD names. Go through the Program Directory to find info on the sample job. You may have to talk to your storage administrator about which volume to use for the new datasets.

6. Run SMP/E APPLY job with CHECK option (CHECK option ensures that you do not update any datasets. In our shop we usually install new product in the "base" libraries and all PTFs in the "maintenance" libraries. After installing thePTFs in the maintenance libraries, if everything goes well, we copy them to the base libraries. With the CHECK option you can see which datasets will be updated and if you do see any issue with updating a particular dataset, you can change the DDDEF - DD definition - in SMP/E and resubmit the job with CHECK option)

7. Run SMP/E APPLY job with CHECK option commented.

8. After a satisfactory testing, run SMP/E ACCEPT with CHECK option (this is in most cases not necessary unless you are going to back out any changes - in my little experience, if I've to backout a PTF, I just RESTORE all the datasets from the ADRSSU backup taken before applying the PTF)

Thanks for stopping by.

How to find if a COBOL program is looping, thru Mainview

If you want to know if a program is looping (on mainframe) and if you have Mainview for MVS, then
1. Go to JCPUR which shows realtime job CPU utilization
2. Place the cursor on the job you are interested in and hit enter
3. It'll take you to EZMJOBR window
4. Under SYSPROG Service, you'll find "MVScope CPU Tracing". Place your cursor there and hit enter
5. Then place your cursor on "Begin CPU Trace" and hit enter. It'll start CPU tracing.
6. Once it finishes, see if it shows any one particular statement. If so, it could mean, the program was in a loop during the trace period.
7. Get the Offset info, go to the compile listing and search for the Offset under HEXLOC (you may not find the exact offset, but the one just before this value is good enough). Get the line number and get the statement that was in loop.

Simple?

Copy Data from DB2 for z/OS to DB2 for LUW (UDB)

One of the applications developer in our shop wanted to know if he can copy data from a mainframe DB2 table to DB2 UDB on LUW. Though there are several ways of doing it, this is the one suggested him assuming the table structure in z/OS and LUW are the same.

1. Some of the tools that can be used to achieve this: Quest Central for DB2, Toad for DB2 or DB2CMD interface

2. Connect to the mainframe database (CONNECT TO ssid) and issue the EXPORT command "EXPORT TO d:\file_name.ixf OF IXF MESSAGES d:\msg_export.txt SELECT *FROM prefix.table_name"
Note: Don't forget to check the message file before moving onto the next step

3. Connect to the LUW database and issue the IMPORT command
"IMPORT FROM d:\file_name.ixf OF IXF MESSAGES d:\msg_import.txt INSERT INTO prefix.table_name"
Note: Don't forget to check the message file

It's that simple. But remember that this works well for relatively smaller tables. For larger tables, you may have to use the LOAD command with proper COMMIT COUNT.

SQL0805N on DB2 package SYSLH203

If you get a SQL0805N on package SYSLH203 then an application is holding a large number of statements open. If this is not an application bug you can bind more packages to allow for more statements open at the same time.

Depending on the type of statement you are executing, DB2 will use a particular package on the server. By default, DB2 creates three packages for each type of package. In this case NULLID.SYSLH2yy is reserved for statements with CURSORHOLD on and isolation level Cursor Stability. The package SYSLH203 means that DB2 is looking for the 4th package (200 is 1st, 201 is 2nd, etc) of this type, but it does not exist. You can create more packages on the server by connecting to the database and issuing the following bind command from the /sqllib/bnd directory:

db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 5

Note: CLIPKG 5 will create 5 large packages, and will give you the package that your application is looking for, as well as one more in this case. This setting only applies to large packages (containing 384 sections). The number of small packages (containing 64 sections) is 3 and cannot be changed.

To bind more CLI packages do the following:

• Find a machine that has the version and fixpack of DB2 that you want to bind. You can map to the machine you're going to bind on and then:
  - Go to the D:\SQLLIB\BND directory in the DB2 Command Line Processor (DB2CMD)
  - Look for the *.BND files in the D:\SQLLIB\BND directory
  - Connect to the host via DB2 connect statement like: db2 connect to dbalias user userid
  - X:\sqllib\bnd>db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 30 to bind the max of 30 CLI packages. Range is 4 to 30.

DB2 CREATE INDEX abended with S04E 00E70005

There is a couple of news items attached to this post:
1. Today is the first anniversary of this blog
2. This is the 50th post

Well, now into the actual blog item ...

My colleague was trying to drop an existing UNIQUE & CLUSTER index and recreate it after adding 3 new columns at the end of the table, with one of the new columns added to the index. However, the DDL failed with S04E reason code 00E70005.

DB2 messages and codes manual had this info on this reason code: "A relational data system (RDS) subcomponent internal inconsistency was detected". It didn't make any sense. We suspected data issue first, so we REORGed the tablespace, and tried to create the index, but that didn't fix the problem. Next, we tried to repair the DBD using this JCL:

//*===============================================
//* Start the database in UT mode
//*===============================================
//STEP0010   EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB    DD DSN=SYS2.DB2x.DSNLOAD,DISP=SHR
//SYSTSPRT   DD SYSOUT=*
//SYSPRINT   DD SYSOUT=*
//LISTING    DD SYSOUT=*
//SYSUDUMP   DD DUMMY
//SYSTSIN    DD *
  DSN SYSTEM(DB2x)
  -START DB(database) ACCESS(UT)
  END
/*
//*===============================================
//* Run REPAIR utility on the DBD
//*===============================================
//STEP020 EXEC PGM=DSNUTILB,PARM='DB2x,utilid'
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD  *
  REPAIR DBD REBUILD DATABASE database
/*
//*===============================================
//* Start the database in RW mode
//*===============================================
//STEP0030   EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB    DD DSN=SYS2.DB2.DB2x.DSNLOAD,DISP=SHR
//SYSTSPRT   DD SYSOUT=*
//SYSPRINT   DD SYSOUT=*
//LISTING    DD SYSOUT=*
//SYSUDUMP   DD DUMMY
//SYSTSIN    DD *
  DSN SYSTEM(DB2x)
  -START DB(database) ACCESS(RW)
  END
/*
 

Even that didn't fix the problem. We were not very keen to open a ticket with IBM, since we wanted to fix the problem fast rather than debugging it. So as a last resort we dropped and recreated the tablespace (off course, we had saved the data). That fixed the problem. We don't know what caused it, but the problem is fixed !! I really hate when I don't know what caused the problem, and the fix is based trial-and-error method.

sqloopen -2079391743 sqlcode -970

I bumped into this error message (sqloopen -2079391743) when I tried to run "EXPORT" utility on a DB2 UDB table on my AIX box. No SQLCODE displayed on the screen. I couldn't find much information on the net, however, when I learnt about the DB2DIAG command, it made life easy. I issued the following command:

db2diag -rc -2079391743

and I got exactly what I was looking for:

Input ZRC string '-2079391743' parsed as 0x840F0001 (-2079391743).

ZRC value to map: 0x840F0001 (-2079391743)

V7 Equivalent ZRC value: 0xFFFFC601 (-14847)

ZRC class : Non-Critical Media Error (Class Index: 4)

Component: SQLO ; oper system services (Component Index: 15)

Reason Code: 1 (0x0001)

Identifer: SQLO_ACCD
Identifer (without component): SQLZ_RC_ACCD

Description: Access Denied

Associated information: Sqlcode -970
SQL0970N The system attempted to write to a read-only file.
Number of sqlca tokens : 0
Diaglog message number: 8701

I changed the datapath in my EXPORT command to a folder in which I had write access and the command ran fine.

"DB2LOOK" to extract DDL from DB2 for z/OS

Many of you may be aware that "db2look" command can be used to extract DDL from DB2 UDB for LUW database. But do you know that you can use this command to extract DDL from a DB2 database for z/OS? Here is how you do it.

1. Bring up a db2cmd prompt (press Start button on your PC, click Run, type db2cmd and hit enter)
2. Connect to your mainframe DB2 subsystem using the command "db2 connectto SSID user USERID". It'll prompt for the password. Enter the password and hit enter
3. Issue the following command:
db2look -d SSID -i USERID -w PWD -e -a -l -x -p -o FILENAME.TXT

The options specified extracts the following info:

-e ==> Extract DDL statements for database objects. DDL for the followingdatabase objects are extracted when using the -e option: Tables, Views, Automatic summary tables (AST), Aliases, Indexes, Triggers, Sequences, User-defined distinct types, Primary key, referential integrity, and check constraints, User-defined structured types, User-defined functions, User-defined methods, User-defined transforms, Wrappers, Servers, User mappings, Nicknames, Type mappings, Function templates, Function mappings, Index specifications, Stored procedures

-a ==> When this option is specified the output is not limited to the objects created under a particular creator ID. All objects created by all users are considered. For example, if this option is specified with the -e option, DDL statements are extracted for all objects in the database. If this option is specified with the -m option, UPDATE statistics statements are extracted for all user created tables and indexes in the database.
Note: If neither -u nor -a is specified, the environment variable USER isused.

-l ==> If this option is specified, then the db2look utility will generate DDL for user defined table spaces, database partition groups and bufferpools.

-x ==> If this option is specified, the db2look utility will generate authorization DDL (GRANT statement, for example).

More info is available in the DB2 V8 LUW Command Reference: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsptopic=/com.ibm.db2.udb.doc/core/r0002051.htm