Netezza incremental backup migration

The db_migrate utility requires Db2® Version 11.5.3 or higher in order to work with the frombackup option.

Until now, only full Netezza backups were supported as the source of restoring data in Db2. To facilitate different migration scenarios, db_migrate introduces the ability to restore full backup and apply incremental, cumulative, and full patches over it.

Because of this change, each restore operation requires a new argument, --config-schema. --config-schema specifies the database schema where db_migrate frombackup stores restored metadata. Examples of metadata include options that have been run, and the level and structure of restored tables. When using this information after re-running, db_migrate can determine where the last restore finished for each table and continue from that point. Additionally, the stored metadata is used to check if the backup-provided table definition changed when compared with the last restore operation and if the table needs to be altered.

Usage example:

Having the following backup structure:
SOME_DB
     20200202201436
          1 / FULL / ...
          2 / DIFF / ...
          3 / DIFF / ...
          4 / DIFF / ...
          5 / DIFF / ...
Backup contains the following Netezza tables:
        MYSCHEMA.TABLE1
        MYSCHEMA.TABLE2
        MYSCHEMA.TABLE3
You can issue the following command:
   db_migrate frombackup restore \
                    --host 192.168.1.50 \
                    --user db_user \
                    --password mY_Secre7Pa55worD \
                    --security=ssl \
                    --config-schema RESTORE_001 \
                    --translation-pattern="TEST01.{s}_{t}" \
                    --src /mnt/backups/SOME_DB/20200202201436/2/DIFF

By running the above command, the whole database is restored from 1/FULL backup and all changes before the 2/DIFF backup was introduced are applied. Configuration is stored in the tables within RESTORE_001 schema. Tables will be created with the names indicated by the --translation-pattern parameter. In the example above, "TEST01.{s}_{t}" means that the objects will be created in a "TEST01" schema. The name consists of the original object's schema and table name separated by an underscore (_) character.

+--------------------------+------------------------------------+
|    Netezza object name   |          DB2 object name           |
+--------------------------+------------------------------------|
|     MYSCHEMA.TABLE1      |     "TEST01"."MYSCHEMA_TABLE1"     |
|     MYSCHEMA.TABLE2      |     "TEST01"."MYSCHEMA_TABLE2"     |
|     MYSCHEMA.TABLE3      |     "TEST01"."MYSCHEMA_TABLE3"     |
+--------------------------+------------------------------------+

As a result, all three tables are in the state as defined in backup 2.

The following is an example of another call:
   db_migrate frombackup restore \
                    --host 192.168.1.50 \
                    --user db_user \
                    --password mY_Secre7Pa55worD \
                    --security=ssl \
                    --config-schema RESTORE_001 \
                    --translation-pattern="TEST01.{s}_{t}" \
                    --src /mnt/backups/SOME_DB/20200202201436/3/DIFF \
                    --tables MYSCHEMA.TABLE1 MYSCHEMA.TABLE2

Owing to configuration stored in RESTORE_001 schema, db_migrate should recognize that the tables are restored to backup 2. It will attempt to upgrade it to backup 3. When the --tables option is specified, only tables listed in that parameter are processed. As a result, the two mentioned tables will be upgraded to the version stored in backup 3, while MYSCHEMA.TABLE3 will remain as is, remaining on increment 2. To make things easier, you can list the tables in a file (one argument per line) and replace --tables MYSCHEMA.TABLE1 MYSCHEMA.TABLE2 with --tables @file_with_table_list.

This technique (replacing list of arguments with @filename, where filename is a name of file containing such arguments all separated by a new line) can be used to substiture any bunch of arguments. For example, arguments related to connection data can be stored in a single file and not typed each time the command is executed. For further reference, please refer to fromfile_prefix_chars parameter in Python's argparse library, db_migrate uses fromfile_prefix_chars="@".

To restore other tables to the state from backup 3, the previous command can be repeated without the --tables argument, to restore all tables being still behind backup 3, or with --tables while limiting the set of tables to be processed. However, this is not always necessary. Calling restore of a more recent backup like below, MYSCHEMA.TABLE3 will be automatically upgraded first to backup 3, then to backup 4 and 5. MYSCHEMA.TABLE1 and MYSCHEMA.TABLE2 will be updated to backup 5 as well. There is no need to process intermediate backups (backup 4 in this case) manually.
   db_migrate frombackup restore \
                    --host 192.168.1.50 \
                    --user db_user \
                    --password mY_Secre7Pa55worD \
                    --security=ssl \
                    --config-schema RESTORE_001 \
                    --translation-pattern="TEST01.{s}_{t}" \
                    --src /mnt/backups/SOME_DB/20200202201436/5/DIFF