Solved

Execution of Dynamic Query in DB2 SQL procedure.

Posted on 2004-04-30
11
600 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:phemanand
Comment Utility
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
Comment Utility
Can you just explain us one a your business validation for me to show you some meaningfull examples ?
0
 

Author Comment

by:phemanand
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 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

15 Experts available now in Live!

Get 1:1 Help Now