The conversion process consists of 3 steps:
1. Taking the backup of an existing database
2. Creating a new database using automatic storage
3. Performing a restore of an old existing database into new database using schema transport. For more information about SCHEMA TRANSPORT please refer to https://enterprisedb2.com/2016/07/17/db2-schema-transport/
The below listed example illustrates the SYSCATSPACE table space conversion from a SMS to an AUTOMATIC storage table space.
Step #1: Create a database with a SMS SYSCATSPACE table space
db2 "CREATE DATABASE SMSDB AUTOMATIC STORAGE NO CATALOG TABLESPACE MANAGED BY SYSTEM USING ('/db/data/syscatspace')"
DB20000I The CREATE DATABASE command completed successfully.
Step #2: Create a few DMS data table spaces to restore user data
db2 "CREATE TABLESPACE tbsp1 MANAGED BY DATABASE USING (FILE '/db/data/TBSP1' 5000)" DB20000I The SQL command completed successfully. db2 "CREATE TABLESPACE tbsp2 MANAGED BY DATABASE USING (FILE '/db/data/TBSP2' 5000)" DB20000I The SQL command completed successfully.
Step #3: Create tables inside newly created DMS user table spaces
db2 "CREATE TABLE demo.employee ( empid INTEGER, name VARCHAR(30), deptid INTEGER) IN tbsp1" DB20000I The SQL command completed successfully. db2 "CREATE TABLE demo.department ( deptid INTEGER NOT NULL PRIMARY KEY, deptname VARCHAR(30)) IN tbsp2" DB20000I The SQL command completed successfully.
Step #4: Insert records into newly created tables
db2 "INSERT INTO demo.employee VALUES (001,'Dan, Mark',001), (002,'Fred, Ray',002), (003,'Milan, Mohan',003)" DB20000I The SQL command completed successfully. db2 "INSERT INTO demo.department VALUES (001, 'Sales'), (002, 'Engineering'),(003, 'Management')" DB20000I The SQL command completed successfully.
Step #5: Display records
db2 "SELECT * FROM demo.employee" EMPID NAME DEPTID ----------- ------------------------------ ----------- 1 Dan, Mark 1 2 Fred, Ray 2 3 Milan, Mohan 3 3 record(s) selected. db2 "SELECT * FROM demo.department" DEPTID DEPTNAME ----------- ------------------------------ 1 Sales 2 Engineering 3 Management 3 record(s) selected. Step #6: Enabled archive logging for SMSDB and inserted 393216 records into DEMO.EMPLOYEE table db2 "UPDATE DB CFG smsdb USING LOGARCHMETH1 disk:/db/logs/smsdb/" db2 "INSERT INTO demo.employee SELECT * FROM demo.employee" db2 "SELECT COUNT(*) FROM demo.employee" 1 ----------- 393216 1 record(s) selected.
Step #7: Take an online backup of SMSDB using below listed command
db2 "BACKUP DB smsdb ONLINE TO /backup COMPRESS" Backup successful. The timestamp for this backup image is : 20171111142216
Step #8: Create a new AUTOMATIC STORAGE database
==> db2 "CREATE DB autodb" DB20000I The CREATE DATABASE command completed successfully.
Step #9: Verify the SYSCATSPACE table space type both in SMSDB and AUTODB
==> db2 "CONNECT TO smsdb" Database Connection Information Database server = DB2/LINUXX8664 10.5.8 SQL authorization ID = DB2DBA01 Local database alias = SMSDB ==> db2 "LIST TABLESPACES SHOW DETAIL" | more Tablespaces for Current Database Tablespace ID = 0 Name = SYSCATSPACE Type = System managed space Contents = All permanent data. Regular table space. State = 0x0000 Detailed explanation: Normal Total pages = 4404 Useable pages = 4404 Used pages = 4404 Free pages = Not applicable High water mark (pages) = Not applicable Page size (bytes) = 32768 Extent size (pages) = 4 Prefetch size (pages) = 24 Number of containers = 1 ==> db2 "CONNECT TO autodb" Database Connection Information Database server = DB2/LINUXX8664 10.5.8 SQL authorization ID = DB2DBA01 Local database alias = AUTODB ==> db2 "LIST TABLESPACES SHOW DETAIL" | more Tablespaces for Current Database Tablespace ID = 0 Name = SYSCATSPACE Type = Database managed space Contents = All permanent data. Regular table space. State = 0x0000 Detailed explanation: Normal Total pages = 7168 Useable pages = 7164 Used pages = 6372 Free pages = 792 High water mark (pages) = 6372 Page size (bytes) = 32768 Extent size (pages) = 4 Prefetch size (pages) = 24 Number of containers = 1
Step #10: Identify transportable sets for SCHEMA TRANSPORT
CONNECT TO smsdb; db2 "SELECT DISTINCT SUBSTR(tabschema,1,10) SCHEMA, SUBSTR(tbspace,1,20) TABLESPACE FROM syscat.tables WHERE TBSPACE IS NOT NULL AND TBSPACE NOT LIKE 'SYS%'" SCHEMA TABLESPACE ---------- -------------------- DEMO TBSP1 DEMO TBSP2 2 record(s) selected.
Step #11: Restore SMSDB database user table spaces (TBSP1 and TBSP2) into AUTODB database
db2 "RESTORE DB smsdb TABLESPACE (tbsp1,tbsp2) SCHEMA (demo) FROM /backup TAKEN AT 20171111142216 TRANSPORT INTO autodb LOGTARGET /db/logs/logtarget" DB20000I The RESTORE DATABASE command completed successfully.
Step #12: Verify table spaces and data is copied across to AUTODB
==> db2 connect to AUTODB Database Connection Information Database server = DB2/LINUXX8664 10.5.8 SQL authorization ID = DB2DBA01 Local database alias = AUTODB ==> db2 "SELECT DISTINCT SUBSTR(tabschema,1,10) SCHEMA, SUBSTR(tbspace,1,20) TABLESPACE FROM syscat.tables WHERE TBSPACE IS NOT NULL AND TBSPACE NOT LIKE 'SYS%'" SCHEMA TABLESPACE ---------- -------------------- DEMO TBSP1 DEMO TBSP2 2 record(s) selected. ==> db2 "SELECT COUNT(*) FROM demo.employee" 1 ----------- 393216 1 record(s) selected. ==> db2 "SELECT COUNT(*) FROM demo.department" 1 ----------- 3 1 record(s) selected.
Step #13: Convert TBSP1 and TBSP2 from DMS table space to AUTOMATIC STORAGE using below listed command
==> db2 "ALTER TABLESPACE tbsp1 MANAGED BY AUTOMATIC STORAGE USING STOGROUP IBMSTOGROUP" DB20000I The SQL command completed successfully. ==> db2 "ALTER TABLESPACE tbsp2 MANAGED BY AUTOMATIC STORAGE USING STOGROUP IBMSTOGROUP" DB20000I The SQL command completed successfully.
