Executing SQL by using SPUFI

You can execute SQL statements dynamically in a TSO session by using the SPUFI (SQL processor using file input) facility.

Before you begin

Before you use SPUFI, allocate an input data set to store the SQL statements that you want to execute, if such a data set does not already exist.

Before you begin this task, you can specify whether TSO message IDs are displayed by using the TSO PROFILE command. To view message IDs, type TSO PROFILE MSGID on the ISPF command line. To suppress message IDs, type TSO PROFILE NOMSGID.

These instructions assume that ISPF is available to you.

About this task

Important: Ensure that the TSO terminal CCSID matches the Db2 CCSID. If these CCSIDs do not match, data corruption can occur. If SPUFI issues the warning message DSNE345I, terminate your SPUFI session and notify the system administrator.

SPUFI can execute SQL statements that retrieve Unicode UTF-16 graphic data. However, SPUFI might not be able to display some characters, if those characters have no mapping in the target SBCS EBCDIC CCSID.

Procedure

To execute SQL by using SPUFI:

  1. Open SPUFI and specify the initial options. To open SPUFI and specify initial options:
    1. Select SPUFI from the DB2I Primary Option Menu as shown in The DB2I primary option menu.
      The SPUFI panel is displayed.
    2. Specify the input data set name and output data set name.
      An example of a SPUFI panel in which an input data set and output data set have been specified is shown in the following figure.
      Figure 1. The SPUFI panel filled in
       DSNESP01                            SPUFI                     SSID: DSN
       ===>
       Enter the input data set name:   (Can be sequential or partitioned)
        1 DATA SET NAME..... ===> EXAMPLES(XMP1)
        2 VOLUME SERIAL..... ===>       (Enter if not cataloged)
        3 DATA SET PASSWORD. ===>       (Enter if password protected)
      
      Enter the output data set name:   (Must be a sequential data set)
        4 DATA SET NAME..... ===> RESULT
      
      Specify processing options:
        5 CHANGE DEFAULTS... ===> Y     (Y/N - Display SPUFI defaults panel?)
        6 EDIT INPUT........ ===> Y     (Y/N - Enter SQL statements?)
        7 EXECUTE........... ===> Y     (Y/N - Execute SQL statements?)
        8 AUTOCOMMIT........ ===> Y     (Y/N - Commit after successful run?)
        9 BROWSE OUTPUT..... ===> Y     (Y/N - Browse output data set?)
      
       For remote SQL processing:
       10 CONNECT LOCATION   ===>
      
      
       PRESS: ENTER to process      END to exit      HELP for more information
    3. Specify new values in any of the other fields on the SPUFI panel.
      For more information about these fields, see The SPUFI panel.
  2. Optional: Change the SPUFI defaults, as described in Changing SPUFI defaults.
  3. Enter SQL statements in SPUFI.
    If the input data set that you specified on the SPUFI panel already contains all of the SQL statements that you want to execute, you can bypass this editing step by specifying NO for the EDIT INPUT field on the SPUFI panel. To enter SQL statements by using SPUFI:
    1. If the EDIT panel is not already open, on the SPUFI panel, specify Y in the EDIT INPUT field and press ENTER. If the input data set that you specified is empty, an empty EDIT panel opens. Otherwise, if the input data set contained SQL statements, those SQL statements are displayed in an EDIT panel.
    2. On the EDIT panel, use the ISPF EDIT program to enter or edit any SQL statements that you want to execute. Move the cursor to the first blank input line, and enter the first part of an SQL statement.
      If the input data set that you specified on the SPUFI panel already contains all of the SQL statements that you want to execute, you can bypass this editing step by specifying NO for the EDIT INPUT field on the SPUFI panel.
      You can enter the rest of the SQL statement on subsequent lines, as shown in the following figure:
      Figure 2. The edit panel: After entering an SQL statement
      EDIT --------userid.EXAMPLES(XMP1) --------------------- COLUMNS 001 072
      COMMAND INPUT ===> SAVE                       SCROLL ===> PAGE
      **********************************  TOP OF DATA  ***********************
      000100 SELECT LASTNAME, FIRSTNME, PHONENO
      000200   FROM DSN8B10.EMP
      000300   WHERE WORKDEPT= 'D11'
      000400   ORDER BY LASTNAME;
      *********************************  BOTTOM OF DATA  *********************
      
      Consider the following rules and recommendations when editing this input data set:
      • Indent your lines and enter your statements on several lines to make your statements easier to read. Entering your statements on multiple lines does not change how your statements are processed.
      • Do not put more than one SQL statement on a single line. If you do, the first statement executes, but Db2 ignores the other SQL statements on the same line. You can put more than one SQL statement in the input data set. Db2 executes the statements in the order in which you placed them in the data set.
      • End each SQL statement with the statement terminator that you specified on the CURRENT SPUFI DEFAULTS panel.
      • Save the data set every 10 minutes or so by entering the SAVE command.
    3. Press the END PF key.
      The data set is saved, and the SPUFI panel is displayed.
  4. Process SQL statements with SPUFI.

    You can use SPUFI to submit the SQL statements in a data set to Db2. To process SQL statements by using SPUFI:

    1. On the SPUFI panel, specify YES in the EXECUTE field.
    2. If you did not just finish using the EDIT panel to edit the input data set as described in "Entering SQL statements in SPUFI," specify NO In the EDIT INPUT field.
    3. Press Enter.
      SPUFI passes the input data set to Db2 for processing. Db2 executes the SQL statement in the input data set and sends the output to the output data set.

      The output data set opens.

      Your SQL statement might take a long time to execute, depending on how large a table Db2 must search, or on how many rows Db2 must process. In this case, you can interrupt the processing by pressing the PA1 key. Then respond to the message that asks you if you really want to stop processing. This action cancels the executing SQL statement. Depending on how much of the input data set Db2 was able to process before you interrupted its processing, Db2 might not have opened the output data set yet, or the output data set might contain all or part of the results data that are produced so far.

Results

SQL statements that exceed resource limit thresholds
Your system administrator might use the Db2 resource limit facility (governor) to set time limits for processing SQL statements in SPUFI. Those limits can be error limits or warning limits.

If you execute an SQL statement through SPUFI that runs longer than this error time limit, SPUFI terminates processing of that SQL statement and all statements that follow in the SPUFI input data set. SPUFI displays a panel that lets you commit or roll back the previously uncommitted changes that you have made. That panel is shown in the following figure.

Figure 3. The resource limit facility error panel
DSNESP04          SQL STATEMENT RESOURCE LIMIT EXCEEDED        SSID: DSN
 ===>
 
 The following SQL statement has encountered an SQLCODE of -905 or -495:
 
Statement text
 
 
 
 
 
 
 
 Your SQL statement has exceeded the resource utilization threshold set
 by your site administrator.
 
 You must ROLLBACK or COMMIT all the changes made since the last COMMIT.
 SPUFI processing for the current input file will terminate immediately
 after the COMMIT or ROLLBACK is executed.
 
  1  NEXT ACTION ===>           (Enter COMMIT or ROLLBACK)
 
 PRESS:  ENTER to process                             HELP for more information
 

If you execute an SQL statement through SPUFI that runs longer than the warning time limit for predictive governing, SPUFI displays the SQL STATEMENT RESOURCE LIMIT EXCEEDED panel. On this panel, you can tell Db2 to continue executing that statement, or stop processing that statement and continue to the next statement in the SPUFI input data set. That panel is shown in the following figure.

Figure 4. The resource limit facility warning panel
DSNESP05          SQL STATEMENT RESOURCE LIMIT EXCEEDED        SSID: DSN
 ===>
 
 The following SQL statement has encountered an SQLCODE of 495:
 
Statement text
 
 
 
 
 
 
 
 You can now either CONTINUE executing this statement or BYPASS the execution
 of this statement. SPUFI processing for the current input file will continue
 after the CONTINUE or BYPASS processing is completed.
 
  1  NEXT ACTION ===>           (Enter CONTINUE or BYPASS)
 
 PRESS:  ENTER to process                             HELP for more information