Migrating from a multiple schema database

If you are migrating from a system with multiple schema enabled, additional steps might be required to keep schema names unique in a case where multiple source databases have the same schemas defined.

About this task

Netezza appliance with multi-schema enabled has two levels of object grouping: database and schema, while Db2 Warehouse has only one level - schema, as there is only one database BLUDB there. Because of that, it is not possible to migrate many multi-schema enabled databases into a single Db2 Warehouse database without the change of data model.

There are two ways to migrate such databases. You can either create new, unique schema names automatically during migration, or you can migrate the schemas one by one, manually renaming some schemas.

Procedure

To automatically rename all schema names and ensure schema name uniqueness, use db_migrate with the following option: -prefixSchema < no | yes | withPrefix <prefixName> >
The option is dedicated for migrating full-schema enabled source databases. With -prefixSchema yes parameter, a prefix is added automatically to target schema names. Possible values:
- no - No action taken (default)
- yes - Database name is used as schema prefix, so that the target schema name is <sourceDB>_<sourceSchema>
- withPrefix <prefixName> - A custom prefixName is used as a schema prefix, and the target schema name is <prefixName>_<sourceSchema>
Examples:
- -prefixSchema yes sets source database name as prefix by default:
```
db_migrate -shost nzhost -suser admin -spassword password -tdb bludb -tuser bluadmin -tpassword bluadmin -createtargettable yes -nodata -sdb nzdb -prefixschema yes
```
  In this example, a table MASTER.ACCESSORIAL is migrated to a table NZDB_MASTER.ACCESSORIAL
- -prefixSchema withPrefix <prefix_name>
```
db_migrate -shost nzhost -suser admin -spassword password -tdb bludb -tuser bluadmin -tpassword bluadmin -createtargettable yes -nodata -sdb nzdb -prefixschema withprefix target_prefix
```
  In this example, a table MASTER.ACCESSORIAL is migrated to a table TARGET_PREFIX_MASTER.ACCESSORIAL
Note:
- The -prefixSchema option is always treated as upper-case string, even if provided in mixed case and quotes.
- When you use Db2® Warehouse versions earlier than 2.5, this option cannot be used to migrate a multiple-schema database with schemas having mixed case quoted names, for example CamelCaseSchema.
To manually migrate schema by schema and define schema names where required, use db_migrate with -sschema and -tschema options.

In the following example, the Netezza system has D1 database and D2 database. D1 database contains schema S1 and S2. D2 database contains schema S1 and schema S2. Note that schema names are the same in both Netezza databases, and there is only one database in target Db2 Warehouse.

The workaround is to migrate schemas one by one and use the -tschema option to specify different names for schemas with the same name on Netezza.
1. Run the following command to migrate schema S1 from Netezza database D1 to Db2 Warehouse:
```
db_migrate
-sdb D1  -shost 9.167.40.76 -suser admin -spassword ****  -sschema S1 
-tdb BLUDB -tuser db_user -tpassword ****** -tschema D1_S1 -createTargetTable yes
```
2. Run the following command to migrate schema S1 from Netezza database D2 to Db2 Warehouse:
```
db_migrate
-sdb D2  -shost 9.167.40.76 -suser admin -spassword ****  -sschema S1 
-tdb BLUDB -tuser db_user -tpassword ****** -tschema D2_S1 -createTargetTable yes
```
  Note:
  After migration, you must take care of some references to older schema names in DDL.