Migrating data from a Netezza backup

You can use the db_migrate frombackup command to restore data from a Netezza backup to Db2® Warehouse. The full backups from PureData® System for Analytics can be used to restore the data on your Db2 Warehouse database. Note that Netezza systems support multiple databases, while in Db2 Warehouse and Integrated Analytics System there is only one database BLUDB. When you migrate multiple Netezza databases and schemas into this single database, you might want to define how to name the objects in the target database so that the data structure can be maintained. This is called translation pattern.

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

Netezza supports full, cumulative and differential backup types. However, to perform a restore with db_migrate frombackup, your Netezza backup must be a full backup.
db_migrate frombackup supports only the migration of tables and their data. Migration of VIEW, SYNONYMS and other backup objects is not available.

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

The following table lists all required arguments.

Table 1. Required arguments for db_migrate frombackup
Option	Description
--host <host>	Name of the target host to migrate the data to. It is either `localhost`, or, if you are using a remote machine to run the command, the target Db2 Warehouse/IIAS host name.
--user <user>	Name of the user connecting to the target database.
--src <src>	Local path of the source directory where the Netezza backup is saved. It must be a path to the FULL directory, containing both data and md folders, for example: /home/user/Netezza/hostname/TESTDB/20190117232613/1/FULL

Following is a list of optional arguments for db_migrate frombackup:

--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.

--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

-on-conflict {replace, skip, exit, append}

Specifies an action to take when a table with the same name already exists.

-on-error {skip, cont, exit}

Specifies an action to take when a table operation fails.

skip: Doesn't perform further action on the erroneous table.
cont: Continues and performs all other tasks on the table.