Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Information regarding CURSOR statement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ravikumar15

New User


Joined: 27 Mar 2006
Posts: 64

PostPosted: Tue Jun 25, 2013 3:52 am    Post subject: Information regarding CURSOR statement
Reply with quote

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
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Jun 25, 2013 11:41 am    Post subject:
Reply with quote

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
View user's profile Send private message
ravikumar15

New User


Joined: 27 Mar 2006
Posts: 64

PostPosted: Wed Jun 26, 2013 11:04 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 27, 2013 12:58 am    Post subject:
Reply with quote

Hello,

Where in the program is the DECLARE placed?
Back to top
View user's profile Send private message
ravikumar15

New User


Joined: 27 Mar 2006
Posts: 64

PostPosted: Thu Jun 27, 2013 1:00 am    Post subject:
Reply with quote

The DECLARE is placed in the Working Storage section.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 27, 2013 1:02 am    Post subject:
Reply with quote

Hello,

Is this code run multiple times or is it a called module?
Back to top
View user's profile Send private message
ravikumar15

New User


Joined: 27 Mar 2006
Posts: 64

PostPosted: Thu Jun 27, 2013 2:02 am    Post subject:
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Jun 27, 2013 11:16 am    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Obtaining file information pahiker COBOL Programming 25 Tue Sep 27, 2016 7:25 pm
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us