Execute SQL using Interactive SQL online and batch
Article ID: 279128
Updated On: 09-19-2024
Products
Issue/Introduction
Database Management Solutions for Db2 for z/OS Interactive SQL (ISQL) is able to execute SQL online or in batch using Batch Processor.
Resolution
From the Value Pack menu select Interactive SQL.
Value Pack
_ B Batch Processor
_ C DB2 Command Processor
_ I Interactive SQL
IQPSQL3O 20.0.10 ------- ISQL Online SQL Execution ------ yyyy/mm/dd hh:mm:s
COMMAND ===> SCROLL ===> CSR
Option ===> * Mode ===> O ONLINE
DB2 SSID ===> ssid DB2 Version ===> 121M500
----------------------------------------------------------------------- authid1
OPTIONS: CONTROL PARAMETERS:
S - SQL Execution Select Row Limit ===>
D - Dataset I/O Max Character Size ===>
BP - Batch Processor/Submit Commit or Rollback ===> R (C or R)
E - Edit SQL Continue if Warnings ===> N (Y or N)
Continue if SQL Errors ===> N (Y or N)
Output to Dataset ===> Y (Y or N)
View Audit File ===> Y (Y or N)
SQL Format ===> S (S,C or P)
SQL TO BE EXECUTED:
The first task to complete is to obtain the SQL to be executed. Using the "D" option above takes the user to this screen.
IQPSQLIO ---- ISQL Dataset I/O Specification ---- yyyy/mm/dd hh:mm:s
COMMAND ===> SCROLL ===> CSR
--------------------------------------------------------------------- authid1
Control Options:
Operation ===> (I - read input dataset, O - write output dataset)
Input dataset:
DATA SET NAME ===> 'hlq.SQL(SELECT)'
VOLUME SERIAL ===> (If not cataloged)
Output dataset:
DATA SET NAME ===> 'hlq.SQL(report)'
VOLUME SERIAL ===> (If not cataloged)
On the above screen the input and output datasets are defined. They must exist already. These are the input SQL and the Output SQL dataset for the edited SQL.
Make sure that quotes are used on the dataset names so that your authid is not appended to the front of the dataset name.
To read in the SQL use the "I" option on the Operation field above and enter the Input Dataset name.
The result is returned to the previous screen and the SQL displayed...
IQPSQL3O 20.0.10 ------- ISQL Online SQL Execution ------ yyyy/mm/dd hh:mm:s
COMMAND ===> SCROLL ===> CSR
IQ490 IQ490I: The requested dataset or member has been read successfully.
Option ===> * Mode ===> O ONLINE
DB2 SSID ===> ssid DB2 Version ===> 121M500
----------------------------------------------------------------------- authid1
OPTIONS: CONTROL PARAMETERS:
S - SQL Execution Select Row Limit ===>
D - Dataset I/O Max Character Size ===>
BP - Batch Processor/Submit Commit or Rollback ===> R (C or R)
E - Edit SQL Continue if Warnings ===> N (Y or N)
Continue if SQL Errors ===> N (Y or N)
Output to Dataset ===> Y (Y or N)
View Audit File ===> Y (Y or N)
SQL Format ===> S (S,C or P)
SQL TO BE EXECUTED:
SELECT * FROM authid1.TBEMP <===========the SQL has been read into the work area.
WHERE EMPNO IN (10000, 90000)
At this point the SQL can be edited with the "E" option which goes into the SQL Editor.
Review the CONTROL Parameters using the online help(PF1) in particular if a ROLLBACK is required and the "Output to Dataset" should be "Y". Viewing the Audit file should also
be "Y" to see the report.
If the SQL that was previously INPUT above has been EDITED since then, the changed SQL can be output to the OUTPUT Dataset that was previously defined using
the O - write output dataset option on ISQL Dataset I/O Specification screen.
To execute the SQL use the "S" option.
The next screen is to define the Output dataset for the SQL execution report.
IQSQLOD -- ISQL Select Data Set Specification -- yyyy/mm/dd hh:mm:s
COMMAND ===>
--------------------------------------------------------------------- authid1
Output Data Set for Select (Must be a sequential data set)
Data Set Name ===> 'hlq.SQLOUT3'
Output data set characteristics
Record Length ==> 4092 (LRECL - logical record length)
Block Size ==> 4096 (Maximum length of one block)
Record Format ==> VB (F, FB, FBA, V, VB, or VBA)
Device Type ==> SYSDA (Generic DASD unit name)
Primary Space ==> 5 (for space allocation)
Secondary Space ==> 1 (for space allocation)
Then press PF3 to go execute the SQL and display the execution report.
--------------- RC/SQL - Browse Select Results -------------- yyyy/mm/dd hh:mm:s
COMMAND ===> SCROLL ===> CSR
2 ROWS RETRIEVED
EMPNO FIRST_NAME
10000 AAA
90000 BBBBB
******************************* BOTTOM OF DATA ********************************
PF3 again.........displays the Batch Processor execution report. Note that Batch Processor can execute SQL online like SPUFI.
BPPAUDT ----------- AUDIT Message File ----------- yyyy/mm/dd hh:mm:s
COMMAND ===> SCROLL ==> CSR
------------------------------------------------------------- USER ID: authid1
.LIST TERM
RETCODE = 0
.CONNECT ssid
BPA0198I: CURRENT FUNCTION LEVEL IS V12R1M500
RETCODE = 0
.ALLOC FI(PTISELDD) DA('hlq.SQLOUT3') + <====The output report dataset is assigned to DD PTISELDD
OLD
RETCODE = 0
.OPTION NOERRORS NOSQLERRORS NOLOG SQLFORMAT(SQL)
RETCODE = 0
SELECT * FROM authid1.TBEMP
WHERE EMPNO IN (10000, 90000)
;
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
ROLLBACK WORK ; <===============the work is rolled back as specified previously.
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
.FREE FI(PTISELDD)
RETCODE = 0
CONTROL PROCESS CARD:
BATCH PROCESSOR PLAN ===> RBPAP200
BATCH PROCESSOR ID ===> (NONE)
STRATEGY NAME ===> (NONE)
DEVICE ALLOCATION UNIT => SYSDA
***** BATCH PROCESSOR COMPLETE *****
******************************** BOTTOM OF DATA *******************************
Looking at the output file using ISPF displays the audit report that was written to it during the execution of the SQL.
DSLIST HLQ.SQLOUT3 Line 0000000000 Col 001 080
Command ===> Scroll ===> CSR
********************************* Top of Data **********************************
SELECT *
FROM authid1.TBEMP
WHERE EMPNO IN (10000,90000)
---------+---------+---------+---------+---------+---------+---------+---------+
2 ROWS RETRIEVED
---------+---------+---------+---------+---------+---------+---------+---------+
EMPNO FIRST_NAME MIDDLE_NAME
---------+---------+---------+---------+---------+---------+---------+---------+
10000 AAA BBBBB
90000 CCCCC DDDD
******************************** Bottom of Data ********************************
Additional Information
Feedback
