Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Query fine tuning/optimization

 
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: Mon Sep 09, 2013 12:38 pm    Post subject: Query fine tuning/optimization
Reply with quote

Hi,
I am struggling with tuning/optimization of following query
INSERT INTO TABLE1 T1 (FEED_ID,BATCH_NUM,RECORD_TYPE,STAGE,SCREEN,DDRECORD_NUM,TRAN_TYPE)
(
SELECT DISTINCT TMP.FEED_ID,TMP.BATCH_NUM,'DD','CBVAL',v_screen_id,TMP.DDRECORD_NUM,TMP.TRAN_TYPE
FROM TABLE2 TMP
WHERE TMP.STATUS IN ('U','D')
AND NOT EXISTS (
SELECT 1
FROM TABLE3 STMT_CORR,
TABLE4 STMT_DED
WHERE STMT_CORR.STMT_MASTER_ID = STMT_DED.STMT_MASTER_ID
AND STMT_CORR.LATEST_CORRECTION_FLG = 'Y'
AND STMT_CORR.PROCESSING_STATUS IN ('STMTAPP','INACTIVE','CSTMTAPP','DMNDCLS')
AND TMP.ORIG_RRR_NUM = STMT_CORR.ORIG_RRR_NUM
AND STMT_CORR.RETN_FINYEAR = v_year
AND STMT_CORR.RETN_PERIODICITY = v_period
AND STMT_DED.RETN_FINYEAR = v_year
AND STMT_DED.RETN_PERIODICITY = v_period
AND TMP.DDRECORD_NUM = STMT_DED.DDRECORD_NUM
AND STMT_DED.CHNG_ACTION <> 'D'
)
);

Please suggest if you can figure out a way to make it run faster. Would it help if we convert into JOIN?

Thanks & 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: Mon Sep 09, 2013 8:01 pm    Post subject:
Reply with quote

Hello,

How much data is being read/inserted?

How long does this take now?
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 query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm
No new posts Query on XMITIP abdulrafi All Other Mainframe Topics 1 Wed Oct 25, 2017 6:54 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us