Migrating data from a Netezza backup

Prerequisites

• To migrate data from a Netezza backup, you use the db_migrate frombackup subcommand. The frombackup option must be specified as the first parameter, followed by any other required parameters as presented below.

• You can run the command locally, from your Db2 Warehouse or Integrated Analytics System appliance, or you can run it from a remote machine.
• You must either copy or mount the backup files from Netezza to the machine from which you run the db_migrate frombackup command.

Limitations

• db_migrate frombackup supports only the migration of tables and their data. Migration of VIEW, SYNONYMS and other backup objects is not available.
• Arguments -on-conflict and -on-error have been removed.

• Incremental restore can be applied only if preceeding restore operations were performed using this version of the tool. Incremental restore depends on the presence of additional columns in each table (used to uniquely identify rows) and additional small backup configuration tables created in a user-specified schema. Additional columns and tables can be dropped once the restore process is finished, but in this case, no further incremental backups to this data is possible. The process needs to be started over, replacing existing data.
• Some combinations of create, drop, and/or rename tables with names conflicting between backup increments may result in corrupted table structure or data. This only affects complicated scenarios where tables change the names multiple times and result in the same name for different objects in different incremental backups. This problem exists because processing each table is performed in parallel without synchronizing the restored level of different tables. Although there is an unlikely chance this situation will occur, restoring consecutive backups instead of just specifying the latest is advised.
• Some ALTER TABLE statements which are possible in Netezza are not supported by Db2 with column-oriented tables.
• As shown in the previous release of utility, the current relase is not capable of translating data which does not fit Db2. Columns of types which do not have their Db2 counterparts (like TIMETZ or INTERVAL) cannot be migrated and neither can the tables they belong to.
• Data from a range broader than Db2 can handle will result in specific table migration failure, which can be overriden by using the --max-errors parameter. This parameter specifies the maximum number of erroneous rows in a single data file (single stream of specific table within single backup increment) which does not cause a data load error. Use 0 to specify the maximum supported number (in practice, to allow all rows fail to load). Using this parameter when set to anything other than 1 (the default value) should be followed by an examination of the external table status and .bad files in the restore log folder to determine which data has been skipped within the process.
• Restore configuration is specific to the context it has been created for (first restore using specified configuration schema). Once first restore of the given backup tree has been performed, the following incremental patches should be applied using the same configuration schema to allow the utility to access restore metadata. Contrarily, the metadata schema should not be reused for restoring different backup sets or restoring backup to other locations. For example, to restore Netezza database to the production schema and two test schemas. Inability to follow this best practice may result in data loss or corruption.
• There is no upgrade path from differential/cumulative to cumulative backup. Cumulative backup can only be applied on a restored full backup. Once differential (or another cumulative) backup is restored, applying more recent cumulative backups will require you to restore the full backup the cumulative backup is based on. The utility finds and applies the required restored path on its own.
• Utility does not lock access to the data nor traces or detects any changes the database has introduced to the database between restore sessions.

Usage

db_migrate frombackup restore
       [-h] --src SRC [--translation-pattern SCHEMA_TRANSLATOR]
       [--tables TABLE [TABLE ...]] --host HOST [--port PORT]
       [--Security {ssl,SSL}] [--SSLServerCertificate SSL_SERVER_CERTIFICATE]
       [--database DATABASE] --user USER [--password PASSWORD] --config-schema
       CONFIG_SCHEMA [--numloads NUMLOADS] [--max-errors MAX_ERRORS]
       [--logdir LOGDIR]
       [--loglevel {info,trace,trace_all,critical,error,debug,warning}]

When specifying delimited object names, enclose them in single quotation marks and then in double quotation marks: '"name"'.

Command options

-h, --help

Displays help message and exits.

--password <password>

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

--port <port>

Number of port on which Db2 service is running.

–-database <database>, -db <database>

Specifies the name of the database on which data is to be restored. Note that BLUDB is the only database available in Db2 Warehouse/IIAS.

--logdir <logdir>

Path for temporary, working, and log files. It is created automatically by default.

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

Logging level.

--max-errors <max_errors>

Specifies the number or rows rejected before loading single external table file fails. The default is 1, which means that loading an external table file fails on the first encountered error. If you set the argument to 0, invalid rows will not cause an ET load fail.

--numloads <numloads>

Specifies the number of parallel loads.

--Security {ssl,SSL}, --security {ssl,SSL}

Specifies security options. Only SSL is available.

--SSLServerCertificate <ssl_server_certificate>, --sslservercertificate <ssl_server_certificate>

Specifies the path to server CA certificate file.

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

Specifies tables to be processed. Case sensitive names of Netezza objects. Object name must contain schema.

--translation-pattern <schema_translator>

This parameter is used when you want to define your own pattern for structuring data on the target BLUDB database when migrating from a multiple database environment.

To map the source database-schema-table structure in the target database, three placeholders can be used in the pattern:

{d}

A placeholder for original database name

{s}

A placeholder for original schema name

{t}

A placeholder for original table name

While {d} and {s} are optional, {t} is required in the pattern. The script provides hints when improper pattern is being set.

Together with placeholders, the following symbols can be used in a pattern:

• A-Z
• a-z
• 0-9
• _ (underscore)
• . (period) used only to separate destination schema from destination table name sections

Examples of patterns:

• {d}_{s}.{t}
• TEST_{d}_{s}.{t}
• {d}_{s}.{t}test1
• {d}_{s}_{t}
• {d}.{t}
• {t}

Following is an example of migrating data from a Netezza backup to IIAS using different patterns.

Source Netezza databases:

database1
	schema1
		table1
		table2
database2
	schema1
		table1
		table2

IIAS BLUDB database structure after restoring from backup with -translation-pattern {d}_{s}.{t}:

	database1_schema1
		table1
		table2
	database2_schema1
		table1
		table2

IIAS BLUDB database structure after restoring from backup with -translation-pattern TEST_{d}_{s}.{t}:

	TEST_database1_schema1
		table1
		table2
	TEST_database2_schema1
		table1
		table2

--user <user>

Specifies the username for connecting to the database.