Big Data and Warehousing

IBM Netezza Data Migration from a Mustang 4.6.12 Appliance to a TwinFin 1000-12

Data migration using NZBACKUP and NZRESTORE tools

In any migration project, administrators hope to migrate data from one environment to another quickly and easily while minimizing errors. IBM® Netezza® data warehouse appliances provide two backup and restore utilities  that help administrators migrate data between various Netezza environments with minimal difficulties:

(1) nzbackup/nzrestore

(2) nz_backup/nz_restore

This article explains the steps needed to successfully migrate data from IBM Netezza Mustang 4.6.12 to IBM Netezza TwinFin® 7.0 using nzbackup/nzrestore utilities that are part of the core Netezza performance server (NPS) commands. For the purposes of this article and to help you understand the following data migration steps, assume prod_mustang is an NPS 4.6.12 appliance and prod_twinfin is an NPS 7.0 appliance.

 

Generate DDL for existing groups

First, create the groups in prod_twinfin appliance as they exist in prod_mustang. To generate the data definition language (DDL) for existing groups in prod_mustang, you can run the /nz/support/contrib/bin/nz_ddl_group script without any arguments, as shown below.

Note: nz_ddl_group is part of nz-support tools, which is downloadable from IBM Netezza Support Fix Central.

[nz@prod_mustang ~]$ /nz/support/contrib/bin/nz_ddl_group

\echo

\echo *****  Creating group:  “DBA”

CREATE GROUP DBA ;

 

\echo

\echo *****  Creating group:  “APP_DBA”

CREATE GROUP APP_DBA ;

 

\echo

\echo *****  Creating group:  “BATCH”

CREATE GROUP BATCH ;

 

\echo

\echo *****  Creating group:  “ETL”

CREATE GROUP ETL WITH DEFPRIORITY NORMAL MAXPRIORITY NORMAL ;

 

Now, log in to the prod_twinfin appliance and execute the preceding CREATE GROUP statements:

[nz@prod_twinfin ~]$ nzsql

Welcome to nzsql, the IBM Netezza SQL interactive terminal.

 

Type:  \h for help with SQL commands

\? for help on internal slash commands

\g or terminate with semicolon to execute query

\q to quit

 

SYSTEM(ADMIN)=> CREATE GROUP DBA;

CREATE GROUP

SYSTEM(ADMIN)=> CREATE GROUP APP_DBA ;

CREATE GROUP

SYSTEM(ADMIN)=> CREATE GROUP BATCH ;

CREATE GROUP

SYSTEM(ADMIN)=> CREATE GROUP ETL WITH DEFPRIORITY NORMAL MAXPRIORITY NORMAL ;

CREATE GROUP

SYSTEM(ADMIN)=> \dg

List of Groups

GROUPNAME | ROWLIMIT | SESSIONTIMEOUT | QUERYTIMEOUT | DEF_PRIORITY    …

———–+———-+—————-+————–+————–+————–+—————+—–

APP_DBA        |        0                   |              0                  |            0                 | NONE                   ….

BATCH             |        0                   |              0                  |            0                 | NONE                   ….

DBA                  |        0                   |              0                  |            0                 | NONE                   ….

ETL                   |        0                   |              0                  |            0                 | NORMAL              ….

PUBLIC            |        0                   |              0                  |            0                 | NONE                   ….

 

SYSTEM(ADMIN)=>

 

Back up users and the database

Once the group’s creation is successful, the next step is to back up the users and the database on the prod_mustang appliance and to make sure these backups are available on the prod_twinfin appliance for restore. The example in this article shows a .TAR file of these backups on prod_mustang and the secure copy (scp) command for the prod_twinfin appliance to restore.
 

Back up your users:

[nz@prod_mustang /tmp/nz_old]$ nzbackup -v -dir /tmp/nz_old/ -users

[Backup Server] : Starting the backup process

[Backup Server] : Backing up to base directory ‘/tmp/nz_old’

[Backup Server] : Start retrieving the schema

[Backup Server] : Backing up metadata to /tmp/nz_old/Netezza/prod_mustang/SYSTEM/20130209192243/1/USERS/md

[Backup Server] : Retrieving host key information

[Backup Server] : Retrieving user information

[Backup Server] : Retrieving group information

[Backup Server] : Retrieving group members

[Backup Server] : Backing up ACL information

[Backup Server] : Operation committed

Backup of users, groups, and global permissions completed successfully.

 

Back up the database:

[nz@prod_mustang nz_old]$ nzbackup -v -db MYDB_MUSTANG -dir /tmp/nz_old

[Backup Server] : Starting the backup process

[Backup Server] : Backing up to base directory ‘/tmp/nz_old’

[Backup Server] : Backing up functions

[Backup Server] : Backing up aggregates

[Backup Server] : Start retrieving the schema

[Backup Server] : Backing up metadata to /tmp/nz_old/Netezza/prod_mustang/MYDB_MUSTANG/20130209195915/1/FULL/md

[Backup Server] : Retrieving host key information

[Backup Server] : Retrieving user information

[Backup Server] : Backing up sequences

[Backup Server] : Backing up table schema.

[Backup Server] : Backing up External Tables.

[Backup Server] : Backing up External table settings.

[Backup Server] : Backing up External table zone settings.

[Backup Server] : Backing up Table Constraints

[Backup Server] : Backing up synonyms

[Backup Server] : Backing up stored procedures

[Backup Server] : Backing up materialized views

[Backup Server] : Backing up view definitions.

[Backup Server] : Retrieving group information

[Backup Server] : Retrieving group members

[Backup Server] : Backing up ACL information

[Backup Server] : Start retrieving the data.

[Backup Server] : Backing up table T1

[Backup Server] : Backing up table T2

[Backup Server] : Operation committed

Backup of database MYDB_MUSTANG to backupset 20130209201330 completed successfully.

 

Create the .TAR file and run scp:

[nz@prod_mustang /tmp/nz_old]$ tar  -czvf   users_mydb.tgz    Netezza

[nz@prod_mustang /tmp/nz_old]$ scp    users_mydb.tgz  nz@prod_twinfin:/tmp/nz_new

 

Restore users and database backups

Once the .TAR file has been transferred to the target TwinFin appliance, log into the target TwinFin and untar it before issuing the restore command.

[nz@prod_twinfin /tmp/nz_new]$ tar  -xzvf   users_mydb.tgz

 

To restore the users’ backup:
[nz@prod_twinfin /tmp/nz_new]$ nzrestore  -v  -dir /tmp/nz_new    -globals  -npshost prod_mustang

 

To restore the database backup:
[nz@prod_twinfin /tmp/nz_new]$ nzrestore  -v  -db MYDB_TWINFIN  -dir /tmp/nz_new   -npshost prod_mustang   -sourcedb  MYDB_MUSTANG

 

If the database backup or restore takes longer, consider running these backup and restore commands using the nohup utility. The backup and restore logs are available in the backupsvr/restoresvr directory under /nz/kit/log/.

What if you have more than one database to migrate? From the second database onward, there is no need to do the users’ backup and restore again—you need only to back up and restore individual databases.

If you have any questions or feedback, please let me know in the comments.

Previous post

Harness the Power of Big Data for a New Economy

Next post

Information On Demand 2013 Call for Speakers Now Open

Ravikumar Nandigam

Ravikumar is an expert in OLTP, data warehousing, Informix, and Netezza database technologies.

  • haylo75

    @karthik – We backup to a network mount with sufficient storage space.

  • karthik

    could you please let me know if the backup is going to some external storage like tivoli how to restore?

  • rahul

    Hi, do you have any article or reference doc which describes which points to be taken care post migration ( Impacts of migration on existing objects / queries )…in terms of data type compatibility or any other compatibility issue?

  • Chandhra S Vadlamudi

    Wow. It really helps. Thanks for sharing.