|
View previous topic :: View next topic
|
| Author |
Message |
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| 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 |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
FWIW, none of my clients allow SELECT * in their code. |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| optimistic locking |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| If its cics you might look at ENQ. As asked before please tell us why you need order by for update? |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| 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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
|
|
