After installation of DB2 V9.7 for Linux/Unix/Windows users may migrate their existing DB2 databases to make use of the new features. One important feature is „AUTOMATIC STORAGE“ which enables users to reduce high watermark and already allocated space. DB2 may itself increase storage of tablespaces whenever needed. This feature is interesting especially for databases which have a large number of tables in tablespaces and usually suffer from high „high watermarks“. SAP database administrators know!

To enable databases to make use of AUTOMATIC STORAGE you probably will first ALTER the existing database:

alter database mydb ADD STORAGE on 'device|path'; 

Then you would probably create new tablespaces and move all tables from the old to the new tablespace using ADMIN_TABLE_MOVE stored procedure.

But what happens to SYSCATSPACE? You cannot create a new tablespaces for the catalog and cannot move catalog tables. What happens if you just ALTER the SYSCATSPACE tablespace, using for example …

ALTER TABLESPACE SYSCATSPACE MANAGED BY AUTOMATIC STORAGE;

With the ALTER TABLESPACE … AUTOMATIC STORAGE command you may change the type of the SYSCATSPACE to AUTOMATIC STORAGE, but you can not make it reclaimable. The tablespace will be marked as „hybrid“ and it will be  impossible in the future of the database to LOWER HIGH WATERMARK or REDUCE space!

The only way to make the SYSCATSPACE reclaimable is to create a new database on V9.7 and to restore a backup of the existing database inside.

The steps you would need to do this are descript in the SAP whitepaper „Changing SYSCATSPACE page size using DB2 ‚RESTORE … TRANSPORT'“. You can find it under the following link:

http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/4082d5cf-4259-2e10-2cbd-f3979b7e4241