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!!

You can find DB2 limits in the Appendix called “SQL Limits” that is in SQL Reference Vol 1 manual.

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!

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.

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..

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).

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!

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.

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!!

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?

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!

I believe it's independent of the page size.