View previous topic :: View next topic
|
Author |
Message |
grayWolf
New User
Joined: 04 Oct 2010 Posts: 19 Location: Land of broken dreams
|
|
|
|
Hi all,
We got a requirement to update the first 'n' rows of a table. I know the query to update the first 'n' rows of a table which has a primary key which is like.
UPDATE table_name
SET col_name = value
WHERE primary_key in (SELECT primary_key FROM table_name order by primary_key FETCH FIRST N ROWS ONLY)
But my table has 3 columns(X,Y,Z) that together gives us a unique row in the table.
Could you please let me know how the same update functionality described above can be used for my table.
I'm using DB2 9.1 version
Thanks in advance. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Code: |
...
where (x,y,z) in (select x,y,z from ...) |
|
|
Back to top |
|
|
grayWolf
New User
Joined: 04 Oct 2010 Posts: 19 Location: Land of broken dreams
|
|
|
|
Thanks a lot for the quick reply and it worked! But one quick question.
Requirement:
Update the whole table of 50000+ rows
What we did:
Fetching individual records from the table using a cursor and updating it.
It updates the first 20000 records and when it reaches the 20001st record it abends with -904. It may have something to do with the page size of the tablespace but am not really aware of it.
So any thoughts on this issue.?
Please let me know if you require some more details
Regards, |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
-904 doesn't say much, you need the reason code something like 00C90088
but it is probably a locking issue. something with lock escalation ? |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
This looks like you are implementing a bad way of solving restartability/concurrency.
Maybe you should go back to why you needed to update the first n rows .
what was the real requirement/problem ? |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Graywolf
are you issuing any COMMITs during the cursor processing? |
|
Back to top |
|
|
grayWolf
New User
Joined: 04 Oct 2010 Posts: 19 Location: Land of broken dreams
|
|
|
|
Apologies for the confusion.
The two question were quite different issues and its solved now. I will explain it here.
The real issue was we were trying to update the whole table,one row at a time. So after 20000 records it would abend with -904 sqlcode and with reason code 00C90096 and type of resource 00000304.
So what we did was issue a commit after an update and declared the cursor as "WITH HOLD". In this way even after every commit the cursor wouldn't close. Hence the whole process went fine.
Maybe it was an issue with the lock escalation as you explained it here.
Please throw light on the issue and let me know if you need further explanation.
Regards, |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Quote: |
00C90096
Explanation: The page, row or LOB lock on the page or subpage identified
by NAME in message DSNT500I or DSNT501I caused the total number of page,
row or LOB locks concurrently held to reach the installation maximum
number of page, row or LOB locks (NUMLKUS) allowed for a single agent in
the system. |
1) if you have to update the whole table , there is no need to open a cursor for the first n rows.
2) don't commit after each update, put in a counter and update each n updates.it wil give you better performance. |
|
Back to top |
|
|
grayWolf
New User
Joined: 04 Oct 2010 Posts: 19 Location: Land of broken dreams
|
|
|
|
Just want to clarify again that those two questions were not related and please note that we are not opening the cursor for the 'n' rows . We are fetching all the rows from the table and updating the columns.
Quote: |
2) don't commit after each update, put in a counter and update each n updates.it wil give you better performance.
|
I will take this point for a better performance.
Thanks a lot GuyC for your swift response.
Regards, |
|
Back to top |
|
|
|