Copying data by using the db_migrate command

Use the db_migrate command to copy IBM PureData® System for Analytics (Netezza®) databases or tables to Db2® warehouse databases.

Before you begin

Make sure that you have the information for the source Netezza system such as its host name or IP address, the names of the databases or tables that you want to copy to a Db2 warehouse database, and the information for the target Db2 environment including connection names and target table names. Use the db_migrate -h help option to read more about the available options for managing and configuring the data transfer.

Ensure that the db_migrate command is available. This command is part of the Db2 support tools. For information about how to obtain the support tools, see the Db2 support tools overview.

Ensure that the system from which you are running the command has network access to both the source Netezza system and the target Db2 environment.

If you want to run the db_migrate command that is in the Db2 Warehouse client container, instead of the db_migrate command that is in the Db2 Warehouse image container, see Deploying the IBM Db2 Warehouse client container.

If you use the db_migrate command from the Db2 warehouse client container, you must catalog the target database on the same host on which you installed the toolkit. Use one of the following methods:

By specifying the -e REMOTE_DB parameter when you issue the docker run command during client container deployment.
By using the db_catalog command after deployment.

Procedure

Copy data from the source Netezza system to a Db2 warehouse by running the db_migrate command. The basic command syntax is as follows:

db_migrate -shost NPS_host -suser NPS_user
-spassword password -sdb NZDB -tuser dashUser 
-tpassword dashPword -tdb BLUDB -t tabName 
-createtargettable yes -truncatetargettable yes

The way in which you run the command depends on where you obtained the Db2 support tools. For information about how to run the command, see the Db2 support tools overview.

After the copy process is complete, it displays messages about the success or failure of the operation. You can review log files in the Log Directory shown in the output to learn more about the cause of migration failures. Sample output follows:


=======================================================================================
db_migrate started on 2016-05-13 11:38:12
Migrating NPS_host:NZDB --> localhost:BLUDB

Source Full Schema Enabled: FALSE
Source System Lettercase  : UPPERCASE

Data Format   : ascii
Log Directory : /tmp/db_migrate.20160513_113754.18002
Log File      : /tmp/db_migrate.20160513_113754.18002/db_migrate.output

Initiated From: CLIENT myclient
Top Level PID : 18002

SOURCE Version: NPS 7.0       Unloaded Via: exttab    # of Dataslices: 3
TARGET Version: v11.1.0.0       Loaded Via: load      # of Dataslices: 1
Script version: 1.0

==================================================================================
Migrate table "ADMIN"."TABNAME" --> "<SCHEMA>"."TABNAME"

.....processing table 1 of 1
.....creating the target table
.....converting DDL
.....truncating the target table
.....migration process                              started at  2016-05-13 11:38:39
.....estimated # of records                                     3
.....load starting               ( thread 1 of 1 )
.....waiting on load             ( thread 1 of 1 )
.....unloading data              ( thread 1 of 1 )
.....data flowing.....
.....load finished               ( thread 1 of 1 )              elapsed seconds: 4
.....load successful             ( thread 1 of 1 )
.....unload results              ( thread 1 of 1 )              Rows Returned : 3
.....unload finished             ( thread 1 of 1 )              elapsed seconds: 4
.....migration process                              ended at    2016-05-13 11:38:44
.....data flow finished
.....actual # of records unloaded                               3
.....
.....migration completed                                        TOTAL seconds: 5
.....
.....data verification                              started at  2016-05-13 11:38:44
.....data verification                              ended at    2016-05-13 11:38:48
.....confirmed: source/target table row count is [3]/[3]
.....
.....data verification completed                    TOTAL seconds: 4

==================================================================================
db_migrate finished on 2016-05-13 11:38:48

Migrating NPS_host:NZDB --> localhost:BLUDB

Data Format   : ascii
Log Directory : /tmp/db_migrate.20160513_113754.18002
Log File      : /tmp/db_migrate.20160513_113754.18002/db_migrate.output

Total # of tables processed: 1
Total # of records unloaded: 3

# of seconds to unload/load the records : 5
# of seconds to compare the tables: 4
==================================================================================

Don't forget to generate statistics on your tables!
The script completed successfully with no errors.

Results

After the migration is complete, your Netezza database or table is available in a Db2 warehouse database. You can use Db2 support tools and Db2 warehouse web console to view or manage the content using the Db2 features. You can also use the dbsql command to run SQL statements against the data.