Thursday 18 April 2013

Enabling AUTORESIZE for DMS tablespaces on DB2 V9


When migrating DB2 V8 databases to DB2 V9 or when creating DMS (non automatic storage tablespaces) on DB2 V9 might be a good practice to enable autoresize for these tablespaces
example:

    db2 create db test automatic storage no on /home/dbpath

   db2 create tablespace ts1 managed by database using (file 'c:\temp\data\cont1.dat' 1000)

These are the ddl's which i am  having the my test databse and ts1 tablespace .so my tablespace  continuously getting error (un allocated new pages). So i want to enabled AUTORESIZE option to the DMS tablespaces.
ex:   db2 "alter tablespace ts1 AUTORESIZE yes"


After enabling AUTORESIZE for DMS tablespaces, DBA no longer needs to extend the tablespaces manually. DBA needs only to guarantee that there is enough space on the file systems so that DB2 can increase the tablespaces automatically when they get full. In this case, monitoring for tablespace full should be disabled.

To have control about how much the tablespaces are being increased, the parameter INCREASESIZE can be set (its default is automatic). For example:
db2 "alter tablespace ts1 INCREASESIZE 20 percent"
After enabling AUTORESIZE yes on DMS tablespaces you can still manually extend the existing containers or add new ones.
db2 "alter tablespace ts1 extend (all 100)"
db2 alter tablespace ts1 add (file '\home\data\cont2.dat' 1100)

No comments:

Post a Comment