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
 

 

Cost of an UPDATE query when executed three times

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Cost of an UPDATE query when executed three times
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

Site Director


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

PostPosted: Mon Oct 14, 2013 3:09 am    Post subject:
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    Post subject: Reply to: Cost of an UPDATE query when executed three times
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

Site Director


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

PostPosted: Tue Oct 15, 2013 12:49 am    Post subject:
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    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 Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts Report cost in CA-dispatch Nileshkul CA Products 3 Wed Jun 07, 2017 10:32 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm


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