How to backup and restore table in IBM PureData Systems for Analytics

Answer

We will use the script nz_backup in order to backup two tables in production and nz_restore in order to restore this tables in test environment.

Backup

1) Create one directory to backup our tables. First verify if there is space enough to backup the tables.

• mkdir -p /nzscratch/backup

2) In our example we will backup the tables BILL and INFO_USER

Create one file using vi command that will contain the list of tables that we plan to backup as e.g below:

• vi /nzscratch/backup/the_list_of_tables

BILL
INFO_USER

3) Backup the tables
nz_backup - Online help is available for the utility using the nz_backup -h command.

• nz_backup -db DWPROD -dir /nzscratch/backup -format binary -t `cat /nzscratch/backup/the_list_of_tables`

nz_backup of database DWPROD started on 2014-07-31 21:17:03

NZ_HOST:
NZ_USER: admin
NZ_DATABASE: DWPROD
Backup Directory: /nzscratch/backup
Backup Format: binary
Custom Script:
# Of Threads: 1
Using lastTXid: 777448
The stableTXid value was: 777447
Debug Mode: Disabled

=======================================================================================

Info: table 1 of 2 BILL
Info: backup process started 2014-07-31 21:17:04
Info: estimated # of records 1
Info: unloading data ( thread 1 of 1 )
Info: data flowing ...
Info: unload finished ( thread 1 of 1 ) seconds 1
Info: unload results ( thread 1 of 1 ) INSERT 0 1
Info: data flow finished
Info: backup process ended 2014-07-31 21:17:05
Info: source table size 131,072
Info: backup file size 177
Info: rate (backup file size / # of seconds elapsed) 177
Info: ratio (source table size / backup file size) 740.51
Info: # of records unloaded 1
Info: # of seconds elapsed 1

Info: table 2 of 2 INFO_USER
Info: backup process started 2014-07-31 21:17:05
Info: estimated # of records 1
Info: unloading data ( thread 1 of 1 )
Info: data flowing ...
Info: unload finished ( thread 1 of 1 ) seconds 0
Info: unload results ( thread 1 of 1 ) INSERT 0 1
Info: data flow finished
Info: backup process ended 2014-07-31 21:17:05
Info: source table size 131,072
Info: backup file size 182
Info: rate (backup file size / # of seconds elapsed) 182
Info: ratio (source table size / backup file size) 720.17
Info: # of records unloaded 1
Info: # of seconds elapsed 0

=======================================================================================

nz_backup of database DWPROD finished on 2014-07-31 21:17:05

Total # of tables processed: 2
Total # of records unloaded: 2
Total # of seconds elapsed : 1

TOTAL source table size : 262,144
TOTAL backup file size : 359
rate (bytes per second) : 359
ratio (source / backup) : 730.20

=======================================================================================

4) Generate the ddl's from the tables using the script nz_ddl_table and save it as file 'tables.ddl'. We will use this file to create the tables on test environment.

Online help is available for the utility using the nz_ddl_table -h command.

• nz_ddl_table DWPROD BILL > /nzscratch/backup/tables.ddl
• nz_ddl_table DWPROD INFO_USER >> /nzscratch/backup/tables.ddl

5) Create tar file with all the content of directory '/nzscratch/backup'
Move to directory where contain our files

• cd /nzscratch/backup

List the content of directory '/nzscratch/backup'

• ls -ltr /nzscratch/backup

total 16
-rw-rw-r-- 1 nz nz 15 Jul 31 21:16 the_list_of_tables
-rw-r--r-- 1 nz nz 177 Jul 31 21:17 BILL.1
-rw-r--r-- 1 nz nz 182 Jul 31 21:17 INFO_USER.1
-rw-rw-r-- 1 nz nz 682 Jul 31 21:25 tables.ddl

Create the tar file named as 'backup_tables.tgz'

• tar cvzf backup_tables.tgz the_list_of_tables BILL.1 INFO_USER.1 tables.ddl

the_list_of_tables
BILL.1
INFO_USER.1
tables.ddl

6) Send the tar file 'backup_tables.tgz' to test machine

• ls -ltr /nzscratch/backup/backup_tables.tgz
  -rw-rw-r-- 1 nz nz 674 Jul 31 21:36 /nzscratch/backup/backup_tables.tgz

Restore

7) Create a directory to save the tar file

• mkdir -p /nzscratch/restore
• cd /nzscratch/restore

Copy the tar file to directory '/nzscratch/restore' on test machine and un-tar it.

• tar -xvzf backup_tables.tgz

the_list_of_tables
BILL.1
INFO_USER.1
tables.ddl

8) Create tables "INFO_USER, BILL" on database DWSTAGE using the file 'tables.ddl'.

• nzsql -db DWSTAGE -f tables.ddl
  ***** Creating table: "INFO_USER"
  CREATE TABLE
  ***** Creating table: "BILL"
  CREATE TABLE

9) Use script nz_restore in order to restore the tables in our test system.

nz_restore - Online help is available for the utility using the nz_restore -h command.

• nz_restore -db DWSTAGE -dir /nzscratch/restore -format binary -t `cat /nzscratch/restore/the_list_of_tables`

nz_restore of database DWSTAGE started on 2014-07-31 21:52:45

NZ_HOST:
NZ_USER: admin
NZ_DATABASE: DWSTAGE
Backup Directory: /nzscratch/restore
Backup Format: binary
Custom Script:
# Of Threads: 1
Using stableTXid: 0
The lastTXid assigned was: 0
Debug Mode: Disabled

=======================================================================================

Info: table 1 of 2 BILL
Info: restore process started 2014-07-31 21:52:45
Info: # of bytes to be read 177
Info: reloading data ( thread 1 of 1 )
Info: data flowing ...
Info: reload finished ( thread 1 of 1 ) seconds 1
Info: reload results ( thread 1 of 1 ) INSERT 0 1
Info: data flow finished
Info: restore process ended 2014-07-31 21:52:46
Info: rate (restore file size / # of seconds elapsed) 177
Info: # of records reloaded 1
Info: # of seconds elapsed 1

Info: table 2 of 2 INFO_USER
Info: restore process started 2014-07-31 21:52:47
Info: # of bytes to be read 182
Info: reloading data ( thread 1 of 1 )
Info: data flowing ...
Info: reload finished ( thread 1 of 1 ) seconds 0
Info: reload results ( thread 1 of 1 ) INSERT 0 1
Info: data flow finished
Info: restore process ended 2014-07-31 21:52:47
Info: rate (restore file size / # of seconds elapsed) 182
Info: # of records reloaded 1
Info: # of seconds elapsed 0

=======================================================================================

nz_restore of database DWSTAGE finished on 2014-07-31 21:52:47

Total # of tables processed: 2
Total # of records reloaded: 2
Total # of seconds elapsed : 1

TOTAL restore file size : 359
rate (bytes per second) : 359

=======================================================================================