Listing Datacom Open SQL Cursors

Products

• Datacom/DB
• Datacom/AD
• Datacom
• Datacom/Server

Issue/Introduction

How to determine the source and number of open SQL cursors in Datacom. We want to identify programs that have not closed cursors.

Resolution

The SQL_STATUS_FRONT dynamic system table allows viewing the source of executing SQL statements and open cursors waiting for further fetches.
You MUST disable the SQL SOURCE CACHE when attempting to generate and retrieve this data. Please note that this will slightly increase MUF CPU usage.

Steps to retrieve the list of query source(s) and open cursor(s) in the MUF environment:

1. Disable the source cache; there are two options to achieve this.
   
   
   1. Update the MUF startup option "SQL_SOURCE_CACHE_STMTS " and set it 0 and recycle the MUF
      
      OR
      
      
   2. After MUF startup, execute the following query:
      
      INSERT INTO sysadm.sql_console values (CURRENT DATACOM MUF_NAME,
'SQL_SOURCE_CACHE_STMTS 0');
      
      
2. Ensure the applications generate enough traffic; allow 4-5 hours for the MUF to accumulate the required data. Intention here is to retrieve the open  cursors after the Source Cache is disabled.
   
   
3. Submit the following query to retrieve the source and currently open cursors:
   
   SELECT JOB_NAME, COUNT(*), TRIM(SUBSTR(SOURCE_FRONT, 53, 3847)) AS SOURCE
FROM SYSADM.SQL_STATUS_FRONT
GROUP BY JOB_NAME, TRIM(SUBSTR(SOURCE_FRONT, 53, 3847))
ORDER BY 1, 2 DESC;
   
   The query groups the source text to count duplicates, listing the most duplicated queries first. For example:
   
   JOB_NAME COUNT(*) SOURCE
---------------------------------------------------
Job1     2 SELECT ID, DESCC FROM TBL233 FETCH FIRST 1 ROWS ONLY
Job2     2 SELECT ID, DESCC FROM TBL233 FETCH FIRST 1 ROWS ONLY
Job3     1 SELECT ID, DESCC FROM TBL233 FETCH FIRST 1 ROWS ONLY


4. Finally re-enable the SOURCE CACHE.
   • Update the MUF startup option "SQL_SOURCE_CACHE_STMTS " and set it to 1000, then recycle the MUF
   
   OR
   
   • Execute the following query:
   INSERT INTO sysadm.sql_console values (CURRENT DATACOM MUF_NAME,
'SQL_SOURCE_CACHE_STMTS 1000');

Review the result data to identify queries with open cursors and updating the application to gracefully close the cursor, statement, or connection as required.

Additional Information

See Datacom documentation section SQL_STATUS_FRONT (SQF) and SQL_SOURCE_CACHE_STMTS.