When does the DECLARE CURSOR statement executes. As far my understanding, the DECLARE CURSOR statement is a non executable statement.
One of our job was executing for a long time. It had a DECLARE CURSOR statement and it was joining 2 tables and it is a simple join. Both the tables have around 200 million rows. The job used to run within an hour or so. Last time it ran upto 20 hrs. When contacted the DB2 DBA, they told that there are 7400 declares and 6000 selects. The program has only one declare cursor statement.
So wanted to know if the DECLARE statement will execute once or for each select statement.
1 Billion Getpages with 6000 Selects. So each select scans a table with 166666 pages, problem with your join.
Use the below query to review access paths, look for Tablespace scans or non-matching index scans. Best to work with DBA.
Before running the query specify ( plan_table_qualifier, collection_name, program name[xxxx,yyyy])
SET CURRENT SQLID='plan_table_qualifier';
SUBSTR(PROGNAME,1,10) AS PG ,
CAST(QUERYNO AS CHAR(4)) AS QNO ,
CAST(QBLOCKNO AS CHAR(2)) AS QBNO ,
CAST(PLANNO AS CHAR(2)) AS PNO ,
CAST(METHOD AS CHAR(1)) AS MTHD ,
SUBSTR(CREATOR,1,6) AS CTR ,
SUBSTR(TNAME,1,7) AS TAB ,
CAST(TABNO AS CHAR(2)) AS TNO ,
ACCESSTYPE AS AT ,
CAST(MATCHCOLS AS CHAR(2)) AS MC ,
SUBSTR(ACCESSCREATOR,1,6) AS AC ,
SUBSTR(ACCESSNAME,1,8) AS AN ,
INDEXONLY AS IO ,
PREFETCH AS PF ,
PAGE_RANGE AS PR
FROM PLAN_TABLE A
A.COLLID = 'collection_name' AND
A.BIND_TIME = ( SELECT MAX(BIND_TIME)
COLLID = A.COLLID AND
PROGNAME = A.PROGNAME )
ORDER BY QUERYNO,