Solved

Execution of Dynamic Query in DB2 SQL procedure.

Posted on 2004-04-30
11
602 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

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…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

813 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

18 Experts available now in Live!

Get 1:1 Help Now