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 100th 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.