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
 

 

Help needed in a query

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

New User


Joined: 31 Aug 2009
Posts: 73
Location: Mumbai

PostPosted: Fri Jun 24, 2011 6:01 pm    Post subject: Help needed in a query
Reply with quote

Hi All,

I have a query,

SELECT SUM(Quantity)
INTO :WS-QUANTITY
FROM TABLE1
WHERE Code = '01'
and KEY_FIELD = :WS-KEY

Exactly after this query, I have 1 more similar query

SELECT SUM(Quantity)
INTO :WS-QUANTITY1
FROM TABLE1
WHERE Code = '02'
and KEY_FIELD = :WS-KEY

After this I am Calculating difference between 2 quantities and writing to o/p file.

Now this whole process is done after reading every record of file.
Input file passes WS-KEY field to table. But Since i am executing 2 queries
on a single read. My program's execution time is increased.
Is there any other way to reduce processing or executing query only once
on every read of file so that CPU time can be reduced.
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Jun 24, 2011 6:27 pm    Post subject:
Reply with quote

Ashutosh,

How many rows are there in the table ?
How many distinct values does the CODE and KEY_FIELD have ?
And in the input file how many records you have and how often does you job run ?
And how often is this table is updated ?

And finally what is your DB2 Version ?

Thanks,
Sushanth
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jun 24, 2011 6:47 pm    Post subject:
Reply with quote

Code:
SELECT
sum( case when code = '01' then Quantity else Quantity * -1 end )
INTO :WS-QUANTITY
FROM TABLE1
WHERE Code in ('01' ,'02')
and KEY_FIELD = :WS-KEY


Code:
SELECT
sum( case when code = '01' then Quantity else 0 end )  as sumlode01
,sum( case when code = '02' then Quantity else 0 end )  as sumcode02

INTO :WS-QUANTITY01 , ws-Quantity02
FROM TABLE1
WHERE Code in ('01' ,'02')
and KEY_FIELD = :WS-KEY
Back to top
View user's profile Send private message
apandey

New User


Joined: 31 Aug 2009
Posts: 73
Location: Mumbai

PostPosted: Tue Jun 28, 2011 12:11 pm    Post subject: Reply to: Help needed in a query
Reply with quote

Thanks a lot GuyC. It actually worked.
CPU time now got reduced from 23 min. to 6 min.
Thanks again. Keep up the good work. icon_biggrin.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Jun 28, 2011 12:40 pm    Post subject: Re: Reply to: Help needed in a query
Reply with quote

thanks for the feedback.

allthough :
apandey wrote:
It actually worked.
sounds like you are surprised icon_confused.gif
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. SORT trick needed bshkris SYNCSORT 6 Tue May 02, 2017 4:35 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


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