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
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Fetch the first 'n' rows of a table having "Composite k
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: 1278
Location: Belgium

PostPosted: Wed Feb 16, 2011 1:14 pm    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed Feb 16, 2011 2:03 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed Feb 16, 2011 2:05 pm    Post subject:
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    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed Feb 16, 2011 3:30 pm    Post subject:
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    Post subject:
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    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 DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Amount field is getting corrupted whe... thesumitk SYNCSORT 5 Tue Oct 18, 2016 8:20 pm


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