I created my table in the wrong schema! How to change the table schema in Db2 LUW?

If you already have some experience with Db2 LUW (or just any other relational database system), you must have made lots of silly mistakes so far. If you didn’t, you’re not human (just FYI). One of the most common is creating a table in an incorrect schema, usually the default one. If you just mess around with Db2, you might not care about schemas, but if you create a side-project or worse, you work as a DBA, schemas matter. If my employer only new what schema mistakes have I done… :D

When you create a table, it’s always put in some schema. If you don’t specify one, the table is created in the current schema. By default, the current schema is the schema named after your instance, so if you know nothing about schemas (and didn’t execute SET CURRENT SCHEMA…), then your current schema is the same as your instance, e.g. db2inst1. So, the following SQL statement:

CREATE TABLE TAB_1 (COL_1 INT);

will result in creating DB2INST1.TAB_1 (table TAB_1 in schema DB2INST1). Typing schema name before table name (SCHEMA.TABLE) is a good habit — it will save you a lot of headaches.

Here is our case study for today:

SET CURRENT SCHEMA WRONG_SCHEMA;
CREATE TABLE MY_TABLE (COL_1 CHAR(8), COL_2 INT);

I created a table in WRONG_SCHEMA and I want to move it to CORRECT_SCHEMA. There is no command to change table’s schema. So, how do I do it? There are two ways:

• simply recreate the table with its data in the correct schema
• or use db2look and db2move tools (more complicated, but still easy)

Recreate the table with its data in the correct schema

Here is how to move the table with its content between schemas (based on my example):

CREATE TABLE CORRECT_SCHEMA.MY_TABLE LIKE WRONG_SCHEMA.MY_TABLE;
INSERT INTO CORRECT_SCHEMA (SELECT * FROM WRONG_SCHEMA.MY_TABLE);
DROP TABLE WRONG_SCHEMA.MY_TABLE;

1. At first, create a new table in the correct schema (table name can be the same if it’s in different schema) with the same structure (columns) as the original one. To do so, use the LIKE keyword.
2. Then, insert in the new table all the content of the original table.
3. The last step is to drop (delete) the original table (make sure your new table has been properly recreated and contains all the data!).

That’s it!

But what if you want to change the name of a schema that consists of dozens (or hundreds) of tables? Is there a way to just change schema names without recreating each table in another schema one-by-one? Yes, there is, but it’s a bit more complicated…

Use db2look and db2move tools

I am not going to describe in detail what kind of tools db2look and db2move are, so if you want to fully understand what’s going on here, please take a look at the following IBM documentation sites:

• Mimicking databases using db2look
• db2move — Database movement tool command

db2look is used to export the database structure and db2move is used to import/export the content of the database (table records). The whole process of changing schema using these tools consists of 7 steps:

1. Export database structure
2. Change database structure
3. Export data
4. Change data description
5. Drop original tables
6. Recreate database structure
7. Import data

Export database structure

1. The first step is to export the whole database in a form of DDL file (DDL stands for Data Definition Language). This file (except for some comments) contains a list of SQL DDL statements to recreate the database (without data — only the structure, tables, columns… no rows).

db2look -d MY_DB -e -o db2look_output.ddl

• MY_DB is the name of my database
• -e means extract DDL
• -o followed by a filename is the output file with the DDL

Change database sctructure (schema names)

2. Then, open the output file and change the schema name. Hint for those using the vim editor:

:1,$s/<SEARCH>/<REPLACE>/g

Export data

3. The third step is to export the records using db2move:

db2move MY_DB export

Now, you should see lots of new files in the current directory. One of them is db2move.lst, which needs to be edited.

Change data description (schema names)

4. So, change schema name(s) in the db2move.lst file. Here is an example of mine. I have 2 user tables, which schemas are changed to CORRECT_SCHEMA:

Drop original tables

5. Now it’s time to drop all the user tables from the database (tables in schema SYSTOOLS are not user tables!)

Recreate database structure

6. After the tables with incorrect schema are dropped, recreate them (with corrected schema names) using the DDL file that you generated (and changed) using db2look:

db2 -tvf db2look_output.ddl

Import data

7. The last step is to import the data with schema names using db2move:

db2move MY_DB import

DONE!

As you see, although there is no explicit command for changing schema of a table, you can still easily do it.

With these new guns in your artillery you‘ll never freak out again, when you find out some tables are in an incorrect schema. ;)