Solved

Execution of Dynamic Query in DB2 SQL procedure.

Posted on 2004-04-30
11
603 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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

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.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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