Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Max number of rows in a table ( in one partition)

    Hi,

    I have a table that's in it own bufferpool with the following settings --

    NPAGES -- 640000
    PAGESIZE -- 8192

    The count of rows in the table is 766,961,355 and it grows by a decent amount everyday. Do you know what the limit on the number of rows for a table is? We have Db2 UDB 8.2 FP 11 ( 64-bit on Linux).
    Also, the partition group associated with the table currently shows a size of 71GB.


    thanks!!
    Last edited by db2user24; 07-01-11 at 18:44.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can find DB2 limits in the Appendix called “SQL Limits” that is in SQL Reference Vol 1 manual.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    thanks, I found this in the appendix --

    Most rows in a table per partition -- 4 x 10 power 9 ( am assuming this is for a table that is 512 GB)

    The table that I'm concerned about -- here is the tablespace information :

    Tablespace ID = 4
    Name = R_TBLSP
    Type = System managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 9442366
    Useable pages = 9442366
    Used pages = 9442366
    Free pages = Not applicable
    High water mark (pages) = Not applicable
    Page size (bytes) = 8192
    Extent size (pages) = 32
    Prefetch size (pages) = 192
    Number of containers = 16
    Minimum recovery time = 2005-10-14-14.32.51.000000


    It's set with a 8K page size.. according to the appendix, the max table size per partition for 8K is 128GB. The current count of records of the only table in the tablespace is 779,156,890 and the size of the container that belongs to the tablespace is currently at 73G. The count and size are only bound to grow so am wondering what the best solution is for it to avoid the max limit?

    Can I just increase the pagesize of the tablespace to 16K or 32K? How can I do that? Thanks!

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You cannot increase the pagesize of a tablespace or table. You will have to create a new tablespace (or use existing one) with a new table defintion assigned to that tablespace, then move the data over. You can use "rename table" to make the job a little easier.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2007
    Posts
    265
    thanks marcus, when you say use 'rename table' .. you mean after all the data is transferred over and the table in the original tablespace is dropped? what is the best way to move such a huge number of records from one table to another? sounds like a daunting task..thank you!

    also, is there no way to reassign the original table to the new tablespace with the bigger pagesize? I assume no..

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    First question=yes.

    Second question, best way is with an export and load utilities to move the data. It may be a little tricky if you have any foreign keys and you may need to do a set integrity statement afterwards (read the manual). You also need to handle any generated always columns with the proper load override options.

    You could also do a load command on new table doing a load from cursor on the old table (without having to an export), but I usually prefer export to flat file, and then load (personal preference).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Nov 2007
    Posts
    265
    thanks, for such a huge number of rows.. would you export the data to a single file in one go and then load from there? I typically export in bits and pieces even though it takes longer. Can you give me an example of the load command that you would run? I've always dealt with a comparatively small number of rows and for that, the import command has sufficed.

    Would it be something like this -- db2 load from datafile1.del of del insert into table1 ( col1, col2, ...)


    And one more question -- is there an alternate solution where I could somehow reassign the original table to the new tablespace? Sorry for all the questions.. I appreciate your help!
    Last edited by db2user24; 08-09-11 at 16:57.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2user24 View Post
    And one more question -- is there an alternate solution where I could somehow reassign the original table to the new tablespace? Sorry for all the questions.. I appreciate your help!
    No, you cannot do that in DB2 V8.2. There is a SP to do it in 9.7, but it just does the export/load for you.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by Marcus_A View Post
    You can find DB2 limits in the Appendix called “SQL Limits” that is in SQL Reference Vol 1 manual.


    I looked up the limits and was wondering if someone can help clarify this --

    Maximum size of a table per partition (in gigabytes) c g 512
    Maximum size of an index per partition (in gigabytes) 512
    Most rows in a table per partition 4 x 10 9


    Table 46. Database Manager Page Size Specific Limits
    Description
    8K page size

    Maximum size of a table per
    partition (in gigabytes)
    128



    The table I'm concerned about is in a tablespace with 8K page size so it seems like 128GB is the max size of the table. I also see that the maximum number of rows in a partition is 4 x 10 power 9... is that value for a table in a tablespace that has 32K page size ( maximum page size)?

    I care to know about the limits of the table size and table rows for 8K page sizes only.. thank you!!

  10. #10
    Join Date
    Nov 2007
    Posts
    265
    Anyone have an idea? Should I go with 4x10 power 9 ( or whatever it is for 8K pagesize if that is taken into account) or 128 GB... or just consider whatever limit will come first?
    Last edited by db2user24; 09-09-11 at 14:45.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2user24 View Post
    or just consider whatever limit will come first?
    Yes.
    ..............................

  12. #12
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by n_i View Post
    Yes.
    ..............................
    Thanks, do you know if the row limit is specific to a certain page size or is that just the total for any and all sizes? thx!

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2user24 View Post
    if the row limit is specific to a certain page size
    I believe it's independent of the page size.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •