Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
Escapa

Senior Member

Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

 Posted: Wed Apr 01, 2009 5:12 pm    Post subject: Maximum number of records in table Can we find out what will be maximum number of records one table can have?

Robert Sample

Global Moderator

Joined: 06 Jun 2008
Posts: 8344
Location: Dubuque, Iowa, USA

 Posted: Wed Apr 01, 2009 5:23 pm    Post subject: You can probably find that in a manual ... link at the top of the page.
dbzTHEdinosauer

Global Moderator

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

 Posted: Wed Apr 01, 2009 6:29 pm    Post subject: 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

REXX Moderator

Joined: 13 Mar 2006
Posts: 1284
Location: Israel

 Posted: Wed Apr 01, 2009 7:40 pm    Post subject: Reply to: Maximum number of records in table 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

Site Director

Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

Posted: Wed Apr 01, 2009 10:01 pm    Post subject:

Hello,

 Quote: Can we find out what will be maximum number of records one table can have?
I suspect that many of us each took a different meaning to your question.

What is it you really want to know? Some possibilities are:
. the maximum number of rows that can ever be in a table.
. the maximum number of rows for for some specific table.
. the most rows some table has ever held.
. something else. . .

If you explain how you would use the answer, it may help someone provide a more useful reply.
Escapa

Senior Member

Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

 Posted: Thu Apr 02, 2009 8:19 am    Post subject: 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

Site Director

Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

 Posted: Thu Apr 02, 2009 8:51 am    Post subject: 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

Active User

Joined: 27 Sep 2008
Posts: 193
Location: chennai

 Posted: Fri Apr 03, 2009 1:05 am    Post subject: Reply to: Maximum number of records in table 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

Global Moderator

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

 Posted: Fri Apr 03, 2009 1:15 am    Post subject: Raghu, an excellent post.
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Question about dataset sequence numbe... harisukumaran JCL & VSAM 23 Tue May 29, 2018 9:31 am Extracting DDL for all list of intere... Ashishpanpaliya DB2 5 Mon May 21, 2018 4:08 pm MXG - Processing Compressed DB2 SMF r... vasanthz All Other Mainframe Topics 2 Thu May 10, 2018 12:47 am INVALID RECFM FOR DDNAME SYSLIN AND C... sandeep kumar302 All Other Mainframe Topics 7 Fri Apr 27, 2018 6:07 pm Extract the records with a PD field's... sudhakar84 DFSORT/ICETOOL 11 Mon Apr 02, 2018 7:26 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us