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
 

 

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 Batch job tuning sgandhla Testing & Performance analysis 0 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