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:
Open SPUFI and specify the initial options. To open SPUFI and specify initial options:
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.
If the length of an SQL statement is greater than 71 bytes for an input data set with record length 79, or 72 bytes for an input data set with record length 80, you need to continue the SQL statement on additional lines of the SPUFI input data set. SPUFI concatenates the text on multiple lines without adding extra spaces at the end of any line. Therefore, if an SQL statement contains two values with a space between them, and the first value ends at the last allowed input position (71 or 72), you need to add an extra space on the next line before the second value.
For example, suppose that the record length of your SPUFI input data set is 80, so the maximum length of an input line is 72. Also suppose that the SQL statement that you wish to enter is 81 bytes long. Bytes 69 through 81 contain FROM MYTABLE;. If you split the SQL statement after FROM, the first line of the statement ends in column 72, so you need to include a space in column 1 of the next line, before MYTABLE;. Otherwise, when SPUFI concatenates the two lines, the result is FROMMYTABLE;. When SPUFI runs the SQL statement, the SQL statement fails with SQLCODE -104.
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.
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.
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.