IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

Query fine tuning/optimization


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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

Moderator Emeritus


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

PostPosted: Mon Sep 09, 2013 8:01 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts COBOL Performance Tuning COBOL Programming 6
No new posts a copybook getting improper values in... COBOL Programming 7
No new posts Mainframe ISPREDIT Macro query TSO/ISPF 3
No new posts DFSORT Output file order query DFSORT/ICETOOL 2
No new posts Workload Manager definitions for Tuni... All Other Mainframe Topics 0

Back to Top