|
View previous topic :: View next topic
|
| Author |
Message |
rmd3003
New User
Joined: 03 Jul 2006
Posts: 55
|
|
|
|
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.  |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
It would be wise to make sure that the 3 col_item columns are a key. . . |
|
| Back to top |
|
|
rmd3003
New User
Joined: 03 Jul 2006
Posts: 55
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Good catch. . . My bad 
I keep forgetting that the db2 optimizer does not provide several things i'm used to. . . Maybe some day. . .
d |
|
| Back to top |
|
|
|
|
