|
View previous topic :: View next topic
|
| Author |
Message |
sudhakarraju
New User
Joined: 05 May 2005
Posts: 31
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Probably not unless you build the query "on the fly".
Many organization do not permit this. . . |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
sudhakarraju
New User
Joined: 05 May 2005
Posts: 31
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Then i hope this is a very small table or that it is referenced this way very seldom. . . |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| dick scherrer wrote: |
| If i understand the request is is not about optimizing the query. |
|
|
| Back to top |
|
|
sudhakarraju
New User
Joined: 05 May 2005
Posts: 31
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
bauer
New User
Joined: 03 Mar 2009
Posts: 28
Location: germany
|
|
|
|
| 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 |
|
|
bhairon singh rathore
New User
Joined: 19 Jun 2008
Posts: 91
Location: banglore
|
|
|
|
| I think best way to do this is thru dynamic query. |
|
| Back to top |
|
|
|
|
