|
View previous topic :: View next topic
|
| Author |
Message |
ravikumar15
New User
Joined: 27 Mar 2006
Posts: 68
|
|
|
|
Hi,
I have one doubt regarding CURSOR statement.
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.
Thanks in advance. |
|
| Back to top |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Ravikumar,
Declare statement does not execute.
You should look at your query, EXPLAIN it and see, statistics might have changed, Check & Compare the Elapsed time of DB2 & Application both the times.
Regards,
Sushanth |
|
| Back to top |
|
|
ravikumar15
New User
Joined: 27 Mar 2006
Posts: 68
|
|
|
|
Thanks Sushanth,
But why the DBA are telling that there are 7400 Declare and 6000 Selects?
I bound the package and plan with EXPLAIN. I checked in the Plan_table, but it is not showing anything about the table which is accessed by the plan which I bound.
Please let me know if I am in the right direction.
Thanks,
Ravi |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Where in the program is the DECLARE placed? |
|
| Back to top |
|
|
ravikumar15
New User
Joined: 27 Mar 2006
Posts: 68
|
|
|
|
| The DECLARE is placed in the Working Storage section. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Is this code run multiple times or is it a called module? |
|
| Back to top |
|
|
ravikumar15
New User
Joined: 27 Mar 2006
Posts: 68
|
|
|
|
Hello Dick Scherrer,
This is a main module. The fetching is in a loop and after fetching a record, it does some processing before it fetches the next record.
The main module is not called multiple times.
I am wondering how, there are 7000 DECLAREs. Also the DBA's told that there are over a billion get pages. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Ravi,
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])
| Code: |
SET CURRENT SQLID='plan_table_qualifier';
SELECT
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
WHERE
A.COLLID = 'collection_name' AND
A.PROGNAME IN
(
'xxxxx',
'yyyyy'
) AND
A.BIND_TIME = ( SELECT MAX(BIND_TIME)
FROM PLAN_TABLE
WHERE
COLLID = A.COLLID AND
PROGNAME = A.PROGNAME )
ORDER BY QUERYNO,
QBLOCKNO,
PLANNO
WITH UR;
|
Thanks,
Sushanth |
|
| Back to top |
|
|
|
|
