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