Solved

Execution of Dynamic Query in DB2 SQL procedure.

Posted on 2004-04-30
11
601 Views
Last Modified: 2008-03-03
What would be the optimum way of buiding the dynamic query and executing the same using CURSOR?..
The scenario is need to build the where clause dynamically based on the SP input parameters.
Basically it's an SELECT query and it needs to be iterated through CURSOR....
If any other possibility of iteration without using CURSOR also welcoming one if it boost the performance.

Thanks in advance.

P.Hemanand.
Satyam Computers Ltd.
0
Comment
Question by:phemanand
  • 3
  • 3
  • 2
11 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10969034
can you give an example of the sort of where clause you would wnat to build?


why / what are you attempting to do with the rows returned by the statement...
(ie why do you "think" you need to use a cursor?)

which version / edition / platform of DB2/UDB are you using?

   
0
 

Author Comment

by:phemanand
ID: 10987918
The reason for using cursor is to loop through the results............Because am expecting more than one record.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10999274
ok
yes  if you've got more than one row returned you want  to do something....

what is the something?

produce some totals ?
update another table ?
what is the actual scenario?

(platform , DB2 version)

cursors can perform well, but in many situtations  it can pay to avoid using them...

could a compound statement be utilised instead?
0
 
LVL 18

Expert Comment

by:BigSchmuh
ID: 11004694
If only the WHERE clause is to be modified, you can use boolean expressions as DB2 optimizer is able to start testing your boolean before testing any other clause (including EXISTS one).
Exemple:
SELECT ...
FROM ... JOIN...
WHERE (YourDynamicValueForExpression1Here = TRUE AND Col1 = Col3)
AND (YourDynamicValueForExpression2Here = TRUE AND Col2 IN(Select Col2PrimaryKey FROM Col2RefTable))

Using Bind variables will allow you to dynamically replace YourDynamicValueForExpressionxHere and avoid DB2 to parse again the query (This saves some CPU).

Hope this helps.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:phemanand
ID: 11123960
To  Lowfatspread,

Platform: Linux, Windows 2000
DB2 version 7.1, 8.1 UDB

I have to do some business validation after fetching the data using cursor...and then insert the
same if the data fits in any of the business validation.....

Hope this addressed your question and sorry for the delayed response.

To  BigSchmuh,

Could you please share some sample sql..

Thanks and Regards,
P.Hemanand
0
 
LVL 18

Expert Comment

by:BigSchmuh
ID: 11126581
Can you just explain us one a your business validation for me to show you some meaningfull examples ?
0
 

Author Comment

by:phemanand
ID: 11126773
Here is the logic......

Let us consider input parameters like branch_code and supplier_code(Which are part of the querying table.)

If the input value of supplier_code is ALL denotes that we should not keep the supplier_code field in the where clause which means we need to fetch the data for all suppliers...and the same logic applies to branch_code if the user entered value for branch_code is ALL then the branch_code will not be the part of where clause.

Case 1:
supplier_code : ALL
branch_code : ALL

then the query should be select * from tab1;

Case 2:
supplier_code : 01
branch_code : ALL

then the query should be select * from tab1 where supplier_code = '01';

Case 3:
supplier_code : 'ALL'
branch_code : '01'

then the query should be select * from tab1 where branch_code = '01';

Case 4:
supplier_code : '01'
branch_code : '01'

then the query should be select * from tab1 where supplier_code = '01' and branch_code = '01';

Hope this addressed your question.

Thanks and Regards,
P.Hemanand.
0
 
LVL 18

Accepted Solution

by:
BigSchmuh earned 125 total points
ID: 11133564
An example using 2 bind variables (or just make it a dynamically builded sql) is:
SELECT *
FROM tab1
WHERE ( :YourSupplierCode = 'ALL' OR supplier_code = :YourSupplierCode)
AND ( :YourBranchCode = 'ALL' OR branch_code = :YourBranchCode)
==> You can try it and I trust the db2 optimizer to build a query plan which first test the bind variables against the ALL value and then choose an index on supplier_code or branch_code if they exist...

Hope this helps.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now