Determine dynamic SQL syntax
Article ID: 98927
Updated On:
Products
IDMS
IDMS - Database
IDMS - ADS
Issue/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.
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
It may not always be easy to detemine the query.
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.
Resolution
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.
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
Feedback
Yes
No
Powered by
