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
 

 

Is there a better way to use cursor in my case? Use CASE?

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

New User


Joined: 03 Jul 2006
Posts: 53

PostPosted: Tue Feb 23, 2010 3:19 am    Post subject: Is there a better way to use cursor in my case? Use CASE?
Reply with quote

Basically what I need to do is open cursor to select specific rows from table in DB2 for report printing. My selection fields will come from control file (up to 5 records) where values will be stored.

Let's say control file has the following records which I will read and populate cursor values:

Code:
FIELD1  FIELD2     FIELD3   
---------------------------
1111    1111AAAA   1111BBBB
2222    2222AAAA   2222BBBB
3333    3333AAAA           


Now cursor will have all variables set as blanks and then in program I will populate it while reading file.

Code:
SELECT *                               
  FROM TESTTBL.TESTDB2                 
    WHERE (COL_ITEM_1 = :HOST_V1_1      AND     
           COL_ITEM_2 = :HOST_V1_2      AND
           COL_ITEM_3 = :HOST_V1_33 )   
                                       
    OR    (COL_ITEM_1 = :HOST_V2_1      AND     
           COL_ITEM_2 = :HOST_V2_2      AND
           COL_ITEM_3 = :HOST_V2_3 )   
                                       
    OR    (COL_ITEM_1 = :HOST_V3_1      AND     
           COL_ITEM_2 = :HOST_V3_2      AND
           COL_ITEM_3 = :HOST_V3_3 )   
                                       
    OR    (COL_ITEM_1 = :HOST_V4_1      AND     
           COL_ITEM_2 = :HOST_V4_2      AND
           COL_ITEM_3 = :HOST_V4_3 )   
                                       
    OR    (COL_ITEM_1 = :HOST_V4_1      AND     
           COL_ITEM_2 = :HOST_V4_2      AND
           COL_ITEM_3 = :HOST_V4_3 )   ;
                                       
                                       
                                       



Cursor after I finished reading control file and all host variables are populated:
Code:
SELECT *                               
  FROM TESTTBL.TESTDB2                 
    WHERE (COL_ITEM_1 = '1111'      AND     
           COL_ITEM_2 = '1111AAAA'  AND
           COL_ITEM_3 = '1111BBBB' )   
                                       
    OR    (COL_ITEM_1 = '2222'      AND     
           COL_ITEM_2 = '2222AAAA'  AND
           COL_ITEM_3 = '2222BBBB' )   
                                       
    OR    (COL_ITEM_1 = '3333'      AND     
           COL_ITEM_2 = '3333AAAA'  AND
           COL_ITEM_3 = '        ' )   
                                       
    OR    (COL_ITEM_1 = '    '      AND     
           COL_ITEM_2 = '        '  AND
           COL_ITEM_3 = '        ' )   
                                       
    OR    (COL_ITEM_1 = '    '      AND     
           COL_ITEM_2 = '        '  AND
           COL_ITEM_3 = '        ' )   ;
                                       
                                       


Is there more efficient way to do that?

Thanks. icon_smile.gif
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: Tue Feb 23, 2010 3:45 am    Post subject:
Reply with quote

Hello,

It would be wise to make sure that the 3 col_item columns are a key. . .
Back to top
View user's profile Send private message
rmd3003

New User


Joined: 03 Jul 2006
Posts: 53

PostPosted: Tue Feb 23, 2010 3:53 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

It would be wise to make sure that the 3 col_item columns are a key. . .


Yes, they are part of Unique Clustering index. But there are 4 more columns in this key.
So you think this is most efficient way to select all rows needed in my case? Right?
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: Tue Feb 23, 2010 4:03 am    Post subject:
Reply with quote

Hello,

Quote:
So you think this is most efficient way to select all rows needed in my case? Right?
With the very little info provided, "most efficient" is not an issue.
Quote:
But there are 4 more columns in this key.
As long as these 3 are the high-order columns in the key it should be ok. . .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Feb 23, 2010 9:57 pm    Post subject:
Reply with quote

dick scherrer wrote:
As long as these 3 are the high-order columns in the key it should be ok. . .

doubtfull.
I would check explain info if you really are using the index matchcols 3.

otherwise you could do one of the following
- write union ALL , each with one set of hostvariables
- use a CTE containing the 4 criteria sets
- open your cursor 4 times
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: Wed Feb 24, 2010 12:46 am    Post subject: Reply to: Is there a better way to use cursor in my case? Us
Reply with quote

Good catch. . . My bad icon_redface.gif

I keep forgetting that the db2 optimizer does not provide several things i'm used to. . . Maybe some day. . . icon_smile.gif

d
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 RFE: DB2 support for mixed case names. Pedro DB2 0 Tue Jul 04, 2017 1:32 am
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm


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