The below listed steps show cases the SYSCATSPACE table space conversion from a DMS to an AUTOMATIC storage table space.
Step 1: Creating a SAMPLE database storing system catalog in a DMS table space
CREATE DATABASE sample AUTOMATIC STORAGE NO USING CODESET 1252 TERRITORY US COLLATE USING SYSTEM PAGESIZE 4096 RESTRICTIVE CATALOG TABLESPACE MANAGED BY DATABASE USING (FILE '/db/data/syscatspace.dat' 50000) DB20000I The CREATE DATABASE command completed successfully.
Step 2: Display the table space attributes using MON_GET_TABLESPACE () table function
db2 "SELECT SUBSTR (TBSP_NAME, 1, 12) AS TBSP_NAME, TBSP_ID, TBSP_TYPE, TBSP_CONTENT_TYPE, TBSP_USING_AUTO_STORAGE FROM TABLE (MON_GET_TABLESPACE('',-2)) AS T WHERE TBSP_NAME='SYSCATSPACE'"
TBSP_NAME TBSP_ID TBSP_TYPE TBSP_CONTENT_TYPE TBSP_USING_AUTO_STORAGE
------------ -------------------- ---------- ----------------- -----------------------
SYSCATSPACE 0 DMS ANY 0
Step 3: Create a new storage group to enable a non-automatic storage database to an automatic storage enabled database
db2 "CREATE STOGROUP data ON '/db/data/'" DB20000I The SQL command completed successfully.
Step 4: Convert your DMS system catalog table space to AUTOMATIC storage
db2 "ALTER TABLESPACE syscatspace MANAGED BY AUTOMATIC STORAGE USING STOGROUP data" DB20000I The SQL command completed successfully.
Step 5: Initiate a data re-balance operation using ALTER TABLE…. REBALANCE command
db2 "ALTER TABLESPACE syscatspace REBALANCE" DB20000I The SQL command completed successfully.
Step 6: You can verify the conversion using MON_GET_TABLESPACE () table function
db2 "SELECT SUBSTR (TBSP_NAME, 1, 12) AS TBSP_NAME, TBSP_ID, TBSP_TYPE, TBSP_CONTENT_TYPE, TBSP_USING_AUTO_STORAGE FROM TABLE (MON_GET_TABLESPACE('',-2)) AS T WHERE TBSP_NAME='SYSCATSPACE'"
TBSP_NAME TBSP_ID TBSP_TYPE TBSP_CONTENT_TYPE TBSP_USING_AUTO_STORAGE
------------ -------------------- ---------- ----------------- -----------------------
SYSCATSPACE 0 DMS ANY 1
