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.