Accessing DB2 LUW table through a COBOL program on mainframe

If you are planning to write a COBOL program to access data from a table on DB2 LUW, you need to do the right setup first. I've described them here as much as I remember. If you get into any trouble, let me know, we can try to resolve it together.

Things to do on Mainframe DB2

Insert row in SYSIBM.LOCATIONS with these values

• LOCATION - dbname <-- This is the DB2 LUW database name

• LINKNAME - linkname <-- This is parent of a row in SYSIBM.IPNAMES or SYSIBM.LUNAMES (For this to work it needs to be the name of the DB on the UDB box, because this is how it determines which DB to connect to if that DB2 instance has more than one DB under it.)

• IBMREQD - N

• PORT - portnum <-- This is the connection port on the UDB box. To find this on AIX do: "cat /etc/services". The end of the file will have the connection port for the DB2 instance. On NT it will be 50000.

• TPN - Leave this empty

• Insert row in SYSIBM.IPNAMES

• LINKNAME - linkname This ties to a row in SYSIBM.LOCATIONS with column LINKNAME set to same

• SECURITY_OUT - A A means Already verified

• USERNAMES -

• IBMREQD - N

• IPADDR - nn.nn.nn.nn This is the IP address. You can find this by doing a ping command from a TSO command line or from a NT command prompt. (e.g. PING EXPLORER)

Then the DDF address space must be stopped and restarted to pick up the changes made to the SYSIBM.IPNAMES, SYSIBM.LUNAMES.


Things to do on DB2 LUW

You need to update the DataBaseManager configuration which is at the DB2 Instance level. You can display the current settings with this command from a command line on NT or AIX

DB2 GET DBM CFG | more

Part of the output will look like this:

Database manager authentication (AUTHENTICATION) = SERVER

Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT

There are 2 parms that must be changed from the default to allow connecting from the host--AUTHENTICATION and TRUST_ALLCLNTS. Use the following commands to change these. The changes don't take effect until the db2 instance is stopped and started, so you may need to coordinate cycling the instance with applications and the server owner(s).


UPDATE DBM CFG USING AUTHENTICATION CLIENT
UPDATE DBM CFG USING TRUST_ALLCLNTS DRDAONLY

To cycle the DB2 instance use DB2STOP After it stops do a DB2START. Its a good idea to do a DB2 LIST APPLICATIONS beforehand to see if there are any threads. You can use a FORCE command if you need to to kill any threads.

On top of the above things, if you are writing a COBOL program on host and trying to connect to DB2 LUW, these steps must also be followed.

1. BIND PACKAGE (luwdbname.collectionid) OWNER(CHGTMANP) ISOLATION(CS) QUALIFIER(ALIUPDP) MEMBER(programname) DYNAMICRULES(RUN) VALIDATE(BIND) SQLERROR(CONTINUE) ACTION(REPLACE)

2. Make sure the above BIND statement doesn't fail on anything other than -204 (especially look for -551s, because this could lead to -525 at run time)

3. Grant DBADM authority to the userid that is specified in the OWNER parameter of your BIND card

4. Add the new "dabase.collection.*" to PKLIST on the plan that you will use to run your cobol program
e.g.,

BIND PLAN(plan name) OWNER(owner) QUALIFIER(qualifier)+

     NODEFER(PREPARE) VALIDATE(BIND) ISOLATION(CS)+
     CACHESIZE(1024) CURRENTDATA(NO) DEGREE(1)+
     SQLRULES(DB2) ACQUIRE(USE) RELEASE(COMMIT)+
     EXPLAIN(NO) REOPT(NONE) KEEPDYNAMIC(NO)+
     IMMEDWRITE(NO) DBPROTOCOL(DRDA) ENCODING(37)+
     DISCONNECT(EXPLICIT)+
     PKLIST(collction1.*,+
            collection2.*,+
            database.collection3.*)+
     ENABLE(*)+
     ACTION(REPLACE) RETAIN

5. In the COBOL program, make sure the programmers are coding these in the right order:


SET CURRENT PACKAGESET = 'collectionname'
CONNECT TO :database-name

6. Give EXECUTE access to PUBLIC on the package on LUW for the first time it is bound. This should be done for each new package you create

DB2 UDB Partitioned Database - Unique Index

In the absence of the DBA that usually works on DB2 UDB, I tried to create an unique index on a table that is defined on a partitioned database. I realized that it's not very straight forward to do so.

From DB2 manual: "There are performance advantages to creating a table across several database partitions in a partitioned database environment. Creating a table that will be a part of several database partitions is specified when you are creating the table. There is an additional option when creating a table in a partitioned database environment: the distribution key. A distribution key is a key that is part of the definition of a table. It determines the database partition on which each row of data is stored.

You must be careful to select an appropriate distribution key because it cannot be changed later. Furthermore, any unique indexes (and therefore unique or primary keys) must be defined as a superset of the distribution key. That is, if a distribution key is defined, unique keys and primary keys must include all of the same columns as the distribution key (they might have more columns)."

This is where I got totally lost "any unique indexes (and therefore unique or primary keys) must be defined as a superset of the distribution key. That is, if a distribution key is defined, unique keys and primary keys must include all of the same columns as the distribution key (they might have more columns)". I was trying create the unique index on a column which was not the hash key and I was getting an SQL0270N Reason Code 1. After including the hash key along with the other key, it worked.

DB2 4K space filled up

It was a Friday night and I got a call around 2 AM from Operations people saying, a lot of applications are spewing some error message in DB2 Master address space. I saw these messages in DB**MSTR, which started about 30 minutes ago and still continuing.

I looked up both the reason codes 00C900A5 and 00C90084. The DB2 messages and codes guide said "Explanation: The temporary file with the page size shown in NAME is not available. An error was detected either during the opening of the page set or during the formatting of the header and the space map pages of the pageset.

If the reason code appears in a DSNT500I message issued during the -START DATABASE command, the request fails. In either case, the temporary file function does not allocate any temporary file on the page set."

I checked the 4K datasets DB2CAT.DSNDBC.DSNDB07.DSN4Kxx.I0001.A001. We've 6 of them. I checked the extents on these, and they had spanned for only one extent. So the problem must be something else. I decided to stop and start the 4K tablespaces and issued a STOP against these 6 tablespaces. However, they were in STOPP (Stop Pending) status for a long time. That's when I looked in Omegamon for DB2 and found that there were 4 threads that we running for a long time and had consumed millions of getpages, they were sorting large amounts of data, which means they were using up most of the 4K space. I killed these 4 threads and voila, all the 6 tablespaces stopped. I then issued the START command and started them. They came up without any hiccup and all the error messages stopped. 

SQLCODE -331

Recently I altered a tablespace CCSID from EBCDIC to UNICODE. This tablespace had a PLAN_TABLE in it. After altering the tablespace, I couldn't retrieve certain rows from this PLAN_TABLE. I started getting SQLCODE -331. The message appeared like this:

"CHARACTER CONVERSION CANNOT BE PERFORMED BECAUSE A STRING, POSITION43 CANNNOT BE CONVERTED FROM 367 TO 37, REASON 12"

Position 43 refers to column IBM_REQUIRED_DATA in the PLAN_TABLE. As a short term solution, I spaced out the value in this column and it fixed it. However, it kept appearing for different programs/queries. So, I altered the table column attribute for IBM_REQUIRED_DATA from "FOR SBCS DATA" to "FOR BIT DATA". It fixed the problem.

SMP/E error "NO APPLICABLE ++VER"

I was trying to apply a PTF BPEnnnn on a BMC product called "Snapshot Upgrade Feature". The SMP/E RECEIVE job failed with the message "NO APPLICABLE ++VER". It means that I do not have the correct version of the 'Base Product' on which I was trying to apply the PTF. I checked the technical bulletin that BMC sent me, it mentioned that I should be on Version 5.6 of the product to apply this product. I browsed my SMPPTFIN dataset and searched for the PTF BPEnnnn and found the FMID. It was one version prior to the one mentioned on the technical bulletin. It was clearly my mistake that I didn't read the technical bulletin clearly before doing a SMP/E RECEIVE, but I learnt something new.

SQLCODE -518

The reason mentioned in the DB2 manual for SQLCODE -518 is "THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT"

You may get this SQLCODE for many reasons. I don't know them all. For me this is what happened. When I tried to connect to a remote DB2 database (on LUW) from a COBOL program on the mainframe, the CONNECT was successful. However when I tried to run an SQL on that database I got this SQLCODE.

After researching for a while, I found that I had an existing SQL statement within EXEC SQL and END-EXEC. That worked good. But when I commented out one of WHERE clause statements (using * at column 7), it gave me this error. I moved the commented statement to the line after END-EXEC and it started working again.

Bottom line is DB2 doesn't like COBOL-like commented code inside EXEC SQL and END-EXEC when you are trying to run the SQL at a remote DB2 database.

SQLCODE -804

SQLCODE -804 means something messed up with the SQLDA. Since SQLDA is internally created by DB2 for static COBOL programs, it is possible for a COBOL subscript to overwrite SQLDA statements which are inserted by DB2 at the end of the Working Storage section.

If your programmer complains about this sqlcode, have him/her increase the table size (increase the 'TIMES' parameter value) and recompile the program.

DB2_ALL to run DB2 command on all partitions

DB2 LUW is all Greek and French to me. Today I was trying to rename a column name. I issued the DROP command, but I got the message "SQL0290N Table space access is not allowed." I checked the tablespace status, it was in 'backup pending' state. So I issued the BACKUP database tablespace command and the tablespace state showed normal. However, when I reissued the DROP command, I got the same SQL0290N.

After some research, I found that on a partitioned database, issuing DB2 command from DB2 command line DOES NOT work on ALL partitions. I had to use DB2_ALL utility. However, DB2_ALL doesn't work from DB2 command line.

So I invoked DB2 Task Center, created a new task and put in the following in the Command Script.

DB2_ALL "DB2 BACKUP DATABASE database TABLESPACE tablespace ONLINE TOD:\DB2\BACKUP\database COMPRESS"

Then saved this task, right clicked on it and clicked on Run Now. After it ran, checked the "Show Results" and confirmed that the backup was succesful. After that I reran the DROP and CREATE table command ssuccesfully.

Phew !!

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