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

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?

COBOL - INITIALIZE

In a COBOL INITIALIZE statement, elementary FILLER items are ignored. That means, INITIALIZE does not touch the values in the FILLER.

e.g.,

01 WS-VAR.
05 FILLER PIC X(5) VALUE 'junk1'.
05 WS-FILLER PIC X(5) VALUE 'junk2'.
05 FILLER PIC X(5) VALUE 'junk3'.

After the statement INITIALIZE WS-VAR is executed, FILLER contains 'junk' ,whereas WS-FILLER contains all spaces.

Effectively, WS-VAR changes from 'junk1junk2junk3' to 'junk1 junk3'.

CA Intertest (Batch) Tips

If you use CA's Intertest for Batch to debug your batch COBOL programs or stored procedures, read on.

There are a few commands that you use frequently. Set them commands in PFKEYS for ease of use:

STEP 1 - Set steps to go in each run to 1 (I usually set it to PF4)
GO - Run as many steps as described in the STEP command (I usually set it to PF5)
RUN - Run until the next breakpoint (I usually set it to PF6)
KUP - Scroll Up in the Data Display Window (I usually set it to PF17)
KDOWN - Scroll Down in the Data Display Window (I usually set it to PF18)