Understanding Spufi output.

There are various ways to execute SQL statements in a mainframe.
You can execute them through 

• Embedding them in Application program (<Exec sql> <end-exec>) 
• Using QMF (Query Management Facility)
• Using any third party product(vendor) available in your shop  like BMC,WINSQL etc.
• Productivity aid sample programs like DSNTIAUL, DSNTEP2 or DSNTIAD etc
• Using SPUFI (SQL Processing Using file input)

This post is mainly to understand the output of the query which is run through SPUFI.

First let’s see the sample spufi output.

 DSNE617I COMMIT PERFORMED, SQLCODE IS 0   DSNE616I STATEMENT

 EXECUTION WAS SUCCESSFUL, SQLCODE IS 0   

---------+---------+---------+---------+---------+---------+---- 

 DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 

1 AND 72   DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1  

 DSNE621I NUMBER OF INPUT RECORDS READ IS 32  

 DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 22

As you can see here,the SPUFI output contains

• The executed SQL statement, copied from the input data set
• The results of executing the SQL statement
• The formatted SQLCA, if an error occurs during statement execution

The weird and interesting thing if you can notice here is,even though the query ran successfully and gave the proper result SQLCODE IS 100 AND NOT 0.

Why??? My query ran successfully so,I should get sqlcode 0 right??

Well to put you in more confusion, if i run the same query through application program ( Embeded in program) , it will give SQLCODE 0
The question remains the same rather more aggressive now.

The answer is 

“For SELECT statements that are executed with SPUFI, the message ”SQLCODE IS 100″ indicates an error-free result. If the message SQLCODE IS 100 is the only result, DB2 is unable to find any rows that satisfy the condition that is specified in the statement.

For all other types of SQL statements (Other than SELECT)that are executed with SPUFI, the message ”SQLCODE IS 0″ indicates an error-free result. “

There is still a trick to avoid getting SQLCODE 100 in this case and you still can get SQLCODE 0

You have 5 rows in your table satisfying your condition , then set the MAXIMUM ROWS TO BE FETCHED option in the SPUFI to 5 but for this you should know in advance ,the no. of rows your query would return which is not feasible every time. 
If you set to any value more than 5 it would give SQLCODE 100 at the end.
 Apart from this,each SPUFI message contains the following:

• The SQLCODE, if the statement executes successfully.
• The formatted SQLCA, if the statement executes unsuccessfully.
• What character positions of the input data set that SPUFI scanned to find SQL statements. This information helps you check the assumptions that SPUFI made about the location of line numbers (if any) in your input data set.
• Some overall statistics:
  • Number of SQL statements that are processed
  • Number of input records that are read (from the input data set)
  • Number of output records that are written (to the output data set).

Other messages that you could receive from the processing of SQL statements include:

• The number of rows that DB2 processed, that either:
  • Your select operation retrieved
  • Your update operation modified
  • Your insert operation added to a table
  • Your delete operation deleted from a table
• Which columns display truncated data because the data was too wide

How the DB2 Application Program runs on Mainframe

This Post is to understand in detail how your application program runs (and various stages it goes through) on mainframe.

What is DB2 Application program?

The program written in any High-Level language (In this context COBOL language) which access the data stored in DB2 database.

IF we feed the DB2 application program written in COBOL to the Cobol Compiler (IGYCRCTL) it can recognize only Cobol statements in application program and cannot recognize any DB2 statement hence before feeding this program directly to Cobol compiler, we want something which can recognize DB2 statement and separate the DB2 statement from the Cobol program. This can be done by feeding the application program to DB2 Pre-compiler.

Ø  DB2 Pre-compiler :

·         The DB2 pre-compiler will comment out the DB2 statement in program and replace them with the CALL statement.  


This commented DB2 statement will be stored separately in different dataset which is called as DBRM (Data Base Request Module).

So this pre-compiler separates out the main program in two parts

1>    The modified Source code (The main program with Commented Db2 statements and corresponding  replaced  CALL statement.

2>    The DBRM (Contains all the DB2 statement from the source program)

The same (How the DB2-Precompiler separates out the main program in two parts) can be viewed in BATCH JCL.  
      

                                                                                                                                                                                                                               ·         Place the timestamp token  on DBRM and modified source code .
This Timestamp token will then carry forward in bot parts and will be checked at the run time when these two separated parts reunite.So at the run time the matching timestamp token will indicate that we are using the same DBRM and Modified source code which get separated at pre-compile step.

The pre-compilation timestamp token placed by DB2 pre-compiler on the DBRM can be obtained by checking the value from POS 25 through POS 32 in the DBRM header, which is the first record in the DBRM member.



The pre-compilation timestamp token placed by DB2 pre-compiler on the Modified source code can be found in variable SQL-TIMESTAMP-1 SQL-TIMESTAMP-2.



SQL-TIMESTAMP-1 (412701320) equals 18995288 in Hex which is the same as the first 8 digits the DBRM.
SQL-TIMESTAMP-2 (471453846) equals 1C19D096 in Hex which is the same as the second 8 digits the DBRM.

Ø  COBOL Compiler (IGYCRCTL):

The modified source code out of the DB2 pre-compiler will now fed to the COBOL compiler. The compiler will check the code for syntax error and creates the OBJLIB (Object Library)   
              


Ø  Link-Edit (IEWL) :

IEWL is the program which link-edit the OBJLIB created in previous step by Cobol compiler to create a LOAD MODULE (The corresponding machine level code of our main DB2 Application program)



Ø  BIND :  

             The second part created out of DB2 Pre-compiler (DBRMLIB) will now get processed.
The Bind is type of compiler to the SQL statement (DB2 statement).It will read the DB2 Statement from the DBRMLIB and check it for syntax error and create a mechanism to execute the SQL statement.
There are two types of BIND, BIND PLAN and BIND PACKAGE.

·         BIND PLAN:
 This will have the one or more DBRM, Packages or the combination of DBRM and Package as an input. The BIND PLAN command will invoke the DB2 Optimizer which intern will create the PLAN containing optimized access path to execute the SQL statement inside DBRM.



The PLAN created here is an executable and will execute only along with the corresponding Load module created from modified source code through Compile-Link Edit process. None of the PLAN or Load Module can be executed alone.

·         BIND PACKAGE:

This will have only single DBRM as an input. The Package created here is not executable and need to be bind to plan again in order to get executed.
The bind process also updates the Pre-compile Timestamp token placed by the DB2 Pre-compiler on DBRM header in TIMESTAMP column in SYSIBM.SYSDBRM table .PRECOMPTS column will have the regular Timestamp. The columns PRECOMPTIME and PRECOMPDATE will have time (Resolution to 100^th of a SEC.) and Date (YYMMDD).Note the remaining flags in the DBRM header will be updated in the first row of SYSIBM.SYSSTMT (for PLAN bind) orSYSIBM.SYSPACKSTMT (for package bind). DB2A gets the details from these tables.

Ø  RUN PROGRAM:

So at the start we separated our main DB2 application program into Modified source code and DBRM which then went through two different processes to create the LOAD module and PLAN respectively. Now just before running the program we need to reunite these two and run our main DB2 application program.

While running, the Timestamp token placed by the DB2 pre-compiler on both part (Plan (fromSYSIBM.SYSDBRM) and Load module) will be checked.
We will get the SQLCODE of -818 if the Timestamp between two doesn’t match.



Note :  Click on the images to get bigger view.

Cusror in DB2 ---> Part II

==> Click Here to Read Part III <==

In first part we discussed about, what the cursor is and why we need it.

In this part we will discuss about the types of cursor. Yes there are different types of cursor and they work differently depending on their type.

In normal or simple cursor the data can only be retrieved one row at a time in forward direction using "FETCH" statement.

Scrollable Cursor :-

With Scrollable cursor, you can scroll forward/backward through the data(Result set of select statement) in an application program.

Ok but before everything else how do you distinguish that the cursor defined in the program is scrollable or not.

The keyword "SCROLL" is use to specify that the cursor defined is scrollable.

Example:

Declare  Cursor1 (cursor name) 

             SCROLL Cursor

Ok so this is how you define it.Now, how does it work? how do you move through the data?

Similar to normal cursor, Fetch statement is still used  but is combined with different keyword to move the fetching in specific direction.

Example :

FIRST            :- This will fetch the first row in the result set.

CURRENT     :- This will fetch the current row from the result set.

Similarly there are different keywords like LAST,NEXT,BEFORE,AFTER,PRIOR etc.

So for the above defined cursor , the fetch statement will be

FETCH FIRST Cursor1 INTO .....

Fetch first statement will position the cursor to the first row of the result set and then fetch it to the defined host variable.

Scrollable cursor makes it easy to scroll through the data thus saves amount of time and effort.

There are two types of scrollable cursor.

Yes there are again types of scrollable cursor but it make sense to understand on what factor they are diffracted.

1. Sensitive      : With this type of scrollable cursor,the program can access the
                           data changed /updated by the user.So at the time, the data
                           being fetch from the table using the cursor if some  other

                           process update/delete the data from table that get reflected in
                           the result set.

                           However, Inserts will not be reflected in the result set.

2. Insensitive :   This type of scrollable cursor will not show any change that are 
                          made to the data at the time of fetch so the underlying data will 
                          not be reflected in the result set

                            

So the definition of cursor finally would finally look like

Declare Cursor1 (cursor name) 

         SENSITIVE SCROLL Cursor

Thing to remember: -

Though easier to use , you should not define every cursor to be scrollable as it require more overhead to the normal/conventional type of cursor.

To be specific , if you do not need to move forward /backword through the result set do not use scrollable cursor.

Until now we saw that using cursor(be it any type) we can fetch only one row at a time.We can also fetch multiple rows using single fecth statement in a cursor.

How do you do it?? we will see it in next part.

==> Click Here to Read Part III <==