IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Fetch the first 'n' rows of a table having "Composite k


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
grayWolf

New User


Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams

PostPosted: Wed Feb 16, 2011 12:29 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Feb 16, 2011 1:14 pm
Reply with quote

Code:
...
where (x,y,z) in (select x,y,z from ...)
Back to top
View user's profile Send private message
grayWolf

New User


Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams

PostPosted: Wed Feb 16, 2011 1:25 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Feb 16, 2011 2:03 pm
Reply with quote

-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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Feb 16, 2011 2:05 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Feb 16, 2011 2:57 pm
Reply with quote

Graywolf

are you issuing any COMMITs during the cursor processing?
Back to top
View user's profile Send private message
grayWolf

New User


Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams

PostPosted: Wed Feb 16, 2011 2:59 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Feb 16, 2011 3:30 pm
Reply with quote

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
View user's profile Send private message
grayWolf

New User


Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams

PostPosted: Wed Feb 16, 2011 3:38 pm
Reply with quote

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 icon_smile.gif . 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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Pulling a fixed number of records fro... DB2 2
No new posts PuTTY - "User is not a surrogate... IBM Tools 5
No new posts Multiple table unload using INZUTILB DB2 2
Search our Forums:

Back to Top