PTT Open/Close CURSOR Count Values in Thread Terminator Thread SQL Activity Display

Products

• Database Management for DB2 for z/OS - Administration Suite
• Database Management for DB2 for z/OS - Performance Suite
• Database Management for DB2 for z/OS - Recovery Suite
• Database Management for DB2 for z/OS - SQL Performance Suite
• Database Management for DB2 for z/OS - Utilities Suite

Issue/Introduction

The below Thread Terminator Thread SQL Activity Display shows one application opened Cursor 32,323 with zero Closed, but the zPARM is set to Max 1000 only, so how it could open more than 1000 cursor without any db2 error?

20.0   ----- Thread Terminator Thread SQL Activity Display ----- 23/01/19 09:14

Command ==>                                                     Scroll ==> CSR LINE 1 OF 83

Option ====> S  B=Buffer,  S=SQL,   C=SQLCall,  L=Lock, I=Parallelism P=Program, R=Drain, Z=SQLtrace, D=Dist, T=Time

-------------------------------------------------------------------------------

 

DB2 SSID ==> DBXX          Planname ==> DISTSERV       Connid ==> SERVER

Opid ======> PCSFUSR       Authid ====> PCSFUSR        Corrid ==> pcsfees.exe

Conntype ==> DISTRUW       Jobname ===> DBXXDIST       Asid ====> 0154

SQL DML Activity

----------------

Select                         32323      Open Cursor                    32323

Insert                         32323      Fetch                          32323

Update                             0      Close Cursor                       0

Delete                         32323      Describe                           0

Prepare                            0      Describe Table                     0

Merge                              0

Multi-Row SQL DML Activity

--------------------------

Insert                         32323      Fetch                          32323

Update                             0      Delete                             0

SQL Prepare Cache Activity

--------------------------

Cache Locate Success               0      Cache Locate Failed                0

KEEPDYNAMIC(Y) Success             0      Cache Discarded/MAXKEEPD           0

DB2 SSID ==> DBXX         Lmod Name ==> DBXXPARM        Assem Date ==> 01/11/23

-------------------------------------------------------------------------------

  DESCRIPTION                                      VALUE      KEYWORD

  ------------------------------------------------ ---------- -----------------

  Maximum open cursors per thread.                 1000       MAX_NUM_CUR

  Maximum active stored procedures per thread.     2000       MAX_ST_PROC

  Number of parts that REORG/RELOAD will process.  0          MAX_UTIL_PARTS

  Max archive logs recorded in BSDS.               10000      MAXARCH

  Connection queue depth.                          ON         MAXCONQN

Environment

Release : 20.0

Resolution

By default, a cursor is closed as a consequence of a commit operation. This can be overridden by specifying WITH HOLD in the DECLARE cursor statement. But even with WITH HOLD specified, the cursor will be closed at commit time under certain conditions.  Here is IBM doc on this topic for reference.