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
 

 

How to update milillions of records by a specified order?

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

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Wed Nov 13, 2013 7:14 pm    Post subject: How to update milillions of records by a specified order?
Reply with quote

I try to update milillions of records in a table, and tried to use below cursor
Code:

DECLARE SENSITIVE  SCROLL CURSOR WITH HOLD
FOR
    SELECT  * FROM TA
    WHERE...
    ORDER BY COLA

WITH HOLD is specified, because after COMMIT, I should continue with the update;
ORDER BY is specified, because these data should be update in a particular order, while when ORDER BY is specified, ' FOR UPDATE' cannot be specified, which means that the cursor is read only, not be intended for update.

Is there a good solution to achieve this?
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: Wed Nov 13, 2013 7:47 pm    Post subject:
Reply with quote

Hello,

For so many rows, i'd unload the table, make the changes to the sequential data, sort the data (if needed) and then reload the TABLE.

I would NOT update millions of rows via sql. . .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Nov 13, 2013 7:54 pm    Post subject:
Reply with quote

Just because the cursor isn't "FOR UPDATE" does not mean you can't update the rows. Only that you can't use CURRENT, you could use something similar to WHERE PK = :PK
(I just hope that your update does not change the order of the rows)

Smileys disabled for clarity
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Thu Nov 14, 2013 8:47 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

For so many rows, i'd unload the table, make the changes to the sequential data, sort the data (if needed) and then reload the TABLE.

I would NOT update millions of rows via sql. . .

this is not an option, as online programs are updating the table without cease.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Thu Nov 14, 2013 8:56 am    Post subject:
Reply with quote

GuyC wrote:
you could use something similar to WHERE PK = :PK


Yes , I 've thought of this method before.
But, if I use WHERE PK=:PK, I have to first issue below statement to lock the row before update.

SELECT ...
FROM ...
WHERE PK=:PK
WITH RS USE AND KEEP UPDATE LOCKS.

This brings another I/O against DB2...
that's to say, if I want to update a row on specified sequence, I have to do below things sequentially:
1. DECLARE cursor with hold for
FOR select...
from...
ORDER BY cola
2. OPEN CURSOR
3. FETCH CURSOR ==> here ,PK can be retrieved for further process.
4. SELECT FROM TBA WHERE PK=:PK WITH RS USE AND KEEP UPDATE LOCKS
5. UPDATE WHERE PK=:PK
6. COMMIT(optional)
7. FETCH NEXT....

Is there a better way to avoid so many IO?

Smileys disabled
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Nov 14, 2013 1:53 pm    Post subject:
Reply with quote

dejunzhu wrote:
But, if I use WHERE PK=:PK, I have to first issue below statement to lock the row before update.

Why? how are your applications bound by default? UR ?
Unlikely, but if so and you are afraid that the row you are updating in 5) has changed since the fetch, you might want to consider using optimistic locking:
WHERE PK = :PK and TS_LASTUPDATE =:TS_LASTUPDATE

at least if you have a column in your table similar to :
TS_LASTUPDDATE NOT NULL
GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Thu Nov 14, 2013 6:19 pm    Post subject:
Reply with quote

The part I do not understand is the reason to 'update in a specific order'.

If the data is stored by DB2 in order of column a: 3, 4, 5, 1, 2

You are going to update all the rows, and insist on the updates to occur in 1, 2, 3 ,4 ,5 order.

After the updates they are still in 3, 4, 5, 1, 2 order within DB2.

Depending on the number of rows returned, the sorting of the result set is extra work that serves no purpose.

Please correct me if I am wrong.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Nov 14, 2013 9:42 pm    Post subject:
Reply with quote

just a random business requirement : supply priority? suppose you got 1000 articles incoming and that you have to distribute them over the different requests according to a certain priority, TS_requested with a maximum of 50% of the original quantity requested.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Nov 18, 2013 8:35 pm    Post subject:
Reply with quote

you are updating one row at-a-time.

you do not have to issue a lock - why would you?

read the cursor,
do a singleton update - not using cursor current (as others have said)
and if you want to reaaaalllly insure that the row you are updating is the exact row that is in your cursor,
use a WHERE clause that includes all the columns that you have selected.

by the way,
use COPY & PASTE from the dclgen (in another TSO session)
to access all the column names.
get out of the habit of using
Code:
SELECT  *
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 Nov 18, 2013 9:12 pm    Post subject:
Reply with quote

Hello,

FWIW, none of my clients allow SELECT * in their code.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Nov 22, 2013 12:18 pm    Post subject:
Reply with quote

yes.... I know it's a bad habit to code "SELECT * FROM", here I code this, just for brief description to my question.

Take below scenario for instance:
step1. SELECT ONE RECORD FROM TABLE; ==>to get all columns for further update
step2. DO some bussiness logic to the selected data;
step3. UPDATE the record.

I must ensure the record is exclusively used by the program between step1 and step3, for data integrety.

and this is why I must lock the record before update.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Nov 22, 2013 1:12 pm    Post subject:
Reply with quote

optimistic locking
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Fri Nov 22, 2013 9:13 pm    Post subject:
Reply with quote

If its cics you might look at ENQ. As asked before please tell us why you need order by for update?
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Sat Nov 23, 2013 10:22 am    Post subject:
Reply with quote

I don't have specific bussiness requirement, I was asked the question by my peers, and I could not provide a satisfactory answer, so I post it here.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Sat Nov 23, 2013 3:35 pm    Post subject: Reply to: How to update milillions of records by a specified
Reply with quote

I'm not sure we like chasing these without all the information.

If it is a speculative question, please make that clear when asking it.
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: Sun Nov 24, 2013 7:03 am    Post subject:
Reply with quote

Hello,

These peers must have some reason(s) for asking. Post their reason(s).

If they have no reasons, they should not waste your time asking . . .

imho
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Nov 29, 2013 6:05 pm    Post subject:
Reply with quote

the problem is insuring the batch update is performed before an online program does an update inbetween the batch read and update.
and not issuing a lot of locks (by virtue of the update) which required occaisional commits.

before e-cobol multi-tasking, i always had a utility online program,
which i called via batch link - passing the row
the called online program did a singleton row cursor open for select for update
and if the selected row mathced the passed row, performed the update.
if not, did not.
end of online program performed the commit and i did not have any long
strings of locks.

using E-COBOL's multi-tasking,
you could have a control program that starts a batch task (enclave) for each row read.
(actually, you should dump the table and read the qsam file as a trigger in the control program, then you don't have the problem of large cursor
commits to issue.
end of batch enclave ('started' program) issues the commit (system issues the commit, not the program)

obviously you need a 'reject' table, which you would continually reprocess
until empty.

and yes, you need a means to 'control the number of active enclaves.

i like the multi-tasking aspect of batch e-cobol,
because I don't have to rely on CICS resources
and because it stays in a batch environmnet - which receives less priority as the online.
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
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 How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


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