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
 

 

Fine tuning UNION ALL query

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

New User


Joined: 12 Nov 2007
Posts: 38
Location: Texas, USA

PostPosted: Thu Jun 25, 2009 9:16 am    Post subject: Fine tuning UNION ALL query
Reply with quote

Hi,
I am getting timeout S322(after 5 minutes of CPU time) on following query in a program. Thru SPUFI I found that result set will have over a million records. Please suggest a solution so that I can run program in lesser time without time abend. Here I am talking about an easytrieve program in which following query is given in JOB statement i.e. data from this query is driving the program. In addition to this there are 3 more simple select queries, fetching 2-3 fields. Please let me know if more information is required.

SELECT
A.CORP_CODE ,
B.IC_ITEM_NO ,
B.IC_ITEM_DESC_1
FROM
XXXBLHD A ,XXXBLLN B
WHERE A.CORP_CODE = B.CORP_CODE
AND A.CUST_AR_OFFICE_CODE = B.CUST_AR_OFFICE_CODE
AND A.SALES_OFFICE = B.SALES_OFFICE
AND A.SALES_ORDR_NO = B.SALES_ORDR_NO
AND A.INVOICE_NO = B.INVOICE_NO
AND A.CUST_TYPE_CODE <> '06'
AND A.WAREHOUSE_NUMBER NOT IN('01','99')
AND A.INVOICE_DATE >= :W-RUN-DATE-2

UNION ALL

SELECT
C.CORP_CODE ,
D.IC_ITEM_NO ,
D.IC_ITEM_DESC_1
FROM
ARCBLHD C ,ARCBLLN D
WHERE C.CORP_CODE = D.CORP_CODE
AND C.CUST_AR_OFFICE_CODE = D.CUST_AR_OFFICE_CODE
AND C.SALES_OFFICE = D.SALES_OFFICE
AND C.SALES_ORDR_NO = D.SALES_ORDR_NO
AND C.INVOICE_NO = D.INVOICE_NO
AND C.CUST_TYPE_CODE <> '06'
AND C.WAREHOUSE_NUMBER NOT IN('01','99')
AND C.INVOICE_DATE >= :W-RUN-DATE-2
ORDER BY
CORP_CODE ,
CUST_AR_OFFICE_CODE ,
WAREHOUSE_NUMBER ,
IC_ITEM_NO ,
SALES_ORDR_NO ,
INVOICE_NO
INTO
:BLD-CORP-CODE ,
:BLN-IC-ITEM-NO ,
:BLN-IC-ITEM-DESC-1


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

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 25, 2009 9:22 am    Post subject:
Reply with quote

Hello,

Suggest you redesign the process so that a million rows is not returned for a single query. . .

Just because something like this can be written and executed, does not mean it should be written and run.
Back to top
View user's profile Send private message
Satish5

New User


Joined: 12 Nov 2007
Posts: 38
Location: Texas, USA

PostPosted: Thu Jun 25, 2009 9:52 am    Post subject: RE:Fine tuning UNION ALL query
Reply with quote

I thought about that redesigning.
Break the whole task into 4 steps

1. Run query on XXXBLHD and XXXBLLN and write the result data into a tempvsam.
2. Run query on ARCBLHD and ARCBLLNand APPEND the result data to tempvsam created in previous step.
3. sort tempvsam on required fields.
4. read tempvsam into program and do the processing and prinht report

Here I have a doubt. Tempvsam will contain over one million records and reading that sequentailly might again result into time abend.

Please share your comments on this approach and if you have a better approach then please do share that.

Thanks,
Satish
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 25, 2009 9:58 pm    Post subject:
Reply with quote

Hello,

The creation and a million inserts into a vsam file would add undesired overhead - not counting the actual read later.

I'd experiment with writing temp qsam files in step 1 & 2. Then, i'd sort them together by the "key". Then, i'd read the sorted temo qsam file do whatever processing/reporting is required.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jun 26, 2009 10:22 pm    Post subject:
Reply with quote

As a turnaround solution ...

your conditions

AND C.CUST_TYPE_CODE <> '06'
AND C.WAREHOUSE_NUMBER NOT IN('01','99')
AND C.INVOICE_DATE >= :W-RUN-DATE-2

is common in both select queries ... remove this and put this outside and inline view ... the view should have only the select queries with join ... this suggestion is because of non -equal conditions which normally does not help in a proper index scan ( assuming one of these cols are part of your index )
wht is the order by doing in your second select ?
Back to top
View user's profile Send private message
Satish5

New User


Joined: 12 Nov 2007
Posts: 38
Location: Texas, USA

PostPosted: Wed Jul 01, 2009 9:10 am    Post subject: RE:Fine tuning UNION ALL query
Reply with quote

The main problem was that a table was accessed on partial key using a cursor and that table was an archive table contaning approx. 15 Million recs. I wrote a batch SPUFI and extracted records on some conditions from that archive table into a tempvsam(less than 1 million recs qualified for result set) and then read that tempvsam in program. This reduced the time significantly and job executed in just over a minute of CPU time.

Thanks Dick Scherrer and Ashimer for your help.

Regards,
Satish
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jul 01, 2009 9:31 am    Post subject: Reply to: Fine tuning UNION ALL query
Reply with quote

You're welcome - thank you for posting how you modified the process to improve the performance icon_smile.gif

d
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
No new posts Batch job tuning sgandhla Testing & Performance analysis 5 Fri Mar 24, 2017 9:41 pm
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