Maximum number of records in table

Escapa · Posted: Wed Apr 01, 2009 5:12 pm

Can we find out what will be maximum number of records one table can have?

Robert Sample · Posted: Wed Apr 01, 2009 5:23 pm

You can probably find that in a manual ... link at the top of the page.

dbzTHEdinosauer · Posted: Wed Apr 01, 2009 6:29 pm

none, since db2 tables only have rows, there are no records in tables. that is a term used in files.

so, now that you know the correct terminology, your search for the answer is easier.

suggest you look at the manuals covering design of tables, since the answer is dependent on allocation.

Marso · Posted: Wed Apr 01, 2009 7:40 pm

Our DBAs always ask "how many rows will you have in your table" so they can allocate enough space in the table.
Maybe your DBA has a formula for quick calculation.
Don't forget that it will always be an approximation, as DB2 tables are stored in VSAM files that can have many EXTENTs.

dick scherrer · Posted: Wed Apr 01, 2009 10:01 pm

Hello,

Escapa · Posted: Thu Apr 02, 2009 8:19 am

Hi All, thanks for inputs..

@Dick,

I want to know maximum number of rows for some specific table if i know the allocation of tablespace and tablespace is having only that table.

If one row of table is n bytes, is there any way to maximum number of rows table can have?

dick scherrer · Posted: Thu Apr 02, 2009 8:51 am

Hello,

You should be able to get an approximate number by dividing the total space (mbytes) by the number of bytes in one row.

One way to get a more accurate number would be to create a clone table that is only 10% (or even 1% if the table is huge) of the current allocation. Make sure the clone/test area does not auto-extend. Insert rows until the space is full and keep a count of rows inserted. When the space fills, display the number inserted. Multiply the number by 10 or 100 depending on how you sized the test table.

Someone else may have a more elegant suggestion

Raghu navaikulam · Posted: Fri Apr 03, 2009 1:05 am

Hi Sambhaji

To find the number of row that can be accommodated in a table depends on various factors. You can not simply add the number of bytes in a row. As you may aware that rows are stored in pages of size 4 to 32 k. Normally 4 K size. The entire 4 K (4096 bytes) can not be used for storing data. There are overheads in the form of Page header and Page trailer.
The storage for a row is also need some consideraion. VARCHAR column needs 2 bytes extra apart form the maximum size and NULLable column need 1 byte extra. There may be free space declared at the time of defining the tablespace. There are pages where rows can not be inserted( Header page, Spacemap page etc). More over the maximum number of rows in a page(4K) is limited to 255 even though the size of the row is so small. And finally the space allocation in terms of KB for Primary and Secondary quantity. Depending on the DB2 version the number of allocation also will vary, so the size of the table/space.

I think I confused you. Better you contact your DBA for a clarification

Regards
Raghu

dbzTHEdinosauer · Posted: Fri Apr 03, 2009 1:15 am

Raghu,

an excellent post.