Help needed in a query

apandey · New User Joined: 31 Aug 2009 Posts: 73 Location: Mumbai

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.

sushanth bobby · Posted: Fri Jun 24, 2011 6:27 pm

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

GuyC · Posted: Fri Jun 24, 2011 6:47 pm

apandey · New User Joined: 31 Aug 2009 Posts: 73 Location: Mumbai

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.

GuyC · Posted: Tue Jun 28, 2011 12:40 pm

thanks for the feedback.

allthough :