SQL Query Optimization Messages › Requesting Messages

Requesting Messages

You specify that SQL Query Optimization Messages be generated by either using the MSG= plan option (see CA Datacom/DB SQL Preprocessors) or by using the COMM function of CA Datacom/DB Utility (DBUTLTY) as follows.

For summary level:

 COMM OPTION=ALTER,TRACE=TRACEMSG,JOBNAME=xxxxxxxx

For detail level, also turn on:

 COMM OPTION=ALTER,TRACE=TRACEDETAIL,JOBNAME=xxxxxxxx

When triggered by CA Datacom/DB Utility (DBUTLTY) traces, the messages are written both to SYSADM.SYSMSG and to the PXX. For more information on using the CA Datacom/DB Utility (DBUTLTY), see the CA Datacom/DB DBUTLTY Reference Guide.

Note: CA Dataquery deletes the messages from the previous query when the next query is executed if the plan option to generate messages is not turned off. Therefore, to save the messages turn off the message option and run:

SELECT * FROM SYSADM.SYSMSG

You can optionally use the WHERE clause to read only rows for the desired plan. After the SELECT statement has been run, use the DQRY STORE command to store the result in a different table. Alternately, before preparing another query (and without signing off DQRY) use any other method, such as DDOL, to query the SYSADM.SYSMSG table.