Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Number of rows in a DB2 page

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

New User


Joined: 14 Jan 2010
Posts: 4
Location: USA

PostPosted: Thu Jan 14, 2010 12:13 am    Post subject: Number of rows in a DB2 page
Reply with quote

Hi,
I searched for a similar post but cant find one. Can anyone tell me how to find out the number of rows in a DB2 page. Also, is there any way of knowing what exactly are the contents of a page, given the exact page number? Any help would be appreciated much.

Thanks,
Shub
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: Thu Jan 14, 2010 12:41 am    Post subject:
Reply with quote

Hello and welcome to the forum,

If you explain how this might be used, someone may be able to profide a usable reply.

Why are you interested in how many rows are on some specific page?
Back to top
View user's profile Send private message
Shubhradeep

New User


Joined: 14 Jan 2010
Posts: 4
Location: USA

PostPosted: Thu Jan 14, 2010 12:57 am    Post subject:
Reply with quote

Hey,
Actually I am researching on a issue where we are having deadlocks in Db2 tables. We are getting the page number, but not the contents of that page to see on which record(or records) we should be focusing on. I was wondering if decreasing the number of rows in a page might solve our problem of deadlocks

Thanks,
Shub
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: Thu Jan 14, 2010 1:20 am    Post subject:
Reply with quote

Hello,

If there really are deadlocks (rather than timeouts), the problem is because of the design of the code in the problem update modules. The sequence of locking would cause deadlock problems.

Your system probably uses page-level locking. Many do. . .

If the problem is a timeout rather than a deadlock and you switch to row level locking, this may be reduced/prevented.

In an older release of db2, i had a group force only 1 row on a page because of the timeout problems encountered because row-level locking was not then implemented.
Back to top
View user's profile Send private message
Shubhradeep

New User


Joined: 14 Jan 2010
Posts: 4
Location: USA

PostPosted: Thu Jan 14, 2010 1:43 am    Post subject:
Reply with quote

Hi,
The problem is deadlock and not timeout. We are having multiple transactions trying to process same page simultaneously causing the issue. Row level locking will be a overhead considering the number of records. If you can let me know how to determine the number of rows in a page, I would like to see if reducing that minimizes the deadlocks.

Thanks,
Shub
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: Thu Jan 14, 2010 1:52 am    Post subject:
Reply with quote

Hello,

Quote:
Row level locking will be a overhead considering the number of records.
An untested assumption. . .

If the manual can be believed, 255 is the max rows per page:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqj10/A.0?ACTION=MATCHES&REQUEST=rows+per+page&TYPE=FUZZY&SHELF=&DT=20040216135741&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT

For an approximation of the actual number of rows, divide the page size by the number of bytes in a row.

To repeat, deadlocks are almost always due to poor/inconsistent design of the update code. . .
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jan 14, 2010 2:09 am    Post subject:
Reply with quote

Quote:
To repeat, deadlocks are almost always due to poor/inconsistent design of the update code


d.sch.,
by now you should realize that the fools that created the crap code do not want to hear that.
they want a solution to the problem other than fixing the problem

sorta along the lines of Mac's motto:
could not fix the brakes, so I made your horn louder.
Back to top
View user's profile Send private message
Shubhradeep

New User


Joined: 14 Jan 2010
Posts: 4
Location: USA

PostPosted: Thu Jan 14, 2010 2:21 am    Post subject:
Reply with quote

Hi D.Sch,
Thanks for the link. I knew the max numbers of rows in a page. The approximation guide was helpful for me.

D.Br,
Thank you too for your encouraging words. Probably in a real world where u need to meet deadlines every next day, sometimes making the horn louder instead of changing millions of lines of 'crapy' code helps!!

Thanks,
Shub
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jan 14, 2010 2:28 am    Post subject:
Reply with quote

yeah yeah, yeah,
everyone has an excuse.
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: Thu Jan 14, 2010 3:28 am    Post subject:
Reply with quote

Hello,

Quote:
. . .do not want to hear that.
they want a solution to the problem other than fixing the problem
Yup, and usually, there is no magic bullet that will "solve" it. . .

Quote:
Probably in a real world where u need to meet deadlines every next day, sometimes making the horn louder instead of changing millions of lines of 'crapy' code helps!!
I've been several places that tell me "You don't understand why we have to do this in a bad way". The only real reason they "had" to do bad things is because they were unaware of any other way to proceed. . . When shown a "good" way, they are often pleasantly surprised to see that doing it "right" goes faster and is more error free than the kludge that was in process. It is faster/easier to implement properly than to just "throw things together" - but this implies there is proper project leadership and a basic understanding of certain things "not to do".

If you isloate the processes that cause the deadlock most often and correct them, it will probably provide the most relief most quickly. Over time, the others can be reviewed/corrected.
Back to top
View user's profile Send private message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Tue Jan 19, 2010 10:15 pm    Post subject:
Reply with quote

Hi,
If you have the page number you can use DSN1PRNT for finding number of record on the page.

Thanks
Manshadi
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 column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Profile changed to NUMBER ON STD (fro... Martylin TSO/ISPF 1 Thu Aug 03, 2017 9:14 am
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us