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 ==> DB3P Planname ==> DISTSERV Connid ==> SERVER
Opid ======> PCSFUSR Authid ====> PCSFUSR Corrid ==> pcsfees.exe
Conntype ==> DISTRUW Jobname ===> DB3PDIST 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 ==> DB3P Lmod Name ==> DB3PPARM 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
Release : 20.0
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.