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
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.
To execute SQL by using SPUFI:
- Open SPUFI and specify the initial options. To open SPUFI and specify initial options:
- Select SPUFI from the DB2I Primary Option Menu as shown in The DB2I primary option menu. The SPUFI panel is displayed.
- 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.
- Specify new values in any of the other fields on the SPUFI panel. For more information about these fields, see The SPUFI panel.
- Select SPUFI from the DB2I Primary Option Menu as shown in The DB2I primary option menu.
- Optional: Change the SPUFI defaults, as described in Changing SPUFI defaults.
- 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:
- 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.
- 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: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.
- Press the END PF key. The data set is saved, and the SPUFI panel is displayed.
- 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:
- On the SPUFI panel, specify YES in the EXECUTE field.
- 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.
- 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.
- 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.
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.