cancel
Showing results for 
Search instead for 
Did you mean: 

SQL1139n The total size of the table space is too big.

Former Member
0 Kudos
981

Hi

Our R3 QA system runs on Solaris 10, using DB6 822.

We have now run into a problem, where we cannot extend a table. It is 66GB in size. Because the page file size is 8 kb, the limit is apparently 64 GB (we got it to 66GB).

It seems we will have to increase the page file size to get past the problem, say 16k or 32k. The question is how?

So far we have a framework in mind:

Create new table

Copy old table into new table

Drop old table

Recreate old table with bigger page file size

Copy new table into old table (now this new /old is getting confusing..)

Bob's you aunty, or something of that effect...

Is the thinking correct? If it is I will need much more detail, as I am not too familiar with DB2.

Thanks in advance!

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

hi derik,

the db6-specific limits for max tablespace/table size are

64/128/256/512GB for 4/8/16/32KB pagesize.

for problems like "tablespace/table reaches its pagesize dependent max. size",

we (DB6 porting team of SAP) have developed a special tool/ABAP report called 'DB6CONV'.

DB6CONV takes care of everything concerning a table conversion.

it is in depth described in OSS note 362325. the report itself it delivered by means of a transport, which is attached to note 362325 and/or can be downloaded via sapmats.

in your case you have to

a) get the latest DB6CONV transport and import it into your system

b) create a new tablespace with a pagesize >8k

c) assign a new data class for this tbsp in tables TADB6 (for data tbsp) and/or IADB6 (for index/tbsp)

d) run DB6CONV from transaction SE38 as described in note 362325

to convert(transfer) the table that is at/near the size limit

by specifying either target tablespaces or target data class

e) DB6CONV will duplicate the table first into the new tablespace, then copy the data into the newly created table. this can be done either 'offline' (fastes way, but table is not accessible during the conversion) or 'online' (slow, but table is accessible the whole time - despite a short time period when the switch from original to target table is performed)

please make yourself familiar with the tool and the documentation.

and feel free to ask if you need more information or have additional questions/remarks.

regards, frank

Former Member
0 Kudos

Frank:

You say:

the db6-specific limits for max tablespace/table size are

64/128/256/512GB for 4/8/16/32KB pagesize.

Derik's page size is 8k, why his table is limited to 64GB?

Thanks!

Answers (1)

Answers (1)

0 Kudos

the long text of SQL1139 reads:

"SQL1139N The total size of the table space is too big.

Explanation: The size of the current table space is too big. The

size of a REGULAR or USER TEMPORARY table space is limited to

0xFFFFFF (16777215) pages while the size of a SYSTEM TEMPORARY or

LONG table space is limited to 2 tera bytes (2 TB). [...]"

with a pagesize of 4KB (4096 byte) this gives you an upper limit for the tablespace size of 64GB and for deriks tbsp with pagesize of 8KB the max size for the tablespace is 128GB.

maybe there are other tables in this tbsp leaving only 66GB for the table mentioned until the tbsp becomes full/reaches its max size.

Derik: could you clarify?

regards, frank

Former Member
0 Kudos

Morning,

Frank, my mistake ... The page size was indeed 4k.

We managed to create a new tablespace and moved the largest tables over. One thing to keep in mind was to also create tempspace with the new tablespace's page size.

Frank, could you perhaps tell me why there is a choice in the first place? Why not just create the pagefile 32k by default and be done with it? Is there perhaps performance issues?

Thanks for your valuable input.

Regards

Derik

0 Kudos

hi derik,

1) i hope all topics and concerns related to table conversions are covered in the DB6CONV note 362325 - especially your point with the new tempspace should be mentioned there.

2) concerning your question about "why is there a choice of pagesize at all":

a) until DB2 V8.2.2 the SYSCATSPACE was always 4K. this could not be changed. so to avoid more than 1 bufferpool we used 4K as default pagesize for SAP tablespaces, too.

b) with the advent of unicode out UFT-8 implementation we needed (worst case)

to triple the length of char fields. thus UC installations with DB2 version <8.2.2

had SYSCATSPACE with 4K, all other SAP tablespaces with 16KB.

c) now since DB2 8.2.2 we finally got rid of the "SYSCATSPACE-pagesize=4K" limit and install with "uniform pagesize" -> all (including SYSCATSPACE) tablespaces on 16K.

d) we did not go to "all on 32K" (although desirable from a max size aspect), since

every page consisted of 255 'slots', so not more than 255 rows can fit in per page.

thus going from 16K pagesize to 32K would in general just waste space.

e) with V9 large row identifiers (Large RIDs) have been introduced, removing the 255 slots limit, so with V9 and Large RIDs enabled, a uniform pagesize of 32K could make sense...

i hope that answers your question, regards, frank