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
 
Maximum number of records in table

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

Senior Member


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

PostPosted: Wed Apr 01, 2009 5:12 pm    Post subject: Maximum number of records in table
Reply with quote

Can we find out what will be maximum number of records one table can have?
Back to top
View user's profile Send private message

Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8154
Location: East Dubuque, Illinois, USA

PostPosted: Wed Apr 01, 2009 5:23 pm    Post subject:
Reply with quote

You can probably find that in a manual ... link at the top of the page.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Apr 01, 2009 6:29 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1243
Location: Israel

PostPosted: Wed Apr 01, 2009 7:40 pm    Post subject: Reply to: Maximum number of records in table
Reply with quote

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.
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: Wed Apr 01, 2009 10:01 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Escapa

Senior Member


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

PostPosted: Thu Apr 02, 2009 8:19 am    Post subject:
Reply with quote

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?
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 Apr 02, 2009 8:51 am    Post subject:
Reply with quote

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 icon_smile.gif
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Fri Apr 03, 2009 1:05 am    Post subject: Reply to: Maximum number of records in table
Reply with quote

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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Apr 03, 2009 1:15 am    Post subject:
Reply with quote

Raghu,

an excellent post.
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 Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 21 Sun Aug 27, 2017 10:35 pm
No new posts Adding a userid to SMTP Security table Yolanda Harvey JCL & VSAM 1 Sun Aug 13, 2017 6:16 pm

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