db_migrate command options

Source

The server/database containing the data that is to be copied. The source database is specified by using -sHost, -sPort and -sDB parameters.

Target

The server/database to receive the data. Target database is cataloged on the system on which db_migrate is executed. The target database is defined by the -tDB parameter, which specifies the alias of the target database as cataloged on the host where db_migrate is started. Parameters -tHost and -tPort are not supported.

With default options, the target database and target tables must already exist. Optionally, you can use the following options for db_migrate to automatically create all objects in the target database, or just the target tables:

-CreateTargetObjects   YES
-CreateTargetTable     YES

To migrate tables the user requires permissions such as these:

• On the source system:

  GRANT LIST ON <DATABASE|dbname> TO <user/group>;

  The user must have access to the source database.

  GRANT SELECT ON <TABLE|tablename> TO <user/group>;

  The user must have access to the tables in the source database, and their data.

  GRANT CREATE EXTERNAL TABLE TO <user/group>;

  The user must be able to create external tables, into which the data will be unloaded.

• On the target system:

  GRANT DBADM WITH DATAACCESS ON DATABASE TO <user/group>;

  The user must have access to the target database.

db_migrate -sdb <dbname> -tdb <dbname> -shost <name> 
                     -suser <user> -tuser <user> -spassword <password>
                     -tpassword <password> [optional args]

A minimalist command line might be something like this:

db_migrate -sdb proddb -tdb testdb -shost nzbox  
                     -suser admin -tuser dbuser 
                     -spassword password -tpassword password

The required arguments can be passed by exporting the following environment variables:

• -shost > NZ_HOST
• -sdb > NZ_DATABASE
• -suser > NZ_USER
• -spassword > DBMIG_SOURCE_PASSWORD or NZ_PASSWORD
• -tdb > DB_DATABASE
• -tuser > DB_USER
• -tpassword > DBMIG_TARGET_PASSWORD or DB_PASSWORD

The tool prompts for lacking values in interactive mode.

The script supports the following generic options:

The following table lists all required arguments.

Following is a list of optional arguments for db_migrate:

-cksum <yes|count|fast|no|slow|full|columns <columnslists> >

Defines if the checksum is run against source and target tables after migration, to confirm that they contain the same data. Default value is yes. The following choices are available:

• yes | count | fast - Perform a simple select COUNT(*) from <table>
• slow | full - Perform checksum based on all columns from <table>
• columns <columnList> - Perform checksum based on columns listed in <columnList>
• no - no checksum is counted

The checksum runs after migration copies the table data to the target.

When using FULL or COLUMNS option, the SQL Extensions Toolkit must be installed and enabled for source database.

Checksum is based on full content of fields with the following Netezza data types: BOOLEAN, BYTEINT, SMALLINT, INTEGER, BIGINT, NUMERIC, CHARACTER (both VARYING and NATIONAL), DATE.

For TIMESTAMP and TIME comparison precision is on the level of seconds. The difference in fractional part (microseconds) is ignored by checksum mechanisms. As a result, TIME data type which is truncated during the migration to seconds precision, is not reported by checksum.

For floating point types (FLOAT/REAL/DOUBLE) checksum is not based on actual values but only distinguishes null/not null.

Checksum can also be calculated on the existing tables using the standalone db_checksum tool. The tool might be helpful when the full checksum calculated by db_migrate reports differences. You can then calculate checksum for a particular column or columns, and specify the where condition. You can use db_checksum -h to find out all the options.

-cloneDDL

If the -cloneDDL switch is included, then this script generates all of the DDL associated with the object to attempt to faithfully clone it. This would include the following statements:

COMMENT ON ...
GRANT ...
ALTER ... OWNER TO ...

Note:

• This option is always enabled when -CreateTargetObjects is specified.
• This script may issue CREATE TABLE statements (-CreateTargetTable YES)
• This script may issue CREATE SYNONYM statements (-viaSynonym)

--config-schema <config_schema>

Displays the schema, where restore configuration is stored. Use the same schema to restore the following increments. Use different schemas if the restore operations are not related.

-CreateTargetObjects <no|yes>

Defines if the script is to attempt to automatically create ALL of its tables and other objects on the target. The default value is NO. If YES, then this script extracts all the DDL from source system and then runs against the target system to create the objects. Many non-fatal errors might occur. For example, a CREATE VIEW statement might reference objects in another database that does not exist on the target system and thus, the DDL statement would fail. This script prints a warning message if errors occur. The output from these operations can be very lengthy, so the details are logged to a disk file for later review. To migrate just the DDL (no data), you can specify -noData option with this option.

-CreateTargetTable <no|yes>

If the table does not already exist in the target database, do you want this script to attempt to automatically create it for you? The default value is NO. If YES, the table is created, and its definition will include any primary key and unique column constraints that may have been defined. If you want to drop existing table and recreate the table, use option -RecreateTargetTable.

--database DATABASE, -db DATABASE

Displays the name of the database on which the data should be restored.

-errors_to <email@recipient>

Email address to be sent a copy of the output from this script, but only if an error is encountered.

-evaluate

Pre-migration evaluation. An HTML report is generated for migration assessment before actual migration. Option "-CreateTargetObjects YES" is required for evaluating the migration of all database objects. To generate a report for only one table, use this option combined with -t option and "-CreateTargetTable YES -CreateTargetObject NO".

-constraints <drop>

Defines how to handle table constraints. Currently only one argument is supported:

• drop - do not create constraints

This option is disabled by default.

-exclude <tablename> [...]

Exclude the specified tables from the migration. This option is useful when you want to migrate most of the tables from the source database without listing them all, you only list the ones you do not want to migrate.

-format <ascii|mixed|binary>

The data transfer format to be used. The default is ascii.

• ascii - Text format used for transfer. Typically migration takes longer time.
• binary - Transfer from compressed binary internal format. Can result in higher throughput and better performance.
• mixed -
  Note: This option is deprecated. Consider using binary format.
  The transfer in compressed NPS internal format between the source and the host where db_migrate is started. Decompression (in fly) takes place on the host where db_migrate is started. If the tool is started on intermediate (not target) host, then the transfer between this host and the target is in uncompressed text format. The mixed mode can improve performance if network between the source host and the host where db_migrate is started is bottleneck.

-genStats <none|full>

After each table is migrated to the target database, this script can automatically issue a RUNSTATS command against the target table. It is invoked as a background job so that it does not block the rest of the migration process. Options are:

• none - Do nothing (the default).
• full - Generate statistics.

-h, --help

Use to prompt a help message and exit.

--host <host>

Displays the target DB2WH hostname.

--logdir <logdir>, -logdir <logdir>

Displays the path for temporary, working, and log files. These files are created by default.

--loglevel {info,trace,trace_all,critical,error,debug,warning}

Displays the logging level.

-mail_program <script_or_executable>

When sending email, this script invokes /bin/mail by default. This option allows you to use a different mailer program (or a shell script) without making any changes to the db_migrate script itself.

--max-errors <max_errors>

Displays the number of rows rejected before loading single external table file fails. The default is 1. The max is 0.

-noData

Performs all the steps as requested on the command line except for moving the actual data. This option can be used to:

• Create the target database objects
• Truncate the target tables to clean them out
• Test operations in general without actually moving data

--numloads <numloads>

Displays the number of parallel loads.

--password <password>

If this is not entered as an argument, the user will be prompted.

--port <port>

Displays the port on which the Db2 service is running.

-RecreateTargetTable <no|yes>

If the table already exists in the target database, the script can automatically drop and recreate it. The default value is NO. If YES, the existing table is dropped and recreated.

-restart <n>

Sometimes a table migration could fail because of a momentary network problem between the two hosts (such as Communication link failure) or a momentary hardware or software issue. This script automatically restarts the migration of a table under the following conditions:

You specified the option -TruncateTargetTable yes

OR

You specified the option -SourceWhereClause <clause>.

During the failed attempt to migrate the table, one or more of the threads may have successfully completed, which might result in some (but not all) data existing in the target table. This leaves the target table in an inconsistent state. Before the migration process is restarted, this script attempts to either TRUNCATE the target table (which should be instantaneous), or issue a DELETE statement against the target table based on the -SourceWhereClause <clause> (which could take a long time to run). By default, the number of restart attempts is set to 1. You can set the number of restart attempts to a value from 0 (no restart) to 10. A restart does not guarantee success, it only automates the attempt. A restart will only take place if the problem occurred during migration itself, not during the early setup or during the final checksum phases. If the subsequent migration of the table is successful then the script treats it as a success, with appropriate log messages included in the output.

--security {ssl,SSL}, –security {ssl,SSL}

Displays security options. Only SSL is available.

-skip_numeric_check

Allows to skip the initial check for numeric values in a source table. The maximum precision of NUMERIC/DECIMAL datatype is higher (38) on performing checks for potential migration problems with numeric columns.

-SourceObjectType <table|any>

With this option, db_migrate copies data from a source table into a target table, but the source can be a view or an external table or a datafile. In this case, you must create the target table manually so that db_migrate can copy the data from the external table or view into this table. To use this option you must:

1. Specify the source object names in the -table or -sTable option.
2. Set the option -SourceObjectType to any.

Otherwise, it is required that the source is truly a table.

-SourceWhereClause <clause>

This option allows you to specify a WHERE clause to restrict the data selected for migration and thus migrate a subset of the table. Enclose the clause in double quotation marks. Examples:

                -SourceWhereClause  "customer_key = 2"
                -SourceWhereClause  "customer_key in (1,3,5)"
                -SourceWhereClause  "region_name = 'AMERICA' or region_key = 0"
                -SourceWhereClause  "order_date between '1998-01-01' and '1998-12-31'"
                -SourceWhereClause  "customer_num in (select id from sales..accounts)"

Note that the specified clause is applied to all of the tables being migrated, so you might want to migrate only the one table that would match the WHERE columns specified in this clause. The clause is applied to the source table. Its use will be logged in the output of this script, as well as in the pg.log file on the source machine. After migration, when the tables are compared in the checksum step, the checksum query also uses the clause on both the source and target tables to evaluate only the data that was copied.

-sPort <#>

Source port number. Generally, the option is never specified, and the default value of 5480 is used. This option allows you to override the default if the source database uses a different port.

-src <src>

Displays the full path of the backup source directory. For example:

/home/us
er/Netezza/hostname/TESTDB/20190117232613/1/FULL

-sSchema <schema>

Source schema. This only applies if the database supports and is using schemas.

--SSLServerCertificate <ssl_server_certificate>, --sskservercertificate <ssl_server_certificate>

Displays the path to the server or CA certificate file.

-sTable <tablename>

Source table name.

-tTable <tablename>

These options allow you to migrate a source table into a target table with a different name. You must always specify both the source table name and the target table name. When using this option you can copy only one table at a time. The -CreateTargetTable and -TruncateTargetTable options can be used. You cannot use the -t <tablename> option in the same command.

-status [<n>]

Provides periodic status updates while the data is flowing. By default, the status is updated every 60 seconds. You can specify an optional value from 1 second to 3600 seconds. Sample output:

.....data flowing.....
.....status Total:       25,165,824    Average:    5,033,164    elapsed seconds: 5
.....status Total:       67,108,864    Average:    8,388,608    elapsed seconds: 10
.....status Total:      117,440,512    Average:   10,066,329    elapsed seconds: 15

The first column is the total number of bytes transferred, the second column is the average number of bytes transferred per second since the last status update. This option also provides additional summary statistics for each table, and for the migration as a whole:

.....# of bytes xfer'ed                                         173,001,038
 .....xfer rate (bytes per second)                               7,863,683

--tables <table> [<table> ...]

Displays the tables to be processed and case sensitive names of Netezza objects. The object name must contain a schema.

-tableFile <filename>

You can specify a file with a list of table names to migrate. The names can be separated by newlines or tab characters. This switch can be used with the -table option. This switch can be specified multiple times.

-TargetDistributeOnClause <clause>

This option allows you to control and override source table distribution key when creating a target table. By default, the target table distribution key matches that of the source table. Wrap the clause in double quotes so it is passed into the script correctly. To use this option, it is required that the target table does not exist and -CreateTargetTable YES option is specified. Because this clause is applied to all tables being migrated, it is advised to migrate a single table at a time, since the clause typically contains column names that are specific to that table. Examples:

-TargetDistributeOnClause  "(customer_id)"
-TargetDistributeOnClause  "(customer_id, account_id)"

-TargetOrderByClause <clause>

This option allows the data to be sorted before it is actually inserted into the target table. It is up to you to decide what column(s) the data should be sorted on, and to pass those column name(s) into this script. Wrap the clause in double quotes so it is passed into the script correctly. Because this clause is applied to all tables being migrated, it is advised to migrate a single table at a time, since the clause typically contains column names that are specific to that table. It is also recommended that you include the -threads 1 option for the data to be sorted as a single dataset. Using multiple threads would cause each thread to sort its data separately. Examples:

 -TargetOrderByClause  "purchase_date"
-TargetOrderByClause  "purchase_date asc"
-TargetOrderByClause  "purchase_date asc, store_id asc"

--translation-pattern <schema_translator>

This options allows you to name the translation scheme, providing the string containing {d}, {s} and {t} placeholders here.

-LoadWarnings <n>

Specifies the number of errors per thread at which the tool stops processing. The default value is 1. This default causes the system to commit a load only if it contains no errors. To disable error limit, set the value to 0.

-threads <n>

Each table is processed by using <n> threads (parallel unload/load streams) to make optimal use of the SMP host and the network bandwidth. By default, 1 thread is used for small tables ( < 1M rows ) and 4 threads are used for larger tables. You can override the default number of threads with this switch. The supported maximum is 31 threads.

-timeout [<n>]

Sometimes a table migration hangs up. For example, all but one of the threads finishes. Perhaps the source sent a packet of data but the target never received it. Neither one received an error but neither one will continue as they are each waiting for the other to do something. This option tries to monitor for such situations, and will 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). Specifying this option automatically enables the '-status' option as well. By default, this option is disabled. Include this switch on the command line to enable it. The default timeout value is set to 600 seconds (10 minutes). You can specify an optional value from 1 second to 7200 seconds. If the timeout is reached, the migration of the current table will automatically be terminated. You can also specify that the migration of that table is automatically restarted as well. See the -restart option described above for more details.

-to <email@recipient>

The script sends a copy of the output to the specified email address.

-TruncateTargetTable <no|yes>

Before loading any data into the target table, TRUNCATE the target table to ensure that it is empty. The default value is NO.

Important: Be very careful to specify your source and target hosts correctly. If you enable this option but you specify the wrong target host, you could accidentally truncate a source table and lose data.

-tschema <schema>

Target schema. If specified, source objects are copied to target system using this schema.

Note: The parameter is always treated as upper-case string, even if provided in mixed case and quotes.

-t|-table|-tables <tablename> [...]

Specifies a list of one or more tables in the source database to migrate. If you do not specify any tables, then all of the tables in the source database are migrated. This switch can be specified multiple times.

--user <user>

Displays the username for connecting to the database.

-viaSynonym

If you pass the script a synonym name (rather than a table name) and include this switch, the script migrates the table that is referenced by the synonym on the source. After successfully copying the source table, the script also creates the synonym in the target database to refer to the migrated table. For example, the synonym TODAYS_DATA might reference the table MARCH_12. The script copies the MARCH_12 table, and if successful, the script also creates the synonym TODAYS_DATA on the target to point to the table.

-loader <exttab>

With this parameter you can define the way in which data is loaded into Db2® Warehouse:

• With the default exttab option, INSERT from external tables is used, which is the feature corresponding to external tables known from Netezza.

-logVerbose

If specified, all logs produced by the script are saved. If not specified, only the main log is saved unless there is an issue.

-multiByteChars <yes|no>

This option should be used if any VARCHAR field holds characters with ASCII code > 127. Such characters have Latin9 encoding in Netezza, and UTF-8 in Db2 Warehouse, so after the migration they become multi-byte. The option can be used only with -loader exttab. If set, VARCHAR fields in Netezza become NVARCHAR (string units CODEUNITS32) in Db2 Warehouse. If not set, each character in VARCHAR field, which is 1-byte in Netezza is migrated to VARCHAR field with string units OCTETS (which means single-byte). This behavior is correct if data in VARCHAR fields contains only characters with ASCII code <= 127. Otherwise string functions do not work as expected and data might not fit into the target field, as its length is specified in bytes, not characters.

-prefixSchema < no | yes | withPrefix <prefixName> >

Option dedicated for full-schema enabled source databases. With default option, -prefixSchema no, each schema is migrated to the same schema name on the target system. With -prefixSchema parameter, a prefix is added automatically to target schema names, which allows to keep schema name unique in a case where multiple source databases have the same schemas defined. Possible values:

• no - No action taken (default)
• yes - Database name is used as schema prefix, target schema name is <sourceDB>_<sourceSchema>
• withPrefix <prefixName> - prefixName is used as a schema prefix, target schema name is <prefixName>_<sourceSchema>

Note:

• This option is mutually exclusive with -tSchema.
• This 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.

-ParallelTargetNodes < no | yes >

If the target machine is a multinode system, the script uses all the available nodes to migrate the data. This doesn't work with MIXED format. The default value -ParallelTargetNodes no. Passwordless ssh connection needs to be configured for all IAS nodes (inside a docker container) for users other than bluadmmin in order to use the parallel migration option.

Following is an example of migrating an entire database:

db_migrate -sDB proddb -tDB bludb -sHost nzbox 
             -sUser admin -tUser dbuser 
             -sPassword password -tPassword password
             -CreateTargetObjects yes -TruncateTargetTable yes

Status, log and timing information for each table that is migrated is sent to standard out, including details about any problems that might need to be addressed. Exit statuses:

• 0 = success
• non-0 = errors were encountered

Migrate table "ADMIN"."ZZSHIPPERS_USING_FAKE_SIC" --> "SANDBOX"."ZZSHIPPERS_USING_FAKE_SIC"

.....processing table 5 of 5
.....truncating the target table
.....migration process                              started at  2016-02-01 10:44:37
.....estimated # of records                                     6
.....load starting               ( thread 1 of 1 )
.....waiting on load             ( thread 1 of 1 )
.....unloading data              ( thread 1 of 1 )
.....data flowing.....
.....unload results              ( thread 1 of 1 )              Rows Returned : 6
.....unload finished             ( thread 1 of 1 )              elapsed seconds: 14
.....load finished               ( thread 1 of 1 )              elapsed seconds: 122
.....load successful             ( thread 1 of 1 )
.....migration process                              ended at    2016-02-01 10:46:39
.....data flow finished
.....actual # of records unloaded                               6
.....
.....migration completed                                        TOTAL seconds: 122
.....
.....launching generate statistics (in the background)