z.OS v11 NFM MAXROWS

Tim Wilkins

z.OS v11 NFM MAXROWS

The manual states for MAXROWS  "The integer can range from 1 through 255. "

I have a table that has a row with a length of 8 bytes so by my figure on a 4K page 500 rows will likely fit.   However the limit is 255 rows so that means what almost 1/2 of the data page space is wasted?   What's up with that IBM? 

 

Roy Boxwell

z.OS v11 NFM MAXROWS
(in response to Tim Wilkins)
More than that in one byte is quite a tough problem...perhaps Db2 Quantum could do it when they get 50+ qubits up and running...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 22 Nov 2017, at 21:14, Tim Wilkins <[login to unmask email]<mailto:[login to unmask email]>> wrote:


The manual states for MAXROWS "The integer can range from 1 through 255. "

I have a table that has a row with a length of 8 bytes so by my figure on a 4K page 500 rows will likely fit. However the limit is 255 rows so that means what almost 1/2 of the data page space is wasted? What's up with that IBM?



-----End Original Message-----

Steen Rasmussen

z.OS v11 NFM MAXROWS
(in response to Tim Wilkins)
Considering the new Relative Page Numbering in Db2 12, I expect this limit to be lifted in the future. Remember there’s only 8 bit available for the RID – hence you can’t address more than 255 rows.

Steen Rasmussen
CA technologies

From: Tim Wilkins [mailto:[login to unmask email]
Sent: Wednesday, November 22, 2017 3:15 PM
To: [login to unmask email]
Subject: [DB2-L] - z.OS v11 NFM MAXROWS

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

The manual states for MAXROWS "The integer can range from 1 through 255. "

I have a table that has a row with a length of 8 bytes so by my figure on a 4K page 500 rows will likely fit. However the limit is 255 rows so that means what almost 1/2 of the data page space is wasted? What's up with that IBM?



-----End Original Message-----

Daniel Luksetich

z.OS v11 NFM MAXROWS
(in response to Roy Boxwell)
Isn’t the row header 6 bytes? So, you actual record length is 14 bytes. There is the 22 page header and 2 byte directory entry for each row. I think you are not wasting space at all.

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, November 22, 2017 2:29 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: z.OS v11 NFM MAXROWS



More than that in one byte is quite a tough problem...perhaps Db2 Quantum could do it when they get 50+ qubits up and running...



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.

-Product Development-

Heinrichstrasse 83-85

40239 Düsseldorf/Germany

Tel. +49 (0)211 96149-675

Fax +49 (0)211 96149-32

Email: [login to unmask email] <mailto:[login to unmask email]>

http://www.seg.de



Software Engineering GmbH

Amtsgericht Düsseldorf, HRB 37894

Geschäftsführung: Gerhard Schubert, Bettina Schubert


On 22 Nov 2017, at 21:14, Tim Wilkins <[login to unmask email] <mailto:[login to unmask email]> > wrote:

The manual states for MAXROWS "The integer can range from 1 through 255. "

I have a table that has a row with a length of 8 bytes so by my figure on a 4K page 500 rows will likely fit. However the limit is 255 rows so that means what almost 1/2 of the data page space is wasted? What's up with that IBM?





-----End Original Message-----



-----End Original Message-----

Michael Hannan

RE: z.OS v11 NFM MAXROWS
(in response to Steen Rasmussen)

Tim,

Agreeing with Steen, that when they originally designed the RIDs for placement in the Index Entries they designed 3 bytes for the Page Number and one byte for the row number in the page, allowing up to 255 rows per page. With 255 rows in a 4K page we get a little short of 16 bytes per row needed to store a full 255 rows.

Subsequently we saw advancements to 5 byte RIDs to allow more pages, and later enhancements to 6 bytes and even 7 byte RIDs, allowing a combination of more pages per partition, more partitions, and with larger page size we may even get more rows per page allowed, but it seems unimportant to enhance that.

If total record length overhead is much lower than 15 bytes, we have space wastage. In these cases, little benefit to compress the rows. LOL

If you choose a non compressed table and add up its column lengths you may find it is less than the RECLENGTH stored in the DB2 Catalog SYSTABLES. There they document a 8 byte overhead on top of the sum of the column lengths. That would be 6 bytes in the record header, plus a two byte page directory entry.

So your table with 8 bytes of column data will not manage to get a full 255 rows per page if uncompressed.

So in the end, we assume that IBM know what they are doing with these internal designs.

Michael Hannan
 
In Reply to Steen Rasmussen:

Considering the new Relative Page Numbering in Db2 12, I expect this limit to be lifted in the future. Remember there’s only 8 bit available for the RID – hence you can’t address more than 255 rows.

Steen Rasmussen
CA technologies

From: Tim Wilkins [mailto:[login to unmask email]
Sent: Wednesday, November 22, 2017 3:15 PM
To: [login to unmask email]
Subject: [DB2-L] - z.OS v11 NFM MAXROWS

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

The manual states for MAXROWS "The integer can range from 1 through 255. "

I have a table that has a row with a length of 8 bytes so by my figure on a 4K page 500 rows will likely fit. However the limit is 255 rows so that means what almost 1/2 of the data page space is wasted? What's up with that IBM?



-----End Original Message-----



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd