Output from SPUFI

SPUFI formats and displays the output data set using the ISPF Browse program.

An output data set contains the following items for each SQL statement that Db2 executes:

The executed SQL statement, copied from the input data set
The results of executing the SQL statement.
The SQLCODE, and if the execution is unsuccessful, the formatted SQLCA.

At the end of the data set are summary statistics that describe the processing of the input data set as a whole.

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 that are executed with SPUFI, the message SQLCODE IS 0 indicates an error-free result.

Example SPUFI output

For example, the sample program returns the following output.

Figure 1. Result data set from the sample problem

   BROWSE-- userid.RESULT                        COLUMNS 001 072
   COMMAND INPUT ===>                                           SCROLL ===> PAGE
   --------+---------+---------+---------+---------+---------+---------+---------+
   SELECT LASTNAME, FIRSTNME, PHONENO                                    00010000
    FROM DSN8D10.EMP                                                     00020000
    WHERE WORKDEPT = 'D11'                                               00030000
    ORDER BY LASTNAME;                                                   00040000
  ---------+---------+---------+---------+---------+---------+---------+---------+
  LASTNAME         FIRSTNME      PHONENO
  ADAMSON          BRUCE         4510
  BROWN            DAVID         4501
  JOHN             REBA          0672
  JONES            WILLIAM       0942
  LUTZ             JENNIFER      0672
  PIANKA           ELIZABETH     3782
  SCOUTTEN         MARILYN       1682
  STERN            IRVING        6423
  WALKER           JAMES         2986
  YAMAMOTO         KIYOSHI       2890
  YOSHIMURA        MASATOSHI     2890
  DSNE610I NUMBER OF ROWS DISPLAYED IS 11
  DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
  ---------+---------+---------+---------+---------+---------+----
  ---------+---------+---------+---------+---------+---------+----
  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 4
  DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 30

Formatting rules for SELECT statement results in SPUFI

The results of SELECT statements follow these rules:

If numeric or character data of a column cannot be displayed completely:
- Character values and binary values that are too wide truncate on the right.
- Numeric values that are too wide display as asterisks (*).
- For columns other than LOB and XML columns, if truncation occurs, the output data set contains a warning message. Because LOB and XML columns are generally longer than the value you choose for field MAX CHAR FIELD on panel CURRENT SPUFI DEFAULTS, SPUFI displays no warning message when it truncates LOB or XML column output.
You can change the amount of data that is displayed for numeric and character columns by changing values on the CURRENT SPUFI DEFAULTS panel, as described in Changing SPUFI defaults.
A null value is displayed as a series of hyphens (-).
A ROWID, BLOB, BINARY, or VARBINARY column value is displayed in hexadecimal.
A CLOB column value is displayed in the same way as a VARCHAR column value.
A DBCLOB column value is displayed in the same way as a VARGRAPHIC column value.
An XML column is displayed in the same way as a LOB column.
A heading identifies each selected column, and is repeated at the top of each output page. The contents of the heading depend on the value that you specified in the COLUMN HEADING field of the CURRENT SPUFI DEFAULTS panel.

Content of the messages from SPUFI

Messages in the SPUFI output contain the following information:

The SQLCODE, and if the execution is unsuccessful, the formatted SQLCA. If multiple SQL conditions occur, SPUFI returns the error SQLCODE first, followed by any previous SQLCODE conditions that occurred as the input SQL statement was executed. If the final SQLCODE is an error, most prior SQLCODE warnings can be ignored.
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