ISQL : Export SQL captured by a Detector Collection and save it to a dataset
Article ID: 278040
Updated On:
Products
Database Management for DB2 for z/OS - SQL Performance Suite
Detector for DB2 for z/OS
Issue/Introduction
Being able to save some SQL captured by Detector in a dataset may be useful to perform more research with it. If the problem SQL was captured in a production environment then running it again in production might be an issue.
Capturing it to a dataset would allow it to be executed for testing on a non-production environment and perhaps improve its performance particularly if it is using specific host variables that must be used for testing.
Environment
DB2 for Z/OS
Release :R20
Resolution
Having selected the interval to display on Detector..........the Planname Summary Display is displayed.
> --------- DETECTOR Planname Summary Display -------- yy/mm/dd hh:mm
Command ==> Scroll ==> CSR
LINE 1 OF 3
DB2 SSID ==> ssid
View Type ==> A * -Activity X -Exception E -Error O -Object
View By ==> P * -Plan G -Prog S -SQL Q -DSQL F -Prof K -Key Total/Avg ==> T
Interval Date => yy/mm/dd Interval Time => hh:mm:ss Elapsed Time => hh:mm
-------------------------------------------------------------------------------
S -Programs, D -Detail, Q -Dynamic SQL, K -Keys, H -History, T -Active Threads
PLANNAME COMMIT ABORT SQL TIMEPCT CPUPCT INDB2_TIME INDB2_CPU
-------- -------- ------ ---------- ------- ------- ------------ ------------
_ ISQPLNMN 70 17 2007860 99.03% 99.87% 00:42.855275 00:18.266778
_ ISQREG 12 0 223 .90% .09% 00:00.391345 00:00.018157
_ DSNREXX 0 0 5 .06% .02% 00:00.027594 00:00.004523
******************************* BOTTOM OF DATA ********************************
Change the View By field to "S" for SQL display and then enter the "Q" line command against the SQL to be viewed in detail.
> ------- DETECTOR Dynamic SQL Summary Display ------- yy/mm/dd hh:mm
Command ==> Scroll ==> CSR
LINE 1 OF 24
DB2 SSID ==> ssid
View Type ==> A * -Activity X -Exception E -Error O -Object
View By ==> Q P -Plan G -Prog S -SQL * -DSQL F -Prof K -Key Total/Avg ==> T
Interval Date => 24/01/09 Interval Time => 08:09:28 Elapsed Time => 00:19
-------------------------------------------------------------------------------
D -Detail, E -Explain, G -Programs, P -Plans, Q -SQL, K -Keys, T -Table/Index
SQL_TEXT SQL_CALL STMT# SECT# USE_COUNT
-------------------------------- -------- ------- ----- ----------
Q SELECT CUST.CUST_ID,CUST.F_NAME> PREPARE 0000207 00002 1
_ DELETE FROM TRIG_EMP1 WHERE EMP> PREPARE 0000682 00003 11
_ INSERT INTO TRIG_EMP1 VALUES(?,> PREPARE 0000406 00003 10
_ INSERT INTO TRIG_EMP1_VW VALUES> PREPARE 0000504 00003 10
_ UPDATE EMP1 SET L_NAME = 'TEST3> PREPARE 0000639 00003 1
--------- DETECTOR Dynamic SQL Call Text Display -------- yy/mm/dd hh:mm
Command ==> Scroll ==> CSR
DB2 SSID ==> ssid
-------------------------------------------------------------------------------
E -Explain SQL, I -ISQL ==> I
SELECT CUST.CUST_ID , CUST.F_NAME , CUST.L_NAME , ACCT.ACCOUNT_NBR ,
ACCT.ACCOUNT_BALANCE , ACCT.STATUS , TRANS.TRANS_NBR ,
TRANS.AMOUNT , TRANS.CLOSE_DATE
FROM AUTHID.ISQCUST AS CUST , AUTHID.ISQTRANS AS TRANS , AUTHID.ISQCUST_ACCT
AS CUSTACCT , AUTHID.ISQACCT AS ACCT
WHERE CUST.CUST_ID > 0
AND CUSTACCT.CUST_ID = CUST.CUST_ID
AND CUSTACCT.ACCOUNT_NBR = ACCT.ACCOUNT_NBR
AND CUSTACCT.ACCOUNT_NBR = TRANS.ACCOUNT_NBR
******************************* BOTTOM OF DATA ********************************
Then enter "I" in the E -Explain SQL, I -ISQL ==> I field. This transfers to an ISQL screen with the captured SQL.
Enter the "D" command in the SQL screen field : Option ===> D
IQPSQL3O 20.0.10 ------- ISQL Online SQL Execution ------ yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Option ===> D 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 CUST.CUST_ID , CUST.F_NAME , CUST.L_NAME , ACCT.ACCOUNT_NBR ,
ACCT.ACCOUNT_BALANCE , ACCT.STATUS , TRANS.TRANS_NBR ,
TRANS.AMOUNT , TRANS.CLOSE_DATE
FROM AUTHID.ISQCUST AS CUST , AUTHID.ISQTRANS AS TRANS , AUTHID.ISQCUST_ACCT
AS CUSTACCT , AUTHID.ISQACCT AS ACCT
WHERE CUST.CUST_ID > 0
AND CUSTACCT.CUST_ID = CUST.CUST_ID
AND CUSTACCT.ACCOUNT_NBR = ACCT.ACCOUNT_NBR
AND CUSTACCT.ACCOUNT_NBR = TRANS.ACCOUNT_NBR
******************************** BOTTOM OF DATA *******************************
Then on this screen enter the "O" in Operation ===> O
Before pressing enter, fill in the Output dataset, DATA SET NAME with a PDS dataset(member) or sequential dataset that has an LRECL of no more than 80 chars or similar to a PDS
that is used for storing SQL and press enter.
IQPSQLIO ---- ISQL Dataset I/O Specification ---- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
--------------------------------------------------------------------- AUTHID1
Control Options:
Operation ===> O (I - read input dataset, O - write output dataset)
Input dataset:
DATA SET NAME ===> 'AUTHID1.HLQ1.SQL(SELECT)'
VOLUME SERIAL ===> (If not cataloged)
Output dataset:
DATA SET NAME ===> 'AUTHID1.HLQ1.SQL(export)'
VOLUME SERIAL ===> (If not cataloged)
This will send the SQL to the output dataset(member).
Another way is to use the "E" for E - Edit SQL on the initial ISQL screen which transfers to the SQL Editor.
IQPSQL3O 20.0.10 ------- ISQL Online SQL Execution ------ yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Option ===> E 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 CUST.CUST_ID , CUST.F_NAME , CUST.L_NAME , ACCT.ACCOUNT_NBR ,
ACCT.ACCOUNT_BALANCE , ACCT.STATUS , TRANS.TRANS_NBR ,
TRANS.AMOUNT , TRANS.CLOSE_DATE
FROM AUTHID.ISQCUST AS CUST , AUTHID.ISQTRANS AS TRANS , AUTHID.ISQCUST_ACCT
AS CUSTACCT , AUTHID.ISQACCT AS ACCT
WHERE CUST.CUST_ID > 0
AND CUSTACCT.CUST_ID = CUST.CUST_ID
AND CUSTACCT.ACCOUNT_NBR = ACCT.ACCOUNT_NBR
AND CUSTACCT.ACCOUNT_NBR = TRANS.ACCOUNT_NBR
******************************** BOTTOM OF DATA *******************************
Then use the DCREATE command with a "CC" and "CC" around the SQL lines to be exported just like in an ISPF copy.
IQPSQLE1 --- (CAPS ON) --- SQL Editor --- (NULLS ON) --- yyyy/mm/dd hh:mm:ss
COMMAND ===> DCREATE SCROLL ===> CSR
Edit the SQL statements that you wish to test below. When you are done
editing the SQL, hit PF3/15 or enter 'END' to return to the RC/SQL control
panel. Host variables will be parsed out, so that you may test your SQL
repeatedly with various host values.
___
CC_ SELECT CUST.CUST_ID , CUST.F_NAME , CUST.L_NAME , ACCT.ACCOUNT_NBR ,
___ ACCT.ACCOUNT_BALANCE , ACCT.STATUS , TRANS.TRANS_NBR ,
___ TRANS.AMOUNT , TRANS.CLOSE_DATE
___ FROM AUTHID.ISQCUST AS CUST , AUTHID.ISQTRANS AS TRANS , AUTHID.ISQCUST_ACCT
___ AS CUSTACCT , AUTHID.ISQACCT AS ACCT
___ WHERE CUST.CUST_ID > 0
___ AND CUSTACCT.CUST_ID = CUST.CUST_ID
___ AND CUSTACCT.ACCOUNT_NBR = ACCT.ACCOUNT_NBR
___ AND CUSTACCT.ACCOUNT_NBR = TRANS.ACCOUNT_NBR
CC_
******************************* BOTTOM OF DATA ********************************
On the next screen fill in the dataset(member) to receive the SQL lines.
PTEDTCR ------------------ SQL Editor Create ----------------- yyyy/mm/dd hh:mm:ss
COMMAND ===>
--------------------------------------------------------------------- AUTHID1
TO ISPF LIBRARY:
PROJECT ===>
GROUP ===>
TYPE ===>
MEMBER ===> (Blank or pattern for member selection list)
To Other Partitioned data set member:
DATA SET NAME ===> 'AUTHID1.HLQ1.SQL(EXPORT1)'
VOLUME SERIAL ===> (If not cataloged)
PF3 back to execute this copy.
One other method is to go from the SQL Editor directly to ISPF using the ISPFEDIT command.
IQPSQLE1 --- (CAPS ON) --- SQL Editor --- (NULLS ON) --- yyyy/mm/dd hh:mm:ss
COMMAND ===> ISPFEDIT SCROLL ===> CSR
Edit the SQL statements that you wish to test below. When you are done
editing the SQL, hit PF3/15 or enter 'END' to return to the RC/SQL control
panel. Host variables will be parsed out, so that you may test your SQL
repeatedly with various host values.
___
___ SELECT CUST.CUST_ID , CUST.F_NAME , CUST.L_NAME , ACCT.ACCOUNT_NBR ,
___ ACCT.ACCOUNT_BALANCE , ACCT.STATUS , TRANS.TRANS_NBR ,
___ TRANS.AMOUNT , TRANS.CLOSE_DATE
___ FROM AUTHID.ISQCUST AS CUST , AUTHID.ISQTRANS AS TRANS , AUTHID.ISQCUST_ACCT
___ AS CUSTACCT , AUTHID.ISQACCT AS ACCT
___ WHERE CUST.CUST_ID > 0
___ AND CUSTACCT.CUST_ID = CUST.CUST_ID
___ AND CUSTACCT.ACCOUNT_NBR = ACCT.ACCOUNT_NBR
___ AND CUSTACCT.ACCOUNT_NBR = TRANS.ACCOUNT_NBR
___
******************************* BOTTOM OF DATA ********************************
Then using the standard ISPF CREATE command with "CC" and "CC" create a new member with the
SQL on it.
EDIT ---- PTI.SQL -------------------------------------------- Columns 001 072
COMMAND ===> create SCROLL ===> CSR
****** ***************************** Top of Data ******************************
cc0001
000002 SELECT CUST.CUST_ID , CUST.F_NAME , CUST.L_NAME , ACCT.ACCOUNT_NBR ,
000003 ACCT.ACCOUNT_BALANCE , ACCT.STATUS , TRANS.TRANS_NBR ,
000004 TRANS.AMOUNT , TRANS.CLOSE_DATE
000005 FROM AUTHID.ISQCUST AS CUST , AUTHID.ISQTRANS AS TRANS , AUTHID.ISQCUST_ACCT
000006 AS CUSTACCT , AUTHID.ISQACCT AS ACCT
000007 WHERE CUST.CUST_ID > 0
000008 AND CUSTACCT.CUST_ID = CUST.CUST_ID
000009 AND CUSTACCT.ACCOUNT_NBR = ACCT.ACCOUNT_NBR
000010 AND CUSTACCT.ACCOUNT_NBR = TRANS.ACCOUNT_NBR
cc0011
****** **************************** Bottom of Data ****************************
and on the next screen........enter the target dataset name(member).
Menu RefList Utilities Help
Edit/View - Create
Command ===>
"Current" Data Set: PTI.SQL
To ISPF Library:
Project . . . AUTHID1
Group . . . . HLQ1
Type . . . . SQL
Member . . . EXPORT2
Viewing these three members with ISPF edit the SQL can be seen:
DSLIST AUTHID1.HLQ1.SQL(EXPORT) - 01.00 Columns 00001 00072
Command ===> Scroll ===> CSR
****** ***************************** Top of Data ******************************
000001
000002 SELECT CUST.CUST_ID , CUST.F_NAME , CUST.L_NAME , ACCT.ACCOUNT_NBR ,
000003 ACCT.ACCOUNT_BALANCE , ACCT.STATUS , TRANS.TRANS_NBR ,
000004 TRANS.AMOUNT , TRANS.CLOSE_DATE
000005 FROM AUTHID.ISQCUST AS CUST , AUTHID.ISQTRANS AS TRANS , AUTHID.ISQCUST_ACCT
000006 AS CUSTACCT , AUTHID.ISQACCT AS ACCT
000007 WHERE CUST.CUST_ID > 0
000008 AND CUSTACCT.CUST_ID = CUST.CUST_ID
000009 AND CUSTACCT.ACCOUNT_NBR = ACCT.ACCOUNT_NBR
000010 AND CUSTACCT.ACCOUNT_NBR = TRANS.ACCOUNT_NBR
000011
****** **************************** Bottom of Data ****************************
DSLIST AUTHID1.HLQ1.SQL(EXPORT1) - 01.00 Columns 00001 00072
Command ===> Scroll ===> CSR
****** ***************************** Top of Data ******************************
000001 SELECT CUST.CUST_ID , CUST.F_NAME , CUST.L_NAME , ACCT.ACCOUNT_NBR ,
000002 ACCT.ACCOUNT_BALANCE , ACCT.STATUS , TRANS.TRANS_NBR ,
000003 TRANS.AMOUNT , TRANS.CLOSE_DATE
000004 FROM AUTHID.ISQCUST AS CUST , AUTHID.ISQTRANS AS TRANS , AUTHID.ISQCUST_ACCT
000005 AS CUSTACCT , AUTHID.ISQACCT AS ACCT
000006 WHERE CUST.CUST_ID > 0
000007 AND CUSTACCT.CUST_ID = CUST.CUST_ID
000008 AND CUSTACCT.ACCOUNT_NBR = ACCT.ACCOUNT_NBR
000009 AND CUSTACCT.ACCOUNT_NBR = TRANS.ACCOUNT_NBR
000010
****** **************************** Bottom of Data ****************************
File Edit Edit_Settings Menu Utilities Compilers Test Help
DSLIST AUTHID1.HLQ1.SQL(EXPORT2) - 01.00 Columns 00001 00072
Command ===> Scroll ===> CSR
****** ***************************** Top of Data ******************************
000001
000002 SELECT CUST.CUST_ID , CUST.F_NAME , CUST.L_NAME , ACCT.ACCOUNT_NBR ,
000003 ACCT.ACCOUNT_BALANCE , ACCT.STATUS , TRANS.TRANS_NBR ,
000004 TRANS.AMOUNT , TRANS.CLOSE_DATE
000005 FROM AUTHID.ISQCUST AS CUST , AUTHID.ISQTRANS AS TRANS , AUTHID.ISQCUST_ACCT
000006 AS CUSTACCT , AUTHID.ISQACCT AS ACCT
000007 WHERE CUST.CUST_ID > 0
000008 AND CUSTACCT.CUST_ID = CUST.CUST_ID
000009 AND CUSTACCT.ACCOUNT_NBR = ACCT.ACCOUNT_NBR
000010 AND CUSTACCT.ACCOUNT_NBR = TRANS.ACCOUNT_NBR
000011
****** **************************** Bottom of Data ****************************
Note that in DETECTOR there are a number of different locations where the DETECTOR Dynamic SQL Call Text Display screen can be accessed and once there , the "I" for ISQL can be used. This document demonstrates just one way to do this. Usually this function is available on a screen that displays the "Q -SQL text" option. This example was on the DETECTOR Dynamic SQL Call Text Display.
Other places are on the SQL Call Text Display, Pgm Dynamic SQL Display and SQL Error Call Text display.
Additional Information
Feedback
Yes
No
Powered by
