[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 618
  • Last Modified:

Execution of Dynamic Query in DB2 SQL procedure.

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
phemanand
Asked:
phemanand
  • 3
  • 3
  • 2
1 Solution
 
LowfatspreadCommented:
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
 
phemanandAuthor Commented:
The reason for using cursor is to loop through the results............Because am expecting more than one record.
0
 
LowfatspreadCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now