Determine dynamic SQL syntax
search cancel

Determine dynamic SQL syntax

book

Article ID: 98927

calendar_today

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

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.

Additional Information

SYSIDMS Parameter Descriptions
OPER Task Commands
DCMT DISPLAY MEMORY Command