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
 

 

Removing array loading between fetch & update takes more

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Wed Nov 16, 2011 7:31 pm    Post subject: Removing array loading between fetch & update takes more
Reply with quote

My program fetch rows from 2 tables using below cursor & updates one of the the above tables from which the rows are fetched.

Cursor:
DECLARE SLS_DATE_CSR CURSOR WITH HOLD FOR
SELECT DISTINCT MANIFEST_NBR , MNFST_ACK_TMSTMP
FROM TABLE1 , TABLE2
WHERE MNFST_NBR = CAST ( MANIFEST_NBR AS INTEGER ) AND PO_STATUS_FLG =
4 AND DATE ( MNFST_ACK_TMSTMP ) > '01/01/1900'

Update query:
UPDATE TABLE1
SET
SALESFLOOR_DATE = :WS-SALESFLOOR-DATE,
PO_STATUS_FLG = 5,
LAST_UPD_DATE = CURRENT TIMESTAMP
WHERE
MANIFEST_NBR = :WS-MANIFEST-NBR

Existing Process: After fetch data is loaded into an array & then after the fetch & array loading is over, it updates table TABLE1.

New process:
Rows are fetched & table is updated one by one without the involvement of the array.
Concern:
Even if the array handling is removed, new process consumes more time.

Please help
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Nov 16, 2011 8:23 pm    Post subject:
Reply with quote

a possible answer to you question is: are you performing any commits?
when you use the COBOL Internal Table to store the results of your cursor
(you would use an Array to multi-row fetch)
you close the cursor, which essentially functions as a commit
and then you do singleton updates.

without storing values in a COBOL Internal Table,
your unit-of-work is ever increasing - fetches from cursor and updates.

the FETCH's issue low/level locks that must be dealt with if you do not commit occaisionally.

If this is a CICS program, then more power to you performing Batch activities online.

ideas how things may go faster:
why are you selecting MNFST_ACK_TMSTMP? you are not using it.

i would change this:
DATE ( MNFST_ACK_TMSTMP ) > '01/01/1900'
to this:
MNFST_ACK_TMSTMP > timestamp('1900-01-01.00.00.00.000000')
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Thu Nov 17, 2011 12:51 pm    Post subject:
Reply with quote

1. SALESFLOOR_DATE is a timestamp field which is loaded MNFST_ACK_TMSTMP from TABLE2. So this is required.

2. Also, I tried changing the below but in visual explain the query cost is increasing.
DATE ( MNFST_ACK_TMSTMP ) > '01/01/1900'
to this:
MNFST_ACK_TMSTMP > timestamp('1900-01-01.00.00.00.000000')

3. Also, I changed the cursor query to have WITH UR to avoid locking issue, even then I could observe no change in the performance.

I want to tune this program so removed the array logic. Is there any way, i can improve performance without the array logic? or, the array logic is the most efficient?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Nov 17, 2011 3:01 pm    Post subject:
Reply with quote

Code:
DECLARE SLS_DATE_CSR CURSOR WITH HOLD FOR
SELECT DISTINCT MANIFEST_NBR , MNFST_ACK_TMSTMP
FROM TABLE1 , TABLE2
WHERE MNFST_NBR = CAST ( MANIFEST_NBR AS INTEGER )
   AND PO_STATUS_FLG = 4
   AND DATE ( MNFST_ACK_TMSTMP ) > '01/01/1900'

Or this query is copied to this topic wrongly
Or this query is somehow missing a join condition
probably generates a cartesian product which is then "cleaned" by the DISTINCT.
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Thu Nov 17, 2011 4:05 pm    Post subject:
Reply with quote

No,

The table correlations are missing in the query because all the columns having different name in the tables. Below is the query with correlation.

DECLARE SLS_DATE_CSR CURSOR WITH HOLD FOR
SELECT DISTINCT T1.MANIFEST_NBR , T2.MNFST_ACK_TMSTMP
FROM TABLE1 T1, TABLE2 T2
WHERE T2.MNFST_NBR = CAST ( T1.MANIFEST_NBR AS INTEGER )
AND T1.PO_STATUS_FLG = 4
AND DATE ( T2.MNFST_ACK_TMSTMP ) > '01/01/1900'
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Nov 17, 2011 4:17 pm    Post subject:
Reply with quote

Do you have any information on unique/duplicate indexes, explain, column types ?
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Thu Nov 17, 2011 7:26 pm    Post subject:
Reply with quote

Explain on Select query shows non unique index scan on table1 & clustering index scan on table2 joined by nested loop join & then workfile sort.
Index X9TABLE1:
PO_STATUS_FLG ASC CHAR 4

Index X1TABLE2:
MNFST_NBR ASC NUM 8 CLS

Update query explain shows a non-unique index scan, sort, fetch & then update.
Index X7TABLE1
MANIFEST_NBR ASC CHAR 8
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Nov 17, 2011 7:43 pm    Post subject:
Reply with quote

what percentage of rows on T1 have PO_STATUS_FLG = 4 ?
Is T2.MNFST_NBR , T2.MNFST_ACK_TMSTMP unique on Table2 ?

If unique you could try :
Code:
DECLARE SLS_DATE_CSR CURSOR WITH HOLD FOR
SELECT DISTINCT T2.MNFST_NBR, T2.MNFST_ACK_TMSTMP
FROM TABLE2 T2
WHERE 
AND T1.PO_STATUS_FLG = 4
AND T2.MNFST_ACK_TMSTMP  > '1900-01-01-00.00.00'
and exists (select 1 from Table1 T1 where
T1.MANIFEST_NBR = cast(T2.MNFST_NBR as char(8))
and  T1.PO_STATUS_FLG = 4 )
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Fri Nov 18, 2011 5:03 pm    Post subject:
Reply with quote

Both are non-unique.
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 -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Removing Extra Spaces in a comma sepa... Puspojit DFSORT/ICETOOL 3 Tue Sep 13, 2016 7:46 am
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am


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