If you have upgraded you Netezza server to higher capacity box then want to migrate data from one netezza server to other, you can use the IBM provided Netezza nz_migrate utility. You can either mention the tables to be migrated or you can migrate all tables available in source database. Netezza nz_migrate script is one of the best Netezza utility that provides lot of option to use while migrating data from one server to other.
nz_migrate optionally creates destination database if it is not already present. All the tables mentioned in the tablelist parameter are migrated to the target database. You can also set the option to create target tables or truncate target tables before migrating the data. NZ_Migrate utility is faster then nzload utility as it does the migration by running many parallel threads.
Netezza nz_migrate Command Syntax
Below is the Netezza nz_migrate command syntax.
nz_migrate -shost <src_host> -thost <tgt_host> -sdb <src_db> -tdb <tgt_db> -suser <src_user> -spassword <src_password> -tuser <tgt_user> -tpassword <tgt_password> -t <table1, table2, ...> -cksum fast -genStats Full -TruncateTargetTable YES >> $log
Netezza nz_migrate Command Options
Below are the list of Netezza nz_migrate basic and advance options.
| Option | Description |
| -d <dbname> / -db <dbname> | Specify database name to connect to [NZ_DATABASE]. |
| -schema <schemaname> | Specify schema name to connect to [NZ_SCHEMA] |
| -u <username> | Specify database username [NZ_USER] |
| -w <password> / -pw <password> | Specify the database user password [NZ_PASSWORD] |
| -host <host> | Specify database server host [NZ_HOST] |
| -port <port> | Specify database server port [NZ_PORT] |
| -rev | Show version information and exit |
| -sdb <dbname> | Source database name |
| -tdb <dbname> | Target database name |
| -shost <name/IP> | Source host |
| -thost <name/IP> | Target host |
| -suser <user> | Source user [SUSER] |
| -tuser <user> | Target user [TUSER] |
| -spassword <password> | Source password [SPASSWORD] |
| -tpassword <password> | Target password [TPASSWORD] |
| -sschema <schema> | Source schema [SSCHEMA] |
| -tschema <schema> | Target schema [TSCHEMA] |
| -t / -table /-tables <tablename> […] | The table(s) within the database to be migrated |
| -exclude <tablename> […] | This option allows you to identify specific tables that are to be EXCLUDE’d from that migration. |
| -tableSQL <SQLQueryString> | You can use this option to dynamically generate the list of tablenames to be migrated |
| not like ‘TEST%'” | This option can be specified only once. This option must be used itself (and not in conjunction with the -table or -tableFile options). |
| -sTable <tablename> | source Table |
| -tTable <tablename> | target Table |
| -format <ascii|binary> | The data transfer format to be used. The default is ascii. |
| -threads <n> | Each table will be processed by using ‘<n>’ threads (parallel unload/load streams) to make optimal use of the SMP host and the network bandwidth. By default, 1 for small tables and 4 for large tables |
| -cksum <yes|no|fast|slow> [column …] | Should a cksum be run against both tables to confirm that the source and target tables (appear to) contain the same data? The default is “Yes”.
Yes | Count — Perform a simple “select COUNT(*) from <table>” |
| -TruncateTargetTable <no|yes> | Before loading any data into the target table, TRUNCATE it to ensure it is empty. |
| -CreateTargetTable <no|yes> | Create table in target database if it is not available. By default, it is set to NO. |
| -CreateTargetDatabase <no|yes> | Create target database if it is not available. By default, it is set to NO. |
| -CreateTargetUDX <no|yes> | Create UDX in target database during migration. By default, it is set to NO. |
| -to <email@recipient> | Email address to be sent a copy of the output from this script |
| -errors_to <email@recipient> | Email address to be sent a copy of the output from this script in case of errors. |
| -mail_program <script_or_executable> | Program used to send mail. |
| -genStats <none|full|express|basic> | After each individual table migration completes, this script can automatically issue a GENERATE STATISTICS command against the target table.
Options are |
| -SourceObjectType <table|any> | Specify this option only to migrate required objects such as tables, views etc. |
| -SourceWhereClause <clause> | Migrate subset of rows from tables using WHERE clause filter. |
| -TargetOrderByClause <clause> | This option allows the data to be sorted before inserting to target tables. This option helps to improve performance as zone maps are created. |
| -TargetDistributeOnClause <clause> | Mention distribution clause on target table. |
| -viaSynonym | The script will find the table that the synonym references (on the source). The table will be migrated to the target. Upon successful completion, a synonym by the same name will be (re)created (on the target) referencing the table that was just migrated to it. |
| -cloneDDL | Generate DLL associated with all objects during migration. |
| -status [<n>] | Provides periodic status updates while the data is flowing. |
| -restart <n> | Script will automatically restart the migration of a table if you specify “-TruncateTargetTable yes” or “-SourceWhereClause <clause>”. |
| -timeout [<n>] | Automatically terminate the hung thread(s) when it notices that 0 bytes of data have been transferred between the two systems over the specified length of time (the -timeout value). |
| -sbin <source bin/executables directory> | Source bin directory. |
| -tbin <target bin/executables directory> | Source bin directory. |
Netezza nz_migrate Examples
Below example shows the data migration from one NPS to other using nz_migrate command.
./nz_migrate -shost 192.168.41.88 -thost 172.25.41.81 -sdb BENCHQADS -tdb BENCHQADS -suser vithal -spassword vithal -tuser vithal -tpassword vithal -cksum fast -genStats Full -threads 5 -CreateTargetTable yes -CreateTargetDatabase yes
Hi,
Can we run nz_migrate while sessions are running.
Thanks
Hi Zack,
That is not a good idea. You should migrate when all of your data processing is done.
Thanks