DB2 add tablespace container but error SQL20318N

[former_member1332892]

I have experience with DB2 10.1. Since old tablespace exceed limit 4TB. I cannot fill-in table in that tablespace

(Error: DIA8710E Maximum table size was reached. )

So I tried to add new tablespace container by command:

DB2 ALTER TABLESPACE {TableSpace} ADD (FILE 'C0000001.LRG' 500G)

But there is error:

SQL20318N The ALTER TABLESPACE statement failed because the change is not

allowed for the type of table space. Table space name: "{TableSpace}". Table

space type: "AUTOMATIC STORAGE". Incompatible clause: "ADD". SQLSTATE=42858

As I check my tablespaces will be like this

C:\IBM\SQLLIB\BIN>db2 list tablespace containers for 2 show detail

Tablespace Containers for Tablespace 2

Container ID = 0

Name = F:\DB2\NODE0000\{DbName}\T0000002\C0000000.LRG

Type = File

Total pages = {NumberofPages}

Useable pages = {NumberofPages}

Accessible = Yes

How can I do?

[AlexGourdet]

Thank you for visiting SAP Community to get answers to your questions. Since this is your first question, I recommend that you familiarize yourself with Community Q&A, as the overview provides tips for preparing questions that draw responses from our members.

Should you wish, you can revise your question by selecting Actions, then Edit.

By adding a Picture to your profile you encourage readers to respond.

[Frank-Martin]

Hi,

your question is a little unclear

(Error: DIA8710E Maximum table size was reached. )

Did you hit a tablespace size limit or a table size limit?

Please run "db6util -f" and check if CONTENTS shows LONG for your user defined tablespaces.

If not, you may have missed to convert your tablespaces to large as described in the database upgrade guides.

The tablespace size limit for large tablespace is much larger than 4GB.

The tablespaces you are using are automatic storage tablespaces. Those extent automatically as long as space is available in the storage paths and you do not need to extent them manually.

Please also note that Db2 V10.1 is out of service and you should upgrade your database version as soon as possible.

There is no size limit for individual tables. But there is a size limit of 4GB for the LOB object of a table. Please use table function ADMIN_GET_TAB_INFO or DBACOCKPIT to check the current size of your table LOB object.

https://www.ibm.com/docs/en/db2/11.5?topic=aracp-admin-get-tab-info-retrieve-table-size-state-inform...

If you have hit the maximum LOB object size limit, here is no easy solution. You can either archive unused data or apply range partitioning. If you need to apply range partitioning to avoid the LOB object size limit you should get into contact with SAP support.

Regards

Frank

[Karen_Kuck]

Hi Teera,

Concerning Frank's recommendation to upgrade your database version because Db2 10.1 is out of mainstream maintenance: You can find all our Db2 for LUW upgrade guides here: SAP on IBM Db2 for Linux, UNIX, and Windows | SAP Help Portal

Kind regards,

Karen

[hans-juergen_zeltwanger]

Hi Teera,

adding to Frank's answer, you probably will find the information on storage management in the SAP on Db2 Learning Journey helpful.
https://help.sap.com/learning-journeys/89daa620fe47417eb4d8b1f5246eeb96

In particular this video:
https://sapvideoa35699dc5.hana.ondemand.com/?entry_id=1_ffqwckk3

This should help to further evaluate the difference between automatic storage and legacy DMS tablespaces..

[former_member1332892]

Thank you, I will tried to remove some unused data from table that reside on this tablespace.