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
 

 

SQL for Multi Index DB2 Table

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

New User


Joined: 05 May 2005
Posts: 26

PostPosted: Fri Oct 08, 2010 1:34 am    Post subject: SQL for Multi Index DB2 Table
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

Site Director


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

PostPosted: Fri Oct 08, 2010 2:26 am    Post subject:
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: 1278
Location: Belgium

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

http://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

Site Director


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

PostPosted: Fri Oct 08, 2010 7:32 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Fri Oct 08, 2010 7:58 pm    Post subject:
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: 26

PostPosted: Fri Oct 08, 2010 8:03 pm    Post subject:
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

Site Director


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

PostPosted: Fri Oct 08, 2010 9:08 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Mon Oct 11, 2010 12:32 pm    Post subject:
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: 26

PostPosted: Mon Oct 11, 2010 6:18 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Mon Oct 11, 2010 6:27 pm    Post subject:
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: 26
Location: germany

PostPosted: Wed Oct 13, 2010 11:38 am    Post subject:
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    Post subject: Reply to: SQL for Multi Index DB2 Table
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    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 Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts Reading selected volumes of a multi-v... RickBig JCL & VSAM 6 Wed Jul 13, 2016 7:26 pm


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