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.
Procedure
To execute SQL by using SPUFI:
- Open SPUFI and specify the initial options.
- Optional: Changing SPUFI defaults
- Enter SQL statements in SPUFI.
- Process SQL statements with SPUFI.
Results
To being using SPUFI, you need to open and fill out the SPUFI panel.
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.
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 - Optional: Specify new values in any of the other fields on the SPUFI panel. For more information about these fields, see The SPUFI panel.
After you open SPUFI, specify the initial options, and optionally change any SPUFI defaults, you can enter one or more SQL statements to execute.
Before you begin this task, you must complete the task "Opening SPUFI and specifying 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.
- 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.
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 DSN8A10.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.
- Press the END PF key. The data set is saved, and the SPUFI panel is displayed.
You can use SPUFI to submit the SQL statements in a data set to DB2.
- Complete the task "Opening SPUFI and specifying initial options."
- Ensure that the input data set contains the SQL statements that you want to execute.
- 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.
For information about how to interpret the output in the output data set, see Output from SPUFI.
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.
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.
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