IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Cost of an UPDATE query when executed three times


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sasanka
Warnings : 1

New User


Joined: 18 Jan 2008
Posts: 34
Location: India

PostPosted: Sun Oct 13, 2013 12:06 am
Reply with quote

Hi,

We have a COBOL-DB2 program, where below update query executed three times with different value of WS-SEC-TYPE.

From the package information after bind this COBOL-DB2 program, we get Cost*Rate of the update query is 4.5. So after executing this query three times when the program runs, what will be the total Cost*Rate of this update query?

Is this Cost*Rate = 4.5 * 3 = 13.5 ?

Similarly, if the query executed for 10 times, then will the Cost*Rate = 4.5 * 10 = 45.

Query
-------
UPDATE EMPLOYEE_TAB
SET STATUS_CODE = 'C'
WHERE SEC_NO = :WS-SEC-NO
AND SEC_CITY = :WS-SEC-TYPE

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

Moderator Emeritus


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

PostPosted: Mon Oct 14, 2013 3:09 am
Reply with quote

Hello,

If the cost for the 3 different values is about the same, why would the total cost not be consistent?

I don't really understand why there is a concern.
Back to top
View user's profile Send private message
sasanka
Warnings : 1

New User


Joined: 18 Jan 2008
Posts: 34
Location: India

PostPosted: Mon Oct 14, 2013 5:01 pm
Reply with quote

Hi,

In table V1CXASST,
(i)cluster and non- unique index is build on combination of three columns ML_SC_NO, MLC_SC_FD_DST_CD and MLC_FD_STA_CD

(ii)non-cluster and non- unique index is build on combination of two columns MLC_SC_FD_DST_CD and MLC_FD_STA_CD

(a)In our cobol db2 program PMGSST, below UPDATE query in Para 2100-UPDATE-SSTTAB executed 3 times as below:
2000-PROCESS-FEED.

MOVE 'SMFFEED' TO SST-MLC-SC-FD-DST-CD
PERFORM 2100-UPDATE-SSTTAB THRU 2100-EXIT

MOVE 'FCTFEED' TO SST-MLC-SC-FD-DST-CD
PERFORM 2100-UPDATE-SSTTAB THRU 2100-EXIT

MOVE 'DACFEED' TO SST-MLC-SC-FD-DST-CD
PERFORM 2100-UPDATE-SSTTAB THRU 2100-EXIT.

2000-EXIT.
EXIT.

2100-UPDATE-SSTTAB.

EXEC SQL
UPDATE V1CXASST
SET MLC_FD_STA_CD = :IN-STATUS-TO
,ROW_LUP_PGM_NM = :WS-PROG-NAME
,ROW_LUP_TS = CURRENT TIMESTAMP
WHERE MLC_SC_FD_DST_CD = :SST-MLC-SC-FD-DST-CD
AND ML_SC_NO = :SST-ML-SC-NO
AND MLC_FD_STA_CD = :SST-MLC-FD-STA-CD
END-EXEC.

2100-EXIT.
EXIT.

When we checked the COST*RATE of the above UPDATE query from the EXPLAIN results for Package (in CATALOG MANAGER), we got as below:
COST*RATE = 4.038248

COST*RATE QB PL MIX QTYPE METH ACC MTCH IX TBNAME
4.038248 1 1 0 UPDATE 0 I 3 Y TBCXASST
IXNAME
X1CXASST

(b) Now in program PGMSST, we made below changes in UPDATE query so that instead of executing the UPDATE query 3 times, it executes only once.

2000-PROCESS-FEED.

MOVE 'SMFFEED' TO WS-SC-FD-DST-CD-SMF
MOVE 'FCTFEED' TO WS-SC-FD-DST-CD-FCT
MOVE 'DACFEED' TO WS-SC-FD-DST-CD-DAC

PERFORM 2100-UPDATE-SSTTAB THRU 2100-EXIT.

2000-EXIT.
EXIT.

2100-UPDATE-SSTTAB.
EXEC SQL
UPDATE V1CXASST
SET MLC_FD_STA_CD = :IN-STATUS-TO
,ROW_LUP_PGM_NM =:WS-PROG-NAME
,ROW_LUP_TS = CURRENT TIMESTAMP
WHERE MLC_SC_FD_DST_CD IN ( : WS-SC-FD-DST-CD-SMF,
:WS-SC-FD-DST-CD-FCT,
: WS-SC-FD-DST-CD-DAC)
AND ML_SC_NO = :SST-ML-SC-NO
AND MLC_FD_STA_CD = :SST-MLC-FD-STA-CD
END-EXEC.

2100-EXIT.
EXIT.

When we checked the COST*RATE of the above UPDATE query from the EXPLAIN results for Package, we got as below:
COST*RATE = 4.720154

COST*RATE QB PL MIX QTYPE METH ACC MTCH IX TBNAME
4.720154 1 1 0 UPDATE 0 N 3 Y TBCXASST
IXNAME
X1CXASST

Please let us know which UPDATE query (query in (a) or query in (b)) is better as performance wise and as cost wise when we run program PGMSST.

Please suggest which UPDATE query do we need to implement in program PGMSST to get better performance.

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

Moderator Emeritus


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

PostPosted: Tue Oct 15, 2013 12:49 am
Reply with quote

Hello,

If you have not done so already, run an explain on each approach you are considering and look at the info provided.

Make sure the queries run on data that is as close to production as possible.

If you have any doubt about the result of the explains, post your doubt here and maybe someone will have a suggestion.
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

 


Similar Topics
Topic Forum Replies
No new posts RACF - Rebuild SETROPTS command which... All Other Mainframe Topics 3
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts RACF cost vs. ACF2 cost IBM Tools 2
No new posts Query on edit primary command CLIST & REXX 5
Search our Forums:

Back to Top