CURSOR WITH HOLD OPTION

This is an optional specification on CURSOR declaration.The significance  can be understood by considering what happens in its absence.

Suppose we need to process some large table, one row at a time by means of a cursor and update a few of the as we go. It is often desirable to divide the work up into batches and to make the processing of each batch into a separate transaction (by  issuing a separate commit at the end of each one); thus, e.g. a table of one million rows might be processed by a sequence of 10,000 transactions, each one dealing with just 100 rows. This way , for e.g. if it becomes necessary to roll a given transaction back, then at most 100 updates will have to be undone, instead of potentially as many as a million.

The problem with this approach ,however  is that every time we issue a commit, we implicitly close the cursor, thereby losing our position within the table. The first thing each transaction has to do, therefore is to execute some re-positioning code in order to get back to the row that is due to be processed next. And that re-positioning code can often be quite complex, especially if the processing sequence is determined by a combination of several columns.

If the cursor declaration specifies with hold, however, commit does not close the cursor, instead, leaves it open, positioned such that the next FETCH will move it to the next row in sequence. The possibly complex code for repositioning is thus no longer required.

However it is important to note that the first operation on the cursor following the commit must be fetch. Update and delete current are illegal.
 

CICS with DB2/IMS - Remote Stored Procedure(RSP) in CICS

When and where do we use Remote Stored procedure:
Remote Stored procedures are very handy specially if an front-end application does an dynamic handshaking with mainframe,ie,fetch data from db2 or ims or any other databases and passes the data back to front end .
Overview:
These are compiled programs stored in CICS that basically execute a bunch of SQL statements coded around some logical and manipulative processing in a programming language (We can use either Assembly or COBOL).  RSP’s are generally written in COBOL. A Client front end application program uses the “CALL”  statement to invoke the RSP .A Client program can be written in VB, C#, Powerbuilder and other languages.

The most visible difference between DB2 stored procedure and RSP is that there is no catalog entry for RSPs and they can be written in COBOL. Advantage of using an RSP is that it executes many precompiled SQL statements in a single call reducing the need for multiple calls to the DB2.

Also every time a dynamic SQL is executed from the Client application, Connection has to be made through Middleware like Direct Connect in common cases.

The Direct Connect Gateway Product can communicate with CICS in five modes

1)       STANDARD

2)       BIN

3)       DB2

4)       SDS 
5)       MIX

Modes 1 & 2 transfer data between front end and the CICS without Data structure.Mode 3,4,5 transfer with data structure.
In Standard mode, the entire data is passed to the front end in form of a long string.

If we are using the Standard mode we have one main driver program that will talk to front end application and other CICS programs or its subprograms,ie the entire information flow should be through one program ;the driver progam.

In DB2 mode, the data is returned in result set to front end. For this to happen, CICS program also passes the Data Structure for all the columns that form the result set by using the SQL Descriptor Area or SQLDA. SQLDA tells the front end data type, length,etc of the column .

There are some terms unique to Direct Connect gateway

1.       Open Pipe

2.       Put Pipe

3.       Close Pipe

Just imagine a pipe with taps at both the ends. One end is connected to Client program and the other end is connected to CICS. 

1.       When the Client issues a call, tap at the client end opens automatically and sends in the call to CICS and the tap at the CICS end receives it through linkage section

2.       The pipe is opened at the CICS end through the command OPEN PIPE to Output data

3.       Input data is processed and output is put into the pipe through PUTPIPE at one row at a time.

4.       If process is complete, then the tap at the CICS end is closed

5.       The data now in the pipe is retrieved by the front end by opening the tap at its end.

Check the Sample program in CICS section.

Before You  Test or invoke RSP:
Each RSP must have a CICS PPT entry. (Generally, the systems administrator or system programmer makes CICS entries.)In addition, if the RSP runs through TRS and accesses DB2,transaction definition in CICS is required for each RSP and an RCT entry is required for that transaction.

Prerequisites to build an RSP:
     RSPs must communicate with the front-end Client application using the SPAREA and the DATA PIPE.The SPAREA contains all the pointers,codes and commands that the RSP needs to  to exchange information. RSP commands(OPENPIPE,PUTPIPE,GETPIPE and others) are small ASSEMBLER programs that calls the Server Connect.We need DATA PIPES to pass rows of data between the application and mainframe.Choosing client application functions
Choosing the Client application function:
You need to understand what functions the client application that calls the RSP is going to perform. Coordinate with the client application programmer to determine the data (that is, keyword variables, variable text, or data) being sent to the RSP and the kind of formatting the client application is capable of performing on the results.
For example, if your RSP provides data structure information with the data it is sending, the client application does less decoding of results. If the RSP sends unformatted data, the client must include more logic to decode the results.

Accessing Databases:
RSP can access the databases like DB2,IMS,ADABAS,VSAM,BDAM

Exchanging Information between front end and CICS:
The SPAREA contains all the pointers, codes, and command details that the RSP needs to exchange with the RSP API. Every RSP receives or sends information using the SPAREA.

BASIC TERMS AND USAGE:
The OPENPIPE command uses values from these SPAREA fields:

SPMODE specifies whether the data pipe is opened for input or output.
INPUT indicates the RSP reads data records sent from the client application.
OUTPUT indicates the RSP writes data records to be sent to the client application.
SPFORMAT specifies the data pipe format.
STD indicates standard format, in which each data record is transmitted to or from the client application as a single-text column record.
BIN indicates a single-binary column format, like STD, except that the data is binary. No ASCII-EBCDIC or EBCDIC-ASCII conversion occurs on binary data
SPMAXLEN specifies the maximum size, in bytes, of the data records written to or read from the data pipe. 
STD and BIN format pipes must use SPMAXLEN to identify the maximum record length.

Sampel Code to Open a STD output pipe:
MOVE 'OUTPUT' TO SPMODE.
MOVE 'STD' TO SPFORMAT.
MOVE 450 TO SPMAXLEN.
CALL 'OPENPIPE' USING SPAREA.
Check the Sample program in CICS section.

DB2 indicates data is transmitted from the RSP as a multiple-column record, where the column definitions are contained in an associated SQLDA. The SQLDA is a collection of variables and pointers that provide column information about data being transmitted to the client application.
SPSQLDA specifies the address of a SQLDA that describes the content of the data records. Use only for output pipes.
For DB2 format pipes, the RSP must supply the SPSQLDA address. DB2 format pipes must use SPSQLDA.
Both an input pipe and an output pipe can be open at the same time

Continued in Sample program in CICS section. ...

COMMIT AND ROLLBACK

COMMIT AND ROLLBACK

About DB2 Consistency Token(CONTOKEN), DB2 SQL ERROR -805 and -818

What is contoken or consistency token in DB2?
 If we remember during precompilation process, the SQL part and COBOL part  are separated.While doing so, compiler engraves a time stamp to each part, so that later they can identify each other. at run time. This time stamp is what we call Contoken or consistency token
We get SQL code of -818 when the Consistency Token placed by DB2 precompilation Step  does not match between the DBRM and the Load Module.
80N or -805 is what we get when the DBRM or Package is not found in Plan which we used in JCL.

Steps to find out the contoken and verify if both are same??
STEP1:
We can go to QMF or run the below query
SELECT NAME, HEX(CONTOKEN), BINDTIME,PDSNAME
FROM SYSIBM.SYSPACKAGE WHERE
NAME='PORGRAM-NAME'
ORDER BY BINDTIME DESC
 
We fetch the HEX value for the contoken.Otherwise we wont be able to see the value,if fetched normally.
This Fetches the program-name, its contoken, bind time and the DBRM Library order by latest bind time.

 


we can get the same Contoken (with out using the above query also) by looking into the DBRM library for the program.
Open the DBRM Library and give the command HEX ON
Position of contoken in DBRMLIB
Look for 25 to 32nd position in the first line. That will show the contoken.

 






Next Step 2:
Remember the Date and Time part of Contoken are swapped in Loadlib, you need to split the above contoken into two halves of 8 characters each and swap their positions like below:
Taking the above example, contokens are
1st Half : 18DAE169
2nd Half: 1F0420A5

Swapping and joining them results in
1F0420A518DAE169

Now go to the LOAD MODULE library and search for it like
 f x'1F0420A518DAE169'


 You should be able to see this. Else There is a contoken mismatch.Rebind again or recompile to get rid of this freaking problem. -:)
Note:  F x 'search-string' . Here x implies the search string in quotes is hex value.

Thanks!

Structure of SYSIBM.SYSPACKAGE

SYSIBM.SYSPACKAGE DB2 table will contain details about package having the structure below


LOCATION VARCHAR(128)
COLLID VARCHAR(128)
NAME VARCHAR(128)
CONTOKEN CHAR(8)
OWNER VARCHAR(128)
CREATOR VARCHAR(128)
TIMESTAMP TIMESTMP
BINDTIME TIMESTMP
QUALIFIER VARCHAR(128)
PKSIZE INTEGER
AVGSIZE INTEGER
SYSENTRIES SMALLINT
VALID CHAR(1)
OPERATIVE CHAR(1)
VALIDATE CHAR(1)
ISOLATION CHAR(1)
RELEASE CHAR(1)
EXPLAIN CHAR(1)
QUOTE CHAR(1)
COMMA CHAR(1)
HOSTLANG CHAR(1)
CHARSET CHAR(1)
MIXED CHAR(1)
DEC31 CHAR(1)
DEFERPREP CHAR(1)
SQLERROR CHAR(1)
REMOTE CHAR(1)
PCTIMESTAMP TIMESTMP
IBMREQD CHAR(1)
VERSION VARCHAR(122)
PDSNAME VARCHAR(132)
DEGREE CHAR(3)
GROUP_MEMBER VARCHAR(24)
DYNAMICRULES CHAR(1)
REOPTVAR CHAR(1)
DEFERPREPARE CHAR(1)
KEEPDYNAMIC CHAR(1)
PATHSCHEMAS VARCHAR(2048)
TYPE CHAR(1)
DBPROTOCOL CHAR(1)
FUNCTIONTS TIMESTMP
OPTHINT VARCHAR(128)
ENCODING_CCSID INTEGER
IMMEDWRITE CHAR(1)
RELBOUND CHAR(1)
CATENCODE CHAR(1)
REMARKS VARCHAR(550)

Structure of SQLCA

 

Cobol DB2 Compilation Process

The Basic steps involved are 1) Precompilation 2) Bind 3) Finally Execution of the COBOL DB2 program. 
When the cobol-DB2 complition job is submitted,the DB2 Precompilation step is first executed.Its Function is to analyse the host language source module ie, the COBOL-DB2 program and stripping out all the db2 sql statements it finds and replaces them by the host language CALL statements to the DSNHLI module. 

SQL statements are commented out and call to DSNHLI module is being made.(click to enlarge)

What the heck does that mean?? :) 
To be clear, it(the precompiler) comments out the all the DB2 SQL statement in our cobol-db2 program,and in turn calls DB2 run time interface modules.
All the sql it encounters in the progrm, the precompiler puts them in a PDS know as  database request module(DBRM)which becomes the input to the next step,ie the bind step.
The runtime call contains necessary parameters(DBRM name,Timestamp, statement number,host variable address,SQLCA address).  
So here the DB2 SQL statements and Cobol code are Separated.
The separated Cobol part goes to cobol compiler and stripped out SQL statements from the same program ,ie DBRM goes as input to BIND step which will create the executable code for the DB2 portion of the cobol program.
The separated cobol and SQL statements must be united later in the final stage of execution.So,to help each other in finding, both the seperated parts are assigned one token,called contoken,which is basically a timestamp. 

Lets see the functions of each step in details now:

STEP1. THE PROCESS OF PRECOMPILATION: 

a. Searches and Expands DB2 related INCLUDE members.You will see something of this sort in the program.
EXEC SQL
  INCLUDE MEMBER
END-EXEC 
These 'member' is nothing but DCLGEN.
b. Searches for all the SQL statements in the cobol program
c. Creates the modified version of the source program where every SQL statement is commented out and call to DB2 runtime interface module replaces each original sql statement.
d. All extracted SQL statements is stored in a PDS called DBRM.
e. Matches each column and table name in the SQL to the DECLARE TABLE statements contained in the DCLGEN


This is how a DBRM looks like having all stripped out SQL statements

Image 1: DBRM

NOTE: DB2 catalog is not accessed during this precompilation process. 
STEP2. BIND: 

we can say this component as the compiler for the sql statements produced in the above step(output of precompilation step). It reads the sql statements from the DBRMs and produces a mechanism to access data as directed by the sql statements being bound.Output of this bind is the PLAN. The bind plan accepts as input one or more DBRM’s produced from the previous DB2 program precompilation steps and creates the executable code for the SQL statements.We BIND the instructions for the SQL that was in DBRM into a PLAN
or BIND the instructions into a PACKAGE.

The major functions of bind are:
a. Parsing and Syntax Checking
b. Optimization
c. Authorization.

Remember we need to Bind 
Either
DBRMS  to a Plan

OR
DBRMS To a Package and then Packages into the Plan.We will see Collections later(Nothing but group of packages,or few packages grouped together)
Note:  Only the Plan is executable.  

Thus,PLAN/Package  are nothing but Container which contains how your SQL statements should get executed. In Application Program, we specify What we want, and not how we want it. Plan Contains the logic for 'How'.(We dont care how DB2 internally does it.)
When we say Plan is executable, it means it needs to be executed along with Cobol load module which was separated during precompilation time.

STEP3:  EXECUTION 
At run time, the load module starts up and eventually hits a paragraph containing a call to db2.

Image 2: SQL statements are commented and call to DSNHLI module are being made

We execute the DB2 program witht the DB2 utility IKJEFT01.In the SYSIN parameter we specify the DB2 subsystem name, the plan name and the load module name. 

General info:
When a plan is bound by the BIND COMMAND,DB2 reads the following DB2 catalog tables:
SYSIBM.SYSCOLDIST
SYSIBM.SYSCOLSTATS
SYSIBM.SYSINDEXES
SYSIBM.SYSPLAN
SYSIBM.SYSTABLES
SYSIBM.SYSTABSTATS
SYSIBM.SYSCOLDISTSTATS
SYSIBM.SYSCOLUMNS
SYSIBM.SYSINDEXSTATS
SYSIBM.SYSPLANAUTH
SYSIBM.SYSTABLESPACE
SYSIBM.SYSUSERAUTH

Information about the plan is stored in:

SYSIBM.SYSDBRM
SYSIBM.SYSPACKLIST
SYSIBM.SYSPLANAUTH
SYSIBM.SYSPLSYSTEM
SYSIBM.SYSTABAUTH
SYSIBM.SYSPACKAUTH
SYSIBM.SYSPLAN
SYSIBM.SYSPLANDEP
SYSIBM.SYSSTMT
Note: The DB2 catalog only stores information about the plan. The executable form of the plan called a skeleton cursor table or SKCT is stored in DB2 directory in SYSIBM.SCT02 tabl.

What is a collection in DB2 ??? 
A collection is a group of packages. When we bind a package into a collection, and the collection is available to the plan, any package included in the collection cane be executed by the plan. we can segregate the packages based on application into one collection

Cheers..!!!