The KICKS KooKbooK is a newsletter of tasty recipes to help you get the most from KICKS. Got your own recipe you’d like to share? Send it to mikenoel137@gmail.com

Today's recipe - Using DB2 with KICKS

KICKS is an ordinary TSO/batch application and as such can be used with most of the tools you would use with any other TSO or batch application - including database products. This recipe focuses on using KICKS with DB2 (in Z/OS of course, since DB2 doesn't run in turnkey MVS).

Most of the 'Murach' example programs are preinstalled with KICKS, but the DB2 example (program DB2INQ1) is not. Let's see how to install and run it!

The first step in installing a system into KICKS is to generate the maps. This system has only one, DB2SET1, and it is already compiled, so the binary map is in the KIKRPL library and the symbolic map is in the COBCOPY library. If you look at the COBOL source (in the CB2 library) you will see it doesn't copy DB2SET1 but instead copies DB2SET1X. That's because DB2SET1X is a programmer generated version of the symbolic map, used instead of the version generated by the KICKS map generator.

The second step in installing a system into KICKS is to compile the programs. This system has only one, DB2INQ1, which is NOT compiled. The usual procedure to compile a KICKS program is to run the KIKCB2PP proc on it, which runs the KICKS preprocessor, the COBOL compiler, and the LINKER to produce an executable copy of the program in the KIKRPL library. But for a program that uses DB2 there are a few more steps.

• Best practice for DB2 applications is that, instead of using static copy books to describe the tables, DB2 itself should generate the copy books describing the tables.  This process is call DCLGEN. Of course for it to work the DB2 tables that the system will use need to exist before DCLGEN is run.
• The DB2 preprocessor must also be run against the source code. Just as the KICKS preprocessor translates EXEC KICKS blocks into something the COBOL compiler understands, the DB2 preprocessor translates EXEC SQL blocks in the source.
• Besides COBOL language statements to be handled by the COBOL compiler, the DB2 preprocessor also generates a DB2 'plan' for the program. This 'plan' must be stored in db2 as a 'dbrm' (data base request module) associated with the compiled program. The process of storing this 'plan' is called 'binding',

The Short version of  "How to do it"

Do exactly like any other DB2 application, but

• Run the KICKS preprocessor against the DB2INQ1 source first, producing a member also named DB2INQ1 in another dataset, which you then use as the input dataset for DB2I
• Run DB2I
  • Specify the desired DB2 subsystem in DB2I defaults
  • Specify CAF as the 'RUN TIME ENVIRONMENT' on the PROGRAM PREPARATION screen.
  • Specify SKIKLOAD as an 'INCLUDE LIBRARY', KIKRPL as the 'LOAD LIBRARY', and AMODE(24) as a 'LINK OPTION' on the COMPILE, PRELINK, LINK, AND RUN screen.
  • Also on the COMPILE, PRELINK, LINK, AND RUN screen: specify 'COMPILER AND ASSEMBLER OPTIONS' NODYNAM, APOST, DATA(24), RMODE(24), and TRUNC(BIN).
• When the compile/bind/link is done it may (probably will) be necessary to re-link KIKRPL(DB2INQ1) with a 24 bit glue routine to replace the CAF module DB2I included. This is because KICKS and your transactions are 24 bit applications but, since about release 5 of DB2, IBM's standard CAF routine is 31 bit. You'll know you need this step if the DIN1 transaction results in KICKS going down with an 0C4 abend...
• One time only, you'll need to copy member DSNHDECP from an 'appropriate' (meaning the one for the DB2 subsystem you intend to use) SDSNEXIT library to your KIKRPL library. This is necessary because the program will use a CAF 'implicit' connection to DB2, and CAF will obtain the id of the subsystem to connect to by looking in the DSNHDECP member. The member you copy into KIKRPL will be loaded by KICKS during startup, thus assuring it is the one CAF will use.

The Long version of  "How to do it"

• Set DB2I defaults

The first thing that needs to be said is that this KooKbooK recipe is not a tutorial on DB2I use. Several good tutorials are available online; Google "db2i tutorial" or "spufi tutorial". DB2 for the COBOL Programmer (Eckols) has a good introduction, built around a very similar set of tables and applications (it's published by the same people that publish CICS for the COBOL Programmer, where the DB2INQ1 program we're installing comes from).

The next thing that needs to be said is that it is common for shops to not fully implement DB2I (ie, to not fully customize it properly for their environment). That's because it's an interactive facility and many shops have pure batch procedures they expect their programmers to use instead of DB2I. But it still provides a convenient framework to illustrate what needs to be done to build a DB2 application, and programmers in shops that don't use DB2I probably know the approved alternative, and if not can consult their local DBA to find out. The remainder of this KooKbooK recipe will assume DB2I is available and customized, occasionally providing hints for dealing with minor customization issues.

DB2I is usually on the ISPF/PDF primary screen as a "D" menu pick. When you select it you see another menu



Notice at the top right above it says SSID: DSNX  That's the name of the DB2 subsystem DB2I will work with. If it's not the one you want select menu pick D to change it.



Notice I changed DSNX to DB8G at line 1. DB8G being the name of the subsystem I want to use. This would be different in your case of course. Also notice the SYSPROC DD concatenation in the line 11 job statement - obviously not valid JCL as is, but it's a good place to store something I will use later - I'll explain it when we get there...

 

• Define the tables (CUST and INV)

  Tables are usually defined using DB2I's SPUFI function (menu pick 1 on the DB2I primary menu), which takes you to

   

  Notice the SSID shown has changed since the default changed. And that the input data (line1) is a member of a file from a recent KICKS release. When this is run (after pressing enter) you should check the return codes to make sure it worked.  The return codes might not be zero because this defines the table in what is assumed to be an existing storage group, database, and tablespace. If those objects don't exist on your system it may be you are supposed to use some others, or to define them yourself. If you are supposed to define them yourself see members STOGR1, DB1, and TABSP1 of userid.KICKS.V1R1M1.SPUFI.IN - which may themselves require some modification for your system. Your DBA can help you with that.

  When the CUST table is successfully defined you should define the INV table, using member INVDEF of userid.KICKS.V1R1M1.SPUFI.IN.

   

• Load the test data into the tables

When the tables are successfully defined you should load data into them, again using SPUFI. For the CUST table:



Check the return codes to ensure this worked. Assuming the preceding define worked, the load should have worked also. When this is complete load the INV table, using members INVLOD.

• Create COBOL layouts for the tables using DCLGEN

When the tables are successfully loaded you should use the DCLGEN utility (menu pick 2 on the DB2I primary menu) to make COBOL copybooks for the CUST table.



and for the INV table (just change CUST to INV above).

• Run the KICKS preprocessor and save result for DB2I

  With COBOL copybooks for the tables available you can now run the KICKS preprocessor. To do this get out of DB2I, and edit userid.KICKS.V1R1M1.CB2($DB2INQ1). This is JCL to run the preprocessor on the COBOL source and create an intermediate file that will be used by the DB2 preprocessor in DB2I.

  

  This job is really just a stripped down version of the normal KIKCB2PP proc that only runs the KICKS preprocessor. Make sure to change all occurrences of KCTXMFN to your own TSO userid, then submit.

   

• Run DB2I, produce plan, bind it, and produce program object module in KIKRPL

  With the KICKS preprocessor output now available in userid.TMP.KIKPPOUT(DB2INQ1) return to DB2I and select Program Preparation (menu pick 3).

  

  Ensure that EVERYTHING on the screen looks exactly as above (except the SSID, and the KCTXMFN). Line 1 should name the file created by the preceding KICKS preprocessor run. Line 2 should be TEMP or similar. Line 3 should be EDITJCL so that batch jcl is created, but you will edit and submit it yourself instead of allowing DB2I to directly submit it. Line 4 should specify CAF.  Lines 6-15 should show Y/N exactly as shown.

  Since Y is specified for many of the DISPLAY PANEL? choices you will see (and must edit) several more screens after you press enter here. The first additional screen you will see is the PRECOMPILE screen

  

  Make sure line 2 is as shown (COBCOPY) then press enter and advance to the BIND PLAN screen

  

  Make sure line 5 says DB2INQ1, and that line 12 says REPLACE, then press enter and advance to the COMPILE/LINK/RUN screen.

  

  Make sure lines 3, 4, 7, and 9 are as shown (except for KCTXMFN which should be your TSO userid). KICKS is a 24 bit system so your applications must also be 24 bit, as indicated above.

  When you press enter on the COMPILE/LINK/RUN screen there will be a short pause, then an edit screen will open with the JCL DB2I has created

  

  You'll notice that lines 1-4 are from the DB2I defaults. What you need to do now is move lines 3-4 to after line 14, and comment out line 20

  

  The original JCL used the %DSNH clist from DSN810.SDSNCLST, and that's fine (meaning you didn't need to have the extra lines in the JOBCARD include, nor move the lines, nor comment the allocation already in the JCL) as long as DB2I was configured so that DSN810.SDSNCLST(DSNH) included all the proper defaults for the DB8G subsystem. But it probably does not. In particular, it probably does not correctly name the system datasets required to perform the precompile, bind, compile, and link. The way you'll know this is the case is to run the job without making the change, and see if it works. If it fails with high condition codes and the listing shows it couldn't find things like "CEE.V!R!M!.SCCLKED" (note the exclamation points in the file name!) that means the DSNH clist was not customized.

  Since your security system probably won't let you change DSN810.SDSNCLST to customize it, make your own clist library userid.DSN810.SDSNCLST and copy DSN810.SDSNCLST(DSNH) into it. Then update that copy of DSNH to match the appropriate libraries on your system. You can find the names of these libraries in your normal batch DB2 compile procs. So you'd globally change things like "CEE.V!R!M!." to "CEE." (probably). And "IGY.V!R!M!." to "IGY330." (probably). And maybe a few more. By concatenating your library ahead of the system library you can make the DB2I JCL work...

  So customized (if necessaary) the job should complete with MAXRC=4 (from the unnecessary prelink step). Now to see if it works!

   

• Test run DIN1 transaction in KICKS

Start KICKS, enter the DIN1 transaction, and enter a customer number you know is present in the CUST table



Press enter and you should be rewarded with a crash!



This is due to your 24 bit program innocently calling a 31 bit DB2 CAF routine. Since the CAF routine that ships with DB2 (since release 5) is 31 bit this is almost a certainty. But it may not happen; some shops replace the 31 bit IBM routine with their own 24 bit version, or make other system installation choices that effectively accomplish the same thing.

• Relink object module with 24 bit "glue" to 31 bit CAF

Assuming you got the above crash (and you can't get your DBA to change your whole shop back to 24 bit - fat chance!) the next step is to replace the 31 bit CAF routine DB2I linked with your program with a a "31 bit aware" 24 bit routine that does a mode switch to 31 bit, calls the the 31 bit routine, and switches back to 24 bit before returning to your 24 bit application. A mouthful, but really only a few lines of assembler code. If you want to see what the routine looks like the source is KICKSSYS.V1R1M1.INSTLIB(KIKALI), but you don't need to reassemble it as the object module is already in KICKSSYS.V1R1M1.SKIKLOAD.

JCL to relink DB2INQ1 with the above 31 bit glue is



Make the indicated userid changes and submit.

• Test run DIN1 transaction in KICKS

Again, start KICKS, enter the DIN1 transaction, and enter a customer number you know is present in the CUST table (like 400003) and press enter. This time you'll see



This is sort of a strange message. It tells us there is a serious problem - but EIBRESP and EIBRESP2 are zero - meaning no problem! To figure out what's going on you can use KEDF (see the programming/debugging section of the KICKS User's Guide) to view the trace table leading to this screen



Which shows DB2INQ1 started up, read its map (DB2MAP), and immediately xctl'd to SYSERR which displayed the error screen. So it's time to look at the source code for DB2INQ1 and see where and why it calls SYSERR. The source is userid.KICKS.V1R1M1.CB2(DB2INQ1). When we look at it we see that SYSERR is referenced only once, in paragraph 9999-TERMINATE-PROGRAM, and that paragraph is referenced 3 times - in all cases after finding an unsatisfactory SQLCODE. 

Opps!  EXEC SQL's don't update the EIB (KICKS or CICS), so calling SYSERR for an SQLCODE problem will never tell us anything useful. Let's edit DB2INQ1 and replace 9999-TERMINATE-PROGRAM to provide more meaningful information.



where we commented out moving EIBRESP and EIBRESP2, instead moving SQLCODE and a flag for plus/minus. Let's recompile.

1. do over "· Run the KICKS preprocessor and save result for DB2I", then
2. do over "· Run DB2I, produce plan, bind it, and produce program object module in KIKRPL", then
3. do over "· Relink object module with 24 bit glue to 31 bit CAF".
4. Finally run this bullet over again to get the SQLCODE for the problem, to wit:

So it was a -991. Of course we could also fixup the SYSERR routine so it somehow recognized an SQLCODE situation and formatted it better, but that's beyond the scope of this recipe.

A -991 SQLCODE means call attach (CAF) was unable to establish an implicit connection or open to DB2. This is usually because the DB2 subsystem in question was not available. But notice that what we are talking about is an IMPLICIT connection. That means there is nothing in the DB2INQ1 application, or in KICKS, to tell CAF what DB2 subsystem it should be using. How do you suppose it decides? From from DB2 UDB for z/OS Version 8 Application Programming and SQL Guide (SC18-7415)

implicit connections use the following defaults:

Subsystem name

The default name specified in the module DSNHDECP. CAF uses the installation default DSNHDECP, unless your own DSNHDECP is in a library in a STEPLIB of JOBLIB concatenation, or in the link list...

Plan name

The member name of the database request module (DBRM) that DB2 produced when you precompiled the source program that contains the first SQL call. If your program can make its first SQL call from different modules with different DBRMs, you cannot use a default plan name...

So CAF will use a subsystem name from the DSNHDECP it finds either from the TSO session STEPLIB (or possibly TASKLIB if such is allocated) or from the linklist (most likely alternative). This will probably not be the "right" subsystem: it may not even be one your shop is actually using. The DSNHDECP member you want CAF to use is the one in the SDSNEXIT library customized for your target subsystem. How can you arrange that? Add a STEPLIB including the appropriate SDSNEXIT library to your TSO logon proc. Unfortuately this is not often possible. Fortunately there is another way. It turns out that the above list of places DSNHDECP might be found is not exhaustive. It will also be found if it is in the LPA or JPA. And KICKS can arrange to load it into the JPA simply by putting a copy of the DSNHDECP module we want into a library in the KIKRPL concatenation, then adding a "LOAD=INITIAL" entry for it in the PPT.

The appropriate PPT entry (already included in KIKPPT1$) is

* * -- DB2 SUPPORT FOR Z/OS * KIKPPT TYPE=ENTRY,PROGRAM=DSNHDECP,PGMLANG=ASSEMBLER, * LOAD=INITIAL *

• Copy DSNHDECP to KIKRPL or Modify KICKS clist

The remaining task is to get a copy of the right module into the KIKRPL concatenation, and the easiest way to do that is to modify the KICKS clist to add the library containing the module to the concatenation.

In this case the module is in DSN810.SDSNEXIT, so the appropriate mods to the KICKS clist are as below (add the lines in white)



• Test run DIN1 transaction in KICKS

Start KICKS one more time, enter the DIN1 transaction, and enter a customer number you know is present in the CUST table (like 400003) and press enter. This time you'll see

Success!! 

 

A closing note

This version of KICKS doesn't really support or utilize DB2 any more than it supports or utilizes LE. What it does is more like 'toleration'. It blindly stays out of their way, doing just what they minimally require, and nothing they can't themselves tolerate.

This is a GOOD thing. It means you can use DB2 with KICKS not because KICKS has special hooks to allow it, but because KICKS's standards compliance means it will work with other z/OS tools that  also use standard interfaces. You should have an easy time using KICKS with other major database products like Oracle, IDMS, Datacom and ADABAS !