IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL for Multi Index DB2 Table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sudhakarraju

New User


Joined: 05 May 2005
Posts: 31

PostPosted: Fri Oct 08, 2010 1:34 am
Reply with quote

I need to write a callable program that will read a DB2 table. The DB2 table has 5 columns as indexes. The calling program can pass either 1 to 5 column values.

Is there a simple way to code SQL for this rather than writing query for every column combination?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Fri Oct 08, 2010 2:26 am
Reply with quote

Hello,

Probably not unless you build the query "on the fly".

Many organization do not permit this. . .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Oct 08, 2010 3:22 pm
Reply with quote

ibmmainframes.com/viewtopic.php?t=49225

but one query can have only 1 accesspath, so only one combination will be optimal
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Fri Oct 08, 2010 7:32 pm
Reply with quote

Hi Guy,

If i understand the request is is not about optimizing the query. It is how to have a completely different "where" built depending on what the user specifies.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Oct 08, 2010 7:58 pm
Reply with quote

well, the thread mentioned describes just that :
How to write 1 static SQLstmt to handle the combination of 5 filled/not filled in criteria.
Back to top
View user's profile Send private message
sudhakarraju

New User


Joined: 05 May 2005
Posts: 31

PostPosted: Fri Oct 08, 2010 8:03 pm
Reply with quote

You are right, not many shops allow to use Dynamic SQL.

How ever I got the solution from one of my co-worker on how to build the query that has multiple indexes


SELECT * FROM DB2.TABLE WHERE
KEY1 BETWEEN :WS-KEY1-L AND :WS-KEY1-H AND
KEY2 BETWEEN :WS-KEY2-L AND :WS-KEY2-H AND
KEY3 BETWEEN :WS-KEY3-L AND :WS-KEY3-H AND
KEY4 BETWEEN :WS-KEY4-L AND :WS-KEY4-H AND
KEY5 BETWEEN :WS-KEY5-L AND :WS-KEY5-H

if the index value passed is blank, then populate the corresponding -L with LOW-VALUES and -H with HIGH-VALUES.

If it's non blank, then pass that value in both -L & -H.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Fri Oct 08, 2010 9:08 pm
Reply with quote

Hello,

Then i hope this is a very small table or that it is referenced this way very seldom. . .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Oct 11, 2010 12:32 pm
Reply with quote

dick scherrer wrote:
If i understand the request is is not about optimizing the query.
Back to top
View user's profile Send private message
sudhakarraju

New User


Joined: 05 May 2005
Posts: 31

PostPosted: Mon Oct 11, 2010 6:18 pm
Reply with quote

The TABLE will have around 500000 rows.
There should not be a problem with the query as all the fields in the where clause are Indexes.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Oct 11, 2010 6:27 pm
Reply with quote

sudhakarraju wrote:
The TABLE will have around 500000 rows.
There should not be a problem with the query as all the fields in the where clause are Indexes.

well , then you misunderstand how db2 static sql works.

unless otherwise stated, the accesspath used (=~the index used) is decided at bind time. thus :
suppose 5 columns , 5 indexes, 5 betweens in the where clause.
then DB2 will choose 1 of the 5 indexes. (lets say ix3)
Now when criteria 3 is not filled in, ie. between low-values and high-values and criteria 1 is filled in , DB2 will NOT suddenly switch over to ix1. It will still use ix3.
Back to top
View user's profile Send private message
bauer

New User


Joined: 03 Mar 2009
Posts: 28
Location: germany

PostPosted: Wed Oct 13, 2010 11:38 am
Reply with quote

You can use Bind option REOPT, this helps to reoptimize the accesspath at runtime depending on the content of your host variables.
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Fri Oct 15, 2010 6:41 pm
Reply with quote

I think best way to do this is thru dynamic query.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Read a flat file and update DB2 table JCL & VSAM 2
No new posts DL/I status code AK for GU call using... IMS DB/DC 1
No new posts Search substring in internal table of... COBOL Programming 2
This topic is locked: you cannot edit posts or make replies. Write from flat file to db2 table Mainframe Interview Questions 5
No new posts Table Information - DB2 DB2 1
Search our Forums:

Back to Top