Can CA Datacom produce a report of the access path for a SQL statement similar to a DB2 EXPLAIN?
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:
To print the trace run DBUTLTY with:
Alternatively run DBSQLPR to query the SYSADM.SYSMSG table:
SELECT * FROM SYSADM.SYSMSG WHERE PLANNAME = 'plan_name';
See DocOps section SQL Query Optimization Messages