The PLAN ANALYSIS function of File-AID for DB2 enables you to examine the performance efficiency of your SQL statements. File-AID for DB2 invokes the EXPLAIN function of DB2 to analyze SQL. Plan Analysis then produces a formatted analysis report that you can view online.
Follow the below steps to generate "plain english" access path for your SQL query.
Follow the below steps to generate "plain english" access path for your SQL query.
Select “4” from the File-aid/Db2 main menu
File-AID for DB2 - 6.2 ------ Primary Option Menu --------------- Enter option
OPTION ===> 4
More: +
0 DEFAULTS and USER PARMS USERID - TXXBXXX
1 BROWSE TIME - 20:02
2 EDIT TERMINAL - 3278
3 UTILITIES PF KEYS - 24
3.1 - Object Administration CCSID - 00037
3.2 - Create, Model, Drop, Alter DB2 Objects DB2 SSID ===> DBXT
3.3 - Copy Rows Between Tables
3.4 - Object List Processing
3.5 - SQL Command Manager
3.6 - Display, Grant or Revoke DB2 Privileges
3.7 - Extract and Load Tables
3.8 - DBA-XPERT Column Impact Analysis
3.9 - Batch Execute SQL with Auto-COMMIT Processing
4 SQL ANALYSIS - SQL Development and Analysis
5 PRINT - Print table Data or Audit Trail
6 RELATIONSHIPS - Relationship Facilitator
7 DATA DISGUISE - Define Disguise Criteria and Disguise Extract files
R File-AID/RDX - Transfer to File-AID/Related Data XPERT
XT XPEDITER - Transfer to XPEDITER
First we need to create the Explain tables under under our TSO id. So, for that select “0” in the below screen.
File-AID for DB2 ----------- SQL Analysis and Development Menu ---- SSID: DBXT
OPTION ===> 0
0 Create or Maintain Explain Table(s)
1 SQL Plan Analysis
2 Review or Print DB2 Explain Reports
3 ISPF/PDF SQL Source Development and Analysis
Select “1” in the below screen.
File-AID for DB2 ----- Create or Maintain Explain Table(s) --------------------
OPTION ===> 1 Scroll ===> CSR
SSID: DBXT
Plan_Table AUTHID ===> TXXBXXX Authorization ID for all EXPLAIN tables
1 Create Explain Table(s)
2 Alter or Upgrade Explain Table(s)
3 Delete rows from Explain Table(s)
(Explain Tables: PLAN_TABLE, F2PLAN_TABLE, DSN_STATEMNT_TABLE,
DSN_FUNCTION_TABLE)
Key in the below highlighted fields
File-AID for DB2 ----------- Create Explain Tables ---------------------------
COMMAND ===> Scroll ===> CSR
SSID: DBXT
Select the tables below to be created.
Plan_Table AUTHID ===> TXXBXXX Authorization ID for all EXPLAIN tables
Required Tables:
Specify the Database and Tablespace for PLAN_TABLE
Database ===> MYTESTDB
Tablespace ===> (blank for implicit Tablespace)
Specify the Database and Tablespace for F2PLAN_TABLE
Database ===> MYTESTDB
Tablespace ===> (blank for implicit Tablespace)
Optional Tables:
_ Specify the Database and Tablespace for DSN_STATEMNT_TABLE
Database ===>
Tablespace ===> (blank for implicit Tablespace)
_ Specify the Database and Tablespace for DSN_FUNCTION_TABLE
Database ===> (This table requires at least a 8K buffer
Tablespace ===> pool to be created in DB2 V8 & above)
Instructions:
Press ENTER to continue or END to cancel.
Once we press ENTER in the above screen, the explain tables will be created.
File-AID for DB2 ----- Create or Maintain Explain Tab Explain Tables Created
OPTION ===> Scroll ===> CSR
SSID: DBXT
Plan_Table AUTHID ===> TXXBXXX Authorization ID for all EXPLAIN tables
1 Create Explain Table(s)
2 Alter or Upgrade Explain Table(s)
3 Delete rows from Explain Table(s)
(Explain Tables: PLAN_TABLE, F2PLAN_TABLE, DSN_STATEMNT_TABLE,
DSN_FUNCTION_TABLE)
Press F3 in the above screen to reach the below screen and select option “3”
File-AID for DB2 ----------- SQL Analysis and Development Menu ---- SSID: DBXT
OPTION ===> 3
0 Create or Maintain Explain Table(s)
1 SQL Plan Analysis
2 Review or Print DB2 Explain Reports
3 ISPF/PDF SQL Source Development and Analysis
Type the dataset which has the SQL to be tested
File-AID for DB2 -------------- SQL ANALYSIS EDIT ENTRY ----------- SSID: DBXT
COMMAND ===>
ISPF LIBRARY:
PROJECT ===>
GROUP ===>
TYPE ===>
MEMBER ===> (Blank or pattern for member selection list)
OTHER PARTITIONED OR SEQUENTIAL DATASET:
DATASET NAME ===> 'TXXBXXX.TSTS.TEST(MYSQL)’
VOLUME SERIAL ===> (If not cataloged)
DATASET PASSWORD ===> (If password protected)
INITIAL MACRO ===>
Once we press ENTER, We will get the below screen.
EDIT ---- TXXBXXX.TSTS.TEST(MYSQL) - 01.00 --------------------- Columns 001 072
COMMAND ===> SCROLL ===> CSR
****** ***************************** Top of Data ******************************
=NOTE= IN ADDITION TO ISPF COMMANDS, FOUR LINE COMMANDS ARE
=NOTE= PROVIDED THAT ENABLE YOU TO ANALYZE SQL. THESE COMMANDS
=NOTE= ARE: EX EXECUTE, XP EXPLAIN, BR BROWSE, ED EDIT.
=NOTE= TO INITIATE THE ACTION AFTER ENTERING THE LINE COMMAND ON
=NOTE= AN SQL STATEMENT, TYPE FADB2 ON THE COMMAND LINE OR PRESS
=NOTE= THE PFK THAT IS ASSIGNED TO FADB2.
=NOTE=
000001 SELECT *
000002 FROM MYTESTDB.MYTAB_DATA
000003 WHERE MYTAB_TBL_ID = 'XX918'
000004 AND MYTAB_KEY_DATA IN (
000005 'XX2070301',
000006 'XX2080301',
000007 'XX6000801',
000008 'XX6010801',
000009 'XX6020601',
000010 'XX6031001',
000011 'XX6090201',
000012 'XX6110901',
000013 'XX6270301',
Type “.XP” against the first line of the SQL query and type “FADB2” in the command line
EDIT ---- TXXBXXX.TSTS.SOS(SQL6) - 01.00 --------------------- Columns 001 072
COMMAND ===> FADB2 SCROLL ===> CSR
****** ***************************** Top of Data ******************************
=NOTE= IN ADDITION TO ISPF COMMANDS, FOUR LINE COMMANDS ARE
=NOTE= PROVIDED THAT ENABLE YOU TO ANALYZE SQL. THESE COMMANDS
=NOTE= ARE: EX EXECUTE, XP EXPLAIN, BR BROWSE, ED EDIT.
=NOTE= TO INITIATE THE ACTION AFTER ENTERING THE LINE COMMAND ON
=NOTE= AN SQL STATEMENT, TYPE FADB2 ON THE COMMAND LINE OR PRESS
=NOTE= THE PFK THAT IS ASSIGNED TO FADB2.
=NOTE=
.XP SELECT *
000002 FROM MYTESTDB.MYTAB_DATA
000003 WHERE MYTAB_TBL_ID = 'XX918'
000004 AND MYTAB_KEY_DATA IN (
000005 'XX2070301',
000006 'XX2080301',
000007 'XX6000801',
000008 'XX6010801',
000009 'XX6020601',
000010 'XX6031001',
000011 'XX6090201',
000012 'XX6110901',
000013 'XX6270301',
Select “1” in the below screen for access path analysis
File-AID for DB2 - Explain - SQL Development and Analysis -- Row 1 to 11 of 13
OPTION ===> 1 SCROLL ===> PAGE
SSID: DBXT
1 - Access Path Analysis 4 - Object Information
2 - Explain Detail 5 - Cost Detail
3 - Predicate Analysis 6 - Function Detail
DBRM/Package QUERYNO Type DB2 Cost * PROCSU Access Type
------------ --------- ------ --------- - --------- ------------------------
<SOURCE> 1 SELECT 5 <N/A> IN-list Index Scan
SQL Statement: 1 OF 1
SELECT *
FROM MYTESTDB.MYTAB_DATA
WHERE MYTAB_TBL_ID = 'XX918'
AND MYTAB_KEY_DATA IN ( 'XX2070301', 'XX2080301', 'XX6000801', 'XX6010801',
'XX6020601', 'XX6031001', 'XX6090201', 'XX6110901', 'XX6270301',
You can the see the access path explained in plain English in the below screen
File-AID for DB2 ------- PLAN_TABLE Analysis Report for: <SOURCE>
COMMAND ===> SCROLL ===> CSR
Use SQL to View/Save/Re-explain the SQL Source
DB2 VERSION: 9.1.5
QUERY NO : 1
CURR SCHEMA: TXXBXXX
Conditions
Query Block No: 1
Plan No: 1
Query Block Type: SELECT
01 - The following table will be the first table accessed in this
query block.
TB Creator: MYTESTDB
Table: MYTAB_DATA
10 - The following table
will be accessed through the following index
TB Creator: MYTESTDB
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.