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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am


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