How to resize tablespace/container using automatic storage in db2 9.1 versi

[Former Member]

Hi

We are using db2 9.1 and database has reached up 1100 GB with in 7 months of go live. Can you advice on to how we can resize tablespace/container the way we do it in oracle?

Thanks in advance

Hetal

[Former Member]

Hi,

You have to check why the database size growing so fast and Archiving could be too soon in your case.

Here is the link which has information on re-sizing Tablespaces/Containers.

db2 alter tablespace USERSPACE1 resize (all containers 1024 M)

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdo...

[Former Member]

Hi

Thanks for the link bt let me know if we can use resize clause in db2 9.1 version

Thanks

Hetal

[former_member227283]

Hi,

Logically it is not possible the database size will grow in such maner. Can you check wether table loggin is enable in your system.

Check ad let us know which are few biggest table in your SAP system by size.

Thanks,

Anil Bhandary

[Former Member]

Hi

Anil can you tell me how can i find table logging is on or off?

Below are tables having maximum size

FAGL_SPLINFO 89,183,744

APQD 56,324,640

TST03 41,483,456

MSEG 40,420,128

FAGL_SPLINFO_VAL 37,028,448

ACCTIT 28,511,488

ARFCSDATA 27,545,856

BSIS 22,874,144

FAGLFLEXA 20,882,912

LIPS 20,185,920

Thanks

Hetal

Edited by: Hetal@amul on Dec 22, 2011 11:50 AM

[former_member189725]

With the resize option you only get the space unallocated in a container above the high water mark . You can definitely issue the command and get some more space unused in your filesystem . What best you can do is run an online reorg on the tables you mentioned . This would free up space within the tablespace but this space would not be released to the file system as they are trapped below the highwater mark. This would definitely bring down your DB size and the unused space inside the database would grow. If you want to release space to the filesystem , you will have to use the db2dart utility to lower the high watermark.

Follow the link for reference

[http://www-01.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=highwatermark&uid=swg21006526&loc=en_US&cs=utf-8&lang=en]

Also if you encounter an error by running the resize command , refer to this

[http://www-01.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=highwatermark&uid=swg21234267&loc=en_US&cs=utf-8&lang=en]

In DB2 9.7 , this has become much easier and its easy to lower the high water mark .

[Former Member]

hi mate

go throgh with this stuff it might me useful for u.

You can maintain the tablespace size using either the DBA Cockpit or DB2 CLP.

Increasing the Size of a Tablespace

Using the DBA Cockpit :

In your SAP system, call transaction DBACOCKPIT and choose Space --> Tablespaces in the

navigation frame. On the Space: Tablespace Configuration screen, choose Change.

Using DB2 CLP

Using SQL statements, you can perform the following actions:

Add a new container to a DMS tablespace by entering the following SQL statement:

alter tablespace <tablespace name> add ( file '<container name>' <number of pages> )

The DB2 database manager automatically rebalances the tables in the DMS tablespace

across all available containers. During rebalancing, data in the tablespace remains

accessible

Increase the size of one or more containers in the DMS tablespace by entering the

following SQL statement:

Extend one or more existing tablespace containers by a specific size by entering the following SQL statement:

alter tablespace <tablespace name> extend (all containers <number additional pages> )

Resize one or more existing tablespace containers to a specific size by entering the following SQL statement:

alter tablespace <tablespace name> resize (all containers <new container size in pages> )

Under certain circumstances the DB2 database manager automatically rebalances the

extents in the DMS tablespace across all available containers. During rebalancing, data in

the tablespace remains accessible.