Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Selective execution of a queries

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Delip

New User


Joined: 13 Sep 2007
Posts: 45
Location: Bangalore

PostPosted: Fri Jan 16, 2015 3:51 pm    Post subject: Selective execution of a queries
Reply with quote

Hi,

I have a situation where the execution of query needs to be done based on an input. I can have a town or postcode as input. Only one would come as input. Column to be fetched is same but the tables are different. Currently I am using a union all like below

select c1 from t1 where town=:input post town and indicator = 1
union all
Select c1 from t2 where postcode= :input postcode and indicator=2

but the issue is the job is going to go to both the tables and causing performance issues.

Is it possible in a query itself as
if indicator = 1 then fetch from t1
else fetch from t2

(it is not possible to do in cobol as the input is not limited to only two inputs. there are nearly 10 inputs,so it is very difficult to do programatically).

Appreciate the help
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Jan 16, 2015 8:06 pm    Post subject:
Reply with quote

Hi Delip,

Quote:
(it is not possible to do in cobol as the input is not limited to only two inputs. there are nearly 10 inputs,so it is very difficult to do programatically).


What do you mean by 10 inputs?

and
Quote:
select c1 from t1 where town=:input post town and indicator = 1
union all
Select c1 from t2 where postcode= :input postcode and indicator=2


How are you executing the query the query currently and how do you differentiate in providing what is the input?
Back to top
View user's profile Send private message
Delip

New User


Joined: 13 Sep 2007
Posts: 45
Location: Bangalore

PostPosted: Fri Jan 16, 2015 8:18 pm    Post subject:
Reply with quote

Hi Pandora,
Apologies for the ambiguous statements. Currently I am executing the query through a cursor. Based on the input, I am setting a flag. If it is town, I am passing 1 into the indicator. If it post code, indicator is 2. Like this I am passing various values to the Indicator(this is a working storage variable but not a column of any table).

As the number of inputs are more, I do not want to handle those many cursors so I put every thing in one cursor . But this is causing performance issues.
Hope I am clear now.Let me know if you still need details.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Jan 16, 2015 8:47 pm    Post subject:
Reply with quote

What are the values present in the input file?

Is it not possible to read the file record by record and process?
Back to top
View user's profile Send private message
Delip

New User


Joined: 13 Sep 2007
Posts: 45
Location: Bangalore

PostPosted: Fri Jan 16, 2015 8:50 pm    Post subject:
Reply with quote

we are processing record by record. every record has one of the inputs (like town/post code/telephone etc).for every record we open the cursor and do the processing
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Jan 16, 2015 9:04 pm    Post subject:
Reply with quote

ok and you feel that process to be not efficient?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Jan 16, 2015 9:25 pm    Post subject:
Reply with quote

Why do you use union because if you get "town" then rest all the Select in that union is useless? Why don't you write each 10 separate queries based on what input you get from the file for that perticular record and route them accordingly? How many rows are present in t1 and t2 tables? Were there proper indexes? because your performance issue might have many reason but not just the approach as you think.

Quote:
As the number of inputs are more, I do not want to handle those many cursors so I put every thing in one cursor

Why so and how many possible cursors you are thinking of here ?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Jan 19, 2015 7:56 pm    Post subject:
Reply with quote

sounds as if dynamic sql is needed here.
Back to top
View user's profile Send private message
Delip

New User


Joined: 13 Sep 2007
Posts: 45
Location: Bangalore

PostPosted: Mon Jan 19, 2015 10:08 pm    Post subject: Reply to: Selective execution of a queries
Reply with quote

is there any other way of doing instead of a dynamic query? how do we build a dynamic query?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Jan 21, 2015 12:24 am    Post subject:
Reply with quote

Did u not see my reply and helped you or it is just that you don't want to spend time coding multiple routines and want to wrap it up in one SQL?

Dynamic SQL e.x.-

http://www-01.ibm.com/support/knowledgecenter/#!/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_samplecoboldynamicsql.dita
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. JCL MULTIPLE STEP EXECUTION QUERY Susanta JCL & VSAM 18 Sat Jul 30, 2016 1:17 pm
No new posts output DDname JESMSGLG job during exe... italo_pm TSO/ISPF 16 Tue Jun 21, 2016 11:13 am
No new posts Can a DB2 insert trigger execution of... TS70363 DB2 6 Tue Nov 24, 2015 10:44 pm
No new posts Need to do selective update in JCL manuwankenobi DFSORT/ICETOOL 1 Mon Sep 07, 2015 4:23 pm
No new posts DFSORT - Retrieve selective records f... narasimha_devi DFSORT/ICETOOL 8 Thu Aug 06, 2015 4:12 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us