CA Datacom SQL Path Report like DB2 EXPLAIN

Products

• Datacom
• DATACOM - AD
• CIS
• COMMON SERVICES FOR Z/OS
• 90S SERVICES
• DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS
• COMMON PRODUCT SERVICES COMPONENT
• Common Services
• CA ECOMETER SERVER COMPONENT FOC
• Easytrieve Report Generator for Common Services
• INFOCAI MAINTENANCE
• IPC
• UNICENTER JCLCHECK COMMON COMPONENT
• Mainframe VM Product Manager
• CHORUS SOFTWARE MANAGER
• CA ON DEMAND PORTAL
• CA Service Desk Manager - Unified Self Service
• PAM CLIENT FOR LINUX ON MAINFRAME
• MAINFRAME CONNECTOR FOR LINUX ON MAINFRAME
• GRAPHICAL MANAGEMENT INTERFACE
• WEB ADMINISTRATOR FOR TOP SECRET
• Xpertware

Issue/Introduction

Can CA Datacom produce a report of the access path for a SQL statement similar to a DB2 EXPLAIN?
 

Environment

Release:
Component: DB

Resolution

There is no EXPLAIN command in CA Datacom but a report on the access path can be obtained from the SQL Query Optimization Messages.
The optimization messages are produced by turning on traces TRACE=TRACEMSG and TRACE=TRACEDETAIL
The messages are written to the PXX and to the SYSADM.SYSMSG table. 

To turn on the traces run DBUTLTY with:

COMM OPTION=ALTER,TRACE=NONE
COMM OPTION=CLRPXX
COMM OPTION=ALTER,TRACE=TRACEMSG,JOBNAME=jobname
COMM OPTION=ALTER,TRACE=TRACEDETAIL,JOBNAME=jobname

Run the application or run the query with DBSQLPR then turn off the trace by running DBUTLTY with:

COMM OPTION=ALTER,TRACE=NONE

To print the trace run DBUTLTY with:

REPORT AREA=PXX,DUMPS=TRACE

Alternatively run DBSQLPR to query the SYSADM.SYSMSG table:

SELECT * FROM SYSADM.SYSMSG WHERE PLANNAME = 'plan_name';

Additional Information

See DocOps section SQL Query Optimization Messages