Determine dynamic SQL syntax

Document ID : KB000098927
Last Modified Date : 31/05/2018
Show Technical Document Details
Introduction:
Sometimes an SQL query may be unexpectedly running for a long time, performing many I/Os and consuming system resources.
It may not always be easy to detemine the query.
 
Background:
The SYSIDMS parameter SAVE_SQL_SYNTAX was introduced during the r18.0 timeframe with APAR RO46968.
With SAVE_SQL_SYNTAX=ON in SYSIDMS, the syntax of any dynamic SQL statement (e.g. OCF, IDMSBCF, JDBC, ODBC) will be saved in storage off the secondary LTE for the SQL session.
For more documentation, see RI49106 or the following page: https://docops.ca.com/ca-idms/19/en/using/using-common-facilities/sysidms-parameter-file-common-facilities/sysidms-parameter-descriptions
Environment:
CA IDMS, all supported releases.
Instructions:
The intention of the SAVE_SQL_SYNTAX option is that a user exit can be written, for example 29 or 39, to extract that syntax.
However, if the problem situation is such that the query runs for a long time, it might be possible to determine the syntax online.

While the task is running, use the command OPER W DB to detemine which task is performing all the I/O (large value in the PageRead column).
Then type the command W AC TA in the bottom line of the OPER screen and observe the value in the TERMINAL column for the same task.

Plug that terminal name into the following command and execute it from the ENTER NEXT TASK CODE (i.e. outside of OPER).

DCMT D MEM LTE terminal +28 % +150 % 600
 
The resulting display should show the storage block containing the SQL statement.
Additional Information: