The SPUFI panel

The SPUFI panel is the first panel that you need to fill out to run the SPUFI application.

After you complete any fields on the SPUFI panel and press Enter, those settings are saved. When the SPUFI panel displays again, the data entry fields on the panel contain the values that you previously entered. You can specify data set names and processing options each time the SPUFI panel is displayed, as needed. Values that you do not change remain in effect.

The following descriptions explain the fields that are available on the SPUFI panel.
1,2,3 INPUT DATA SET NAME
Identify the input data set in fields 1 through 3. This data set contains one or more SQL statements that you want to execute. Allocate this data set before you use SPUFI, if one does not already exist. Consider the following rules:
  • The name of the data set must conform to standard TSO naming conventions.
  • The data set can be empty before you begin the session. You can then add the SQL statements by editing the data set from SPUFI.
  • The data set can be either sequential or partitioned, but it must have the following DCB characteristics:
    • A record format (RECFM) of either F or FB.
    • A logical record length (LRECL) of either 79 or 80. Use 80 for any data set that the EXPORT command of QMF™ did not create.
  • Data in the data set can begin in column 1. It can extend to column 71 if the logical record length is 79, and to column 72 if the logical record length is 80. SPUFI assumes that the last 8 bytes of each record are for sequence numbers.

If you use this panel a second time, the name of the data set you previously used displays in the field DATA SET NAME. To create a new member of an existing partitioned data set, change only the member name.

4 OUTPUT DATA SET NAME
Enter the name of a data set to receive the output of the SQL statement. You do not need to allocate the data set before you do this.

If the data set exists, the new output replaces its content. If the data set does not exist, DB2® allocates a data set on the device type specified on the CURRENT SPUFI DEFAULTS panel and then catalogs the new data set. The device must be a direct-access storage device, and you must be authorized to allocate space on that device.

Attributes required for the output data set are:
  • Organization: sequential
  • Record format: F, FB, FBA, V, VB, or VBA
  • Record length: 80 to 32768 bytes, not less than the input data set

Executing SQL by using SPUFI shows the simplest choice, entering RESULT. SPUFI allocates a data set named userid.RESULT and sends all output to that data set. If a data set named userid.RESULT already exists, SPUFI sends DB2 output to it, replacing all existing data.

5 CHANGE DEFAULTS
Enables you to change control values and characteristics of the output data set and format of your SPUFI session. If you specify Y(YES) you can look at the SPUFI defaults panel. See Changing SPUFI defaults for more information about the values you can specify and how they affect SPUFI processing and output characteristics. You do not need to change the SPUFI defaults for this example.
6 EDIT INPUT
To edit the input data set, leave Y(YES) on line 6. You can use the ISPF editor to create a new member of the input data set and enter SQL statements in it. (To process a data set that already contains a set of SQL statements you want to execute immediately, enter N (NO). Specifying N bypasses the step 3 described in Executing SQL by using SPUFI.)
7 EXECUTE
To execute SQL statements contained in the input data set, leave Y(YES) on line 7.

SPUFI handles the SQL statements that can be dynamically prepared.

8 AUTOCOMMIT
To make changes to the DB2 data permanent, leave Y(YES) on line 8. Specifying Y makes SPUFI issue COMMIT if all statements execute successfully. If all statements do not execute successfully, SPUFI issues a ROLLBACK statement, which deletes changes already made to the file (back to the last commit point).

If you specify N, DB2 displays the SPUFI COMMIT OR ROLLBACK panel after it executes the SQL in your input data set. That panel prompts you to COMMIT, ROLLBACK, or DEFER any updates made by the SQL. If you enter DEFER, you neither commit nor roll back your changes.

9 BROWSE OUTPUT
To look at the results of your query, leave Y(YES) on line 9. SPUFI saves the results in the output data set. You can look at them at any time, until you delete or write over the data set.
10 CONNECT LOCATION
Specify the name of the database server, if applicable, to which you want to submit SQL statements. SPUFI then issues a type 2 CONNECT statement to this server.

SPUFI is a locally bound package. SQL statements in the input data set can process only if the CONNECT statement is successful. If the connect request fails, the output data set contains the resulting SQL return codes and error messages.