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
 

 

Performance issue for OPEN cursor

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

New User


Joined: 28 Aug 2006
Posts: 50
Location: London

PostPosted: Tue Jun 09, 2009 8:05 pm    Post subject: Performance issue for OPEN cursor
Reply with quote

Hello All,

I have a Cursor Defined with the Below Query
Code:

SELECT     A.COL1,                                                     
           A.COL2,                                           
           MAX(A.COL3)                                         
             FROM  VIEW1 A,                                 
                   VIEW2 B                               
             WHERE A.COL1 = B.COL1                    AND               
                   A.COL2 = B.COL2                    AND               
                   A.COL3 IN ('AAA','BBB','ZZZ') AND             
                   B.Col4 = :WS-VAR1                   AND               
                   B.col5 = :WS-VAR2                                         
             GROUP BY A.COLUMN1, A.COLUMN2     
             ORDER BY A.COLUMN1 ASC , A.COLUMN2 DESC;                     



Code:

VIew1
------
Col1 Col2 Col3
10   4    AAA
10   4    BBB
10   5    ZZZ
11   1    ZZZ
12   6    AAA
12   6    BBB
13   3    AAA

VIEW2
-----
Col1  COl2  Col4  Col5
10    4     3     5
11    1     2     3
12    6     3     5
13    3     3     5


Considering the values of ws-Var1 = 3 and ws-Var2=5
Following is the Output of the SQL
Code:

A.Col1   A.Col2  A.COl3
-------  ------  ------
10        4       BBB
12        6       BBB
13        3       AAA


Problem:CPU time for Opening the Cursor is much Higher(due to the MAx function and
GROUP/ORDER clause) and it has an impact on the performance.

CAn anyone suggest any modification to the SQL query in such a way that
the MAX function and GROUP/ORDER can be avoided,but give the Same Above result.

Thanks,
Sree
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Jun 09, 2009 8:24 pm    Post subject:
Reply with quote

Are VIEW1 and VIEW2 views or tables. Applying your query against actual tables would most likely be faster then using views.
Back to top
View user's profile Send private message
sreekusr
Warnings : 1

New User


Joined: 28 Aug 2006
Posts: 50
Location: London

PostPosted: Tue Jun 09, 2009 8:27 pm    Post subject:
Reply with quote

HI Carg,

I am restricted to use Views instead of actual Tables.
VIEW1 and VIEW2 are Views.

Thanks,
Sree
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jun 10, 2009 7:02 pm    Post subject:
Reply with quote

views usually perform pretty well stand-alone and they can insulate the developer from requiring any knowledge about the table. But when multiple views are joined , the access path can be less than optimum.

Basically, you will need to reference the base table directly along with code similar to the logic in the view for a better performance ...

Alternatively you can try this query

Code:


SELECT A.COL1, A.COL2, A.COL3 FROM
(
SELECT COL1,COL2,MAX(COL3) AS COL3
FROM VIEW1
WHERE COL3 IN ('AAA','BBB','ZZZ')
GROUP BY COL1, COL2
) AS A
,
(
SELECT COL1, COL2
FROM VIEW2
WHERE B.COL4 = :WS-VAR1
AND B.COL5 = :WS-VAR2
) AS B

WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
ORDER BY A.COL1 ASC, A.COL2 DESC ;


Be sure to run EXPLAIN on both queries as I am not aware of the table indexes ...
Back to top
View user's profile Send private message
bauer

New User


Joined: 03 Mar 2009
Posts: 26
Location: germany

PostPosted: Mon Jun 15, 2009 3:10 pm    Post subject:
Reply with quote

Use Explain and check if runstats has executed.

which indices are available ?
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 Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm
No new posts BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts Execessive parameter issue Sumeendar JCL & VSAM 5 Mon Dec 19, 2016 4:35 pm
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am


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