Here is the step by step table restore using Netezza utility nz_backup & nz_restore
pre-request:table must be created prior to restore.
nz@<nz_server_name>:/nzscratch/test> nz_backup -db NZ_PRD1 -t NZ_table_1 -format binary -dir /nzscratch/test
nz_backup of database NZ_PRD1 started on 2018-03-28 11:21:10
NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_PRD1
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 1
stableTXid: 6148525
lastTXid: 6148610
This backup is based on TXid: 6148608
with an invisibility list of: (6148526)
Debug Mode: Disabled
=======================================================================================
ERROR: 0 tables were processed !!
No tables (matching your specifications) were found.
Or, perhaps this database account doesn't have access to those tables.
=======================================================================================
nz_backup of database NZ_PRD1 finished on 2018-03-28 11:21:10
Total # of tables processed: 0
Total # of seconds elapsed : 0
TOTAL source table size : 0
TOTAL backup file size : 0
rate (bytes per second) : 0
ratio (source / backup) : 0
=======================================================================================
nz@<nz_server_name>:/nzscratch/test> nz_find_object NZ_table_1
The Object Name Is | It Is Of Type | Its 'objid' Is | In The Database
-----------------------------+---------------+----------------+------------------
NZ_table_1 | TABLE | 18413626 | NZ_PRD1_MARCH23
(1 row)
nz@<nz_server_name>:/nzscratch/test> nz_backup -db NZ_PRD1_MARCH23 -t NZ_table_1 -format binary -dir /nzscratch/test
nz_backup of database NZ_PRD1_MARCH23 started on 2018-03-28 11:22:21
NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_PRD1_MARCH23
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 1
stableTXid: 6148525
lastTXid: 6148616
This backup is based on TXid: 6148614
with an invisibility list of: (6148526)
Debug Mode: Disabled
=======================================================================================
Info: table 1 of 1 NZ_table_1
Info: backup process started 2018-03-28 11:22:21
Info: estimated # of records 14,534,983
Info: unloading data ( thread 1 of 1 )
Info: data flowing ...
Info: unload finished ( thread 1 of 1 ) seconds 5
Info: unload results ( thread 1 of 1 ) INSERT 0 14534983
Info: data flow finished
Info: backup process ended 2018-03-28 11:22:26
Info: source table size 947,781,632
Info: backup file size 851,962,520
Info: rate (backup file size / # of seconds elapsed) 170,392,504
Info: ratio (source table size / backup file size) 1.11
Info: # of records unloaded 14,534,983
Info: # of seconds elapsed 5
=======================================================================================
nz_backup of database NZ_PRD1_MARCH23 finished on 2018-03-28 11:22:26
Total # of tables processed: 1
Total # of records unloaded: 14,534,983
Total # of seconds elapsed : 5
TOTAL source table size : 947,781,632
TOTAL backup file size : 851,962,520
rate (bytes per second) : 170,392,504
ratio (source / backup) : 1.11
=======================================================================================
nz@<nz_server_name>:/nzscratch/test> ls -ltr
total 832816
-rw-r--r-- 1 nz nz 851962520 Mar 28 11:22 NZ_table_1.1
nz@<nz_server_name>:/nzscratch/test> rm NZ_table_1.1
nz@<nz_server_name>:/nzscratch/test>
nz@<nz_server_name>:/nzscratch/test> nz_backup -db NZ_PRD1_MARCH23 -t NZ_table_1 -format binary -dir /nzscratch/test -thread 4
nz_backup of database NZ_PRD1_MARCH23 started on 2018-03-28 11:24:11
NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_PRD1_MARCH23
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 4
stableTXid: 6148525
lastTXid: 6148624
This backup is based on TXid: 6148622
with an invisibility list of: (6148526)
Debug Mode: Disabled
=======================================================================================
Info: table 1 of 1 NZ_table_1
Info: backup process started 2018-03-28 11:24:11
Info: estimated # of records 14,534,983
Info: unloading data ( thread 1 of 4 )
Info: unloading data ( thread 2 of 4 )
Info: unloading data ( thread 3 of 4 )
Info: unloading data ( thread 4 of 4 )
Info: data flowing ...
Info: unload finished ( thread 4 of 4 ) seconds 5
Info: unload results ( thread 4 of 4 ) INSERT 0 3306067
Info: unload finished ( thread 1 of 4 ) seconds 5
Info: unload results ( thread 1 of 4 ) INSERT 0 3963043
Info: unload finished ( thread 2 of 4 ) seconds 5
Info: unload results ( thread 2 of 4 ) INSERT 0 3963680
Info: unload finished ( thread 3 of 4 ) seconds 6
Info: unload results ( thread 3 of 4 ) INSERT 0 3302193
Info: data flow finished
Info: backup process ended 2018-03-28 11:24:17
Info: source table size 947,781,632
Info: backup file size 851,963,336
Info: rate (backup file size / # of seconds elapsed) 141,993,889
Info: ratio (source table size / backup file size) 1.11
Info: # of records unloaded 14,534,983
Info: # of seconds elapsed 6
=======================================================================================
nz_backup of database NZ_PRD1_MARCH23 finished on 2018-03-28 11:24:17
Total # of tables processed: 1
Total # of records unloaded: 14,534,983
Total # of seconds elapsed : 6
TOTAL source table size : 947,781,632
TOTAL backup file size : 851,963,336
rate (bytes per second) : 141,993,889
ratio (source / backup) : 1.11
=======================================================================================
nz@<nz_server_name>:/nzscratch/test> ls -ltr
total 832844
-rw-r--r-- 1 nz nz 193783237 Mar 28 11:24 NZ_table_1.4
-rw-r--r-- 1 nz nz 232343901 Mar 28 11:24 NZ_table_1.2
-rw-r--r-- 1 nz nz 232283008 Mar 28 11:24 NZ_table_1.1
-rw-r--r-- 1 nz nz 193553190 Mar 28 11:24 NZ_table_1.3
nz@<nz_server_name>:/nzscratch/test> 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)=> \l
List of databases
DATABASE | OWNER
------------------------------+---------
NZ_PRD1 | CVADMIN
NZ_PRD1_MARCH23 | ADMIN
NZ_PRD1_OLD | ADMIN
SYSTEM | ADMIN
(16 rows)
SYSTEM(ADMIN)=> \q
nz@<nz_server_name>:/nzscratch/test> nz_restore -db NZ_prd2_db_OLD -t NZ_table_1 -format binary -dir /nzscratch/test -thread 4
nz_restore of database NZ_prd2_db_OLD started on 2018-03-28 11:28:49
NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_prd2_db_OLD
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 4
Using stableTXid: 0
The lastTXid assigned was: 0
Debug Mode: Disabled
=======================================================================================
ERROR: 0 tables were processed !!
No tables (matching your specifications) were found.
Or, perhaps this database account doesn't have access to those tables.
=======================================================================================
nz_restore of database NZ_prd2_db_OLD finished on 2018-03-28 11:28:49
Total # of tables processed: 0
Total # of seconds elapsed : 0
=======================================================================================
nz@<nz_server_name>:/nzscratch/test> nz_ddl_table NZ_PRD1_MARCH23 NZ_table_1
\echo
\echo ***** Creating table: "NZ_table_1"
CREATE TABLE NZ_table_1
(
LOAD_DATE timestamp,
RCC character varying(50),
NWM_FACILITY_DESC character varying(50),
NWM_CMTS_DESC character varying(50),
NODE_DESC character varying(10),
DATA_DEVICES integer,
TOTAL_CUSTOMERS integer,
NODE_HEALTH_SCORE numeric(10,8),
NODE_HEALTH_SCORE_LAST_3_DAYS numeric(10,8),
FLAG integer,
TX_SCORE numeric(10,8),
RCV_SCORE numeric(10,8),
DS_SNR_SCORE numeric(10,8),
DS_CER_SCORE numeric(10,8),
US_SNR_SCORE numeric(10,8),
US_CER_SCORE numeric(10,8),
US_CCER_SCORE numeric(10,8),
CM_T3_SCORE numeric(10,8),
TOTAL_OPEN_TICKETS integer,
TOTAL_MODULATIONS_LAST_3_DAYS integer,
SUM_DURATION_MIN_PAST_3_DAYS numeric(10,2),
TOTAL_OSP_REF integer,
TOTAL_TSG_BUS_REF integer,
TOTAL_TSG_RES_REF integer,
TOTAL_TROUBLE_CALLS integer,
CRITICAL character varying(3),
DATE_STAMP timestamp,
KOM_LAST_MODIFIED_DATE timestamp
)
DISTRIBUTE ON RANDOM
;
/*
Number of columns 28
(Variable) Data Size 158 - 322
Row Overhead 34
====================== =============
Total Row Size (bytes) 192 - 356
*/
nz@<nz_server_name>:/nzscratch/test> 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)=> \l
List of databases
DATABASE | OWNER
------------------------------+---------
NZ_PRD1 | CVADMIN
NZ_PRD1_MARCH23 | ADMIN
NZ_PRD1_OLD | ADMIN
SYSTEM | ADMIN
(16 rows)
SYSTEM(ADMIN)=> \c NZ_prd2_db_old
You are now connected to database NZ_prd2_db_old.
NZ_prd2_db_OLD(ADMIN)=> CREATE TABLE NZ_table_1
NZ_prd2_db_OLD(ADMIN)-> RCC character varying(50),
NWM_FACILITY_DESC character varying(50),
NWM_CMTS_DESC character varying(50),
NODE_DESC character varying(10),
DATA_DEVICES integer,
TOTAL_CUSTOMERS integer,
NODE_HEALTH_SCORE numeric(10,8),
(
NZ_prd2_db_OLD(ADMIN)(> RCV_SCORE numeric(10,8),
DS_SNR_SCORE numeric(10,8),
DS_CER_SCORE numeric(10,8),
LOAD_DATE timestamp,
NZ_prd2_db_OLD(ADMIN)(> US_CCER_SCORE numeric(10,8),
CM_T3_SCORE numeric(10,8),
TOTAL_OPEN_TICKETS integer,
TOTAL_MODULATIONS_LAST_3_DAYS integer,
SUM_DURATION_MIN_PAST_3_DAYS numeric(10,2),
TOTAL_OSP_REF integer,
TOTAL_TSG_BUS_REF integer,
TOTAL_TSG_RES_REF integer,
TOTAL_TROUBLE_CALLS integer,
CRITICAL character varying(3),
DATE_STAMP timestamp,
KOM_LAST_MODIFIED_DATE timestamp
)
RCC character varying(50),
NZ_prd2_db_OLD(ADMIN)(> NWM_FACILITY_DESC character varying(50),
NZ_prd2_db_OLD(ADMIN)(> NWM_CMTS_DESC character varying(50),
NZ_prd2_db_OLD(ADMIN)(> NODE_DESC character varying(10),
NZ_prd2_db_OLD(ADMIN)(> DATA_DEVICES integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_CUSTOMERS integer,
NZ_prd2_db_OLD(ADMIN)(> NODE_HEALTH_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> NODE_HEALTH_SCORE_LAST_3_DAYS numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> FLAG integer,
NZ_prd2_db_OLD(ADMIN)(> TX_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> RCV_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> DS_SNR_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> DS_CER_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> US_SNR_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> US_CER_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> US_CCER_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> CM_T3_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> TOTAL_OPEN_TICKETS integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_MODULATIONS_LAST_3_DAYS integer,
NZ_prd2_db_OLD(ADMIN)(> SUM_DURATION_MIN_PAST_3_DAYS numeric(10,2),
NZ_prd2_db_OLD(ADMIN)(> TOTAL_OSP_REF integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_TSG_BUS_REF integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_TSG_RES_REF integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_TROUBLE_CALLS integer,
NZ_prd2_db_OLD(ADMIN)(> CRITICAL character varying(3),
NZ_prd2_db_OLD(ADMIN)(> DATE_STAMP timestamp,
NZ_prd2_db_OLD(ADMIN)(> KOM_LAST_MODIFIED_DATE timestamp
NZ_prd2_db_OLD(ADMIN)(> )
NZ_prd2_db_OLD(ADMIN)-> DISTRIBUTE ON RANDOM
NZ_prd2_db_OLD(ADMIN)-> ;
CREATE TABLE
NZ_prd2_db_OLD(ADMIN)=> \q
nz@<nz_server_name>:/nzscratch/test> nz_restore -db NZ_prd2_db_OLD -t NZ_table_1 -format binary -dir /nzscratch/test -thread 4
nz_restore of database NZ_prd2_db_OLD started on 2018-03-28 11:30:13
NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_prd2_db_OLD
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 4
Using stableTXid: 0
The lastTXid assigned was: 0
Debug Mode: Disabled
=======================================================================================
Info: table 1 of 1 NZ_table_1
Info: restore process started 2018-03-28 11:30:13
Info: # of bytes to be read 851,963,336
Info: reloading data ( thread 1 of 4 )
Info: reloading data ( thread 2 of 4 )
Info: reloading data ( thread 3 of 4 )
Info: reloading data ( thread 4 of 4 )
Info: data flowing ...
Info: reload finished ( thread 3 of 4 ) seconds 38
Info: reload results ( thread 3 of 4 ) INSERT 0 3302193
Info: reload finished ( thread 4 of 4 ) seconds 38
Info: reload results ( thread 4 of 4 ) INSERT 0 3306067
Info: reload finished ( thread 2 of 4 ) seconds 41
Info: reload finished ( thread 1 of 4 ) seconds 41
Info: reload results ( thread 2 of 4 ) INSERT 0 3963680
Info: reload results ( thread 1 of 4 ) INSERT 0 3963043
Info: data flow finished
Info: restore process ended 2018-03-28 11:30:54
Info: rate (restore file size / # of seconds elapsed) 20,779,593
Info: # of records reloaded 14,534,983
Info: # of seconds elapsed 41
=======================================================================================
nz_restore of database NZ_prd2_db_OLD finished on 2018-03-28 11:30:54
Total # of tables processed: 1
Total # of records reloaded: 14,534,983
Total # of seconds elapsed : 41
TOTAL restore file size : 851,963,336
rate (bytes per second) : 20,779,593
=======================================================================================
nz@<nz_server_name>:/nzscratch/test> nz_find_object NZ_table_1
The Object Name Is | It Is Of Type | Its 'objid' Is | In The Database
-----------------------------+---------------+----------------+------------------
NZ_table_1 | TABLE | 18572914 | NZ_prd2_db_OLD
NZ_table_1 | TABLE | 18413626 | NZ_PRD1_MARCH23
(2 rows)
pre-request:table must be created prior to restore.
nz@<nz_server_name>:/nzscratch/test> nz_backup -db NZ_PRD1 -t NZ_table_1 -format binary -dir /nzscratch/test
nz_backup of database NZ_PRD1 started on 2018-03-28 11:21:10
NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_PRD1
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 1
stableTXid: 6148525
lastTXid: 6148610
This backup is based on TXid: 6148608
with an invisibility list of: (6148526)
Debug Mode: Disabled
=======================================================================================
ERROR: 0 tables were processed !!
No tables (matching your specifications) were found.
Or, perhaps this database account doesn't have access to those tables.
=======================================================================================
nz_backup of database NZ_PRD1 finished on 2018-03-28 11:21:10
Total # of tables processed: 0
Total # of seconds elapsed : 0
TOTAL source table size : 0
TOTAL backup file size : 0
rate (bytes per second) : 0
ratio (source / backup) : 0
=======================================================================================
nz@<nz_server_name>:/nzscratch/test> nz_find_object NZ_table_1
The Object Name Is | It Is Of Type | Its 'objid' Is | In The Database
-----------------------------+---------------+----------------+------------------
NZ_table_1 | TABLE | 18413626 | NZ_PRD1_MARCH23
(1 row)
nz@<nz_server_name>:/nzscratch/test> nz_backup -db NZ_PRD1_MARCH23 -t NZ_table_1 -format binary -dir /nzscratch/test
nz_backup of database NZ_PRD1_MARCH23 started on 2018-03-28 11:22:21
NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_PRD1_MARCH23
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 1
stableTXid: 6148525
lastTXid: 6148616
This backup is based on TXid: 6148614
with an invisibility list of: (6148526)
Debug Mode: Disabled
=======================================================================================
Info: table 1 of 1 NZ_table_1
Info: backup process started 2018-03-28 11:22:21
Info: estimated # of records 14,534,983
Info: unloading data ( thread 1 of 1 )
Info: data flowing ...
Info: unload finished ( thread 1 of 1 ) seconds 5
Info: unload results ( thread 1 of 1 ) INSERT 0 14534983
Info: data flow finished
Info: backup process ended 2018-03-28 11:22:26
Info: source table size 947,781,632
Info: backup file size 851,962,520
Info: rate (backup file size / # of seconds elapsed) 170,392,504
Info: ratio (source table size / backup file size) 1.11
Info: # of records unloaded 14,534,983
Info: # of seconds elapsed 5
=======================================================================================
nz_backup of database NZ_PRD1_MARCH23 finished on 2018-03-28 11:22:26
Total # of tables processed: 1
Total # of records unloaded: 14,534,983
Total # of seconds elapsed : 5
TOTAL source table size : 947,781,632
TOTAL backup file size : 851,962,520
rate (bytes per second) : 170,392,504
ratio (source / backup) : 1.11
=======================================================================================
nz@<nz_server_name>:/nzscratch/test> ls -ltr
total 832816
-rw-r--r-- 1 nz nz 851962520 Mar 28 11:22 NZ_table_1.1
nz@<nz_server_name>:/nzscratch/test> rm NZ_table_1.1
nz@<nz_server_name>:/nzscratch/test>
nz@<nz_server_name>:/nzscratch/test> nz_backup -db NZ_PRD1_MARCH23 -t NZ_table_1 -format binary -dir /nzscratch/test -thread 4
nz_backup of database NZ_PRD1_MARCH23 started on 2018-03-28 11:24:11
NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_PRD1_MARCH23
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 4
stableTXid: 6148525
lastTXid: 6148624
This backup is based on TXid: 6148622
with an invisibility list of: (6148526)
Debug Mode: Disabled
=======================================================================================
Info: table 1 of 1 NZ_table_1
Info: backup process started 2018-03-28 11:24:11
Info: estimated # of records 14,534,983
Info: unloading data ( thread 1 of 4 )
Info: unloading data ( thread 2 of 4 )
Info: unloading data ( thread 3 of 4 )
Info: unloading data ( thread 4 of 4 )
Info: data flowing ...
Info: unload finished ( thread 4 of 4 ) seconds 5
Info: unload results ( thread 4 of 4 ) INSERT 0 3306067
Info: unload finished ( thread 1 of 4 ) seconds 5
Info: unload results ( thread 1 of 4 ) INSERT 0 3963043
Info: unload finished ( thread 2 of 4 ) seconds 5
Info: unload results ( thread 2 of 4 ) INSERT 0 3963680
Info: unload finished ( thread 3 of 4 ) seconds 6
Info: unload results ( thread 3 of 4 ) INSERT 0 3302193
Info: data flow finished
Info: backup process ended 2018-03-28 11:24:17
Info: source table size 947,781,632
Info: backup file size 851,963,336
Info: rate (backup file size / # of seconds elapsed) 141,993,889
Info: ratio (source table size / backup file size) 1.11
Info: # of records unloaded 14,534,983
Info: # of seconds elapsed 6
=======================================================================================
nz_backup of database NZ_PRD1_MARCH23 finished on 2018-03-28 11:24:17
Total # of tables processed: 1
Total # of records unloaded: 14,534,983
Total # of seconds elapsed : 6
TOTAL source table size : 947,781,632
TOTAL backup file size : 851,963,336
rate (bytes per second) : 141,993,889
ratio (source / backup) : 1.11
=======================================================================================
nz@<nz_server_name>:/nzscratch/test> ls -ltr
total 832844
-rw-r--r-- 1 nz nz 193783237 Mar 28 11:24 NZ_table_1.4
-rw-r--r-- 1 nz nz 232343901 Mar 28 11:24 NZ_table_1.2
-rw-r--r-- 1 nz nz 232283008 Mar 28 11:24 NZ_table_1.1
-rw-r--r-- 1 nz nz 193553190 Mar 28 11:24 NZ_table_1.3
nz@<nz_server_name>:/nzscratch/test> 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)=> \l
List of databases
DATABASE | OWNER
------------------------------+---------
NZ_PRD1 | CVADMIN
NZ_PRD1_MARCH23 | ADMIN
NZ_PRD1_OLD | ADMIN
SYSTEM | ADMIN
(16 rows)
SYSTEM(ADMIN)=> \q
nz@<nz_server_name>:/nzscratch/test> nz_restore -db NZ_prd2_db_OLD -t NZ_table_1 -format binary -dir /nzscratch/test -thread 4
nz_restore of database NZ_prd2_db_OLD started on 2018-03-28 11:28:49
NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_prd2_db_OLD
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 4
Using stableTXid: 0
The lastTXid assigned was: 0
Debug Mode: Disabled
=======================================================================================
ERROR: 0 tables were processed !!
No tables (matching your specifications) were found.
Or, perhaps this database account doesn't have access to those tables.
=======================================================================================
nz_restore of database NZ_prd2_db_OLD finished on 2018-03-28 11:28:49
Total # of tables processed: 0
Total # of seconds elapsed : 0
=======================================================================================
nz@<nz_server_name>:/nzscratch/test> nz_ddl_table NZ_PRD1_MARCH23 NZ_table_1
\echo
\echo ***** Creating table: "NZ_table_1"
CREATE TABLE NZ_table_1
(
LOAD_DATE timestamp,
RCC character varying(50),
NWM_FACILITY_DESC character varying(50),
NWM_CMTS_DESC character varying(50),
NODE_DESC character varying(10),
DATA_DEVICES integer,
TOTAL_CUSTOMERS integer,
NODE_HEALTH_SCORE numeric(10,8),
NODE_HEALTH_SCORE_LAST_3_DAYS numeric(10,8),
FLAG integer,
TX_SCORE numeric(10,8),
RCV_SCORE numeric(10,8),
DS_SNR_SCORE numeric(10,8),
DS_CER_SCORE numeric(10,8),
US_SNR_SCORE numeric(10,8),
US_CER_SCORE numeric(10,8),
US_CCER_SCORE numeric(10,8),
CM_T3_SCORE numeric(10,8),
TOTAL_OPEN_TICKETS integer,
TOTAL_MODULATIONS_LAST_3_DAYS integer,
SUM_DURATION_MIN_PAST_3_DAYS numeric(10,2),
TOTAL_OSP_REF integer,
TOTAL_TSG_BUS_REF integer,
TOTAL_TSG_RES_REF integer,
TOTAL_TROUBLE_CALLS integer,
CRITICAL character varying(3),
DATE_STAMP timestamp,
KOM_LAST_MODIFIED_DATE timestamp
)
DISTRIBUTE ON RANDOM
;
/*
Number of columns 28
(Variable) Data Size 158 - 322
Row Overhead 34
====================== =============
Total Row Size (bytes) 192 - 356
*/
nz@<nz_server_name>:/nzscratch/test> 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)=> \l
List of databases
DATABASE | OWNER
------------------------------+---------
NZ_PRD1 | CVADMIN
NZ_PRD1_MARCH23 | ADMIN
NZ_PRD1_OLD | ADMIN
SYSTEM | ADMIN
(16 rows)
SYSTEM(ADMIN)=> \c NZ_prd2_db_old
You are now connected to database NZ_prd2_db_old.
NZ_prd2_db_OLD(ADMIN)=> CREATE TABLE NZ_table_1
NZ_prd2_db_OLD(ADMIN)-> RCC character varying(50),
NWM_FACILITY_DESC character varying(50),
NWM_CMTS_DESC character varying(50),
NODE_DESC character varying(10),
DATA_DEVICES integer,
TOTAL_CUSTOMERS integer,
NODE_HEALTH_SCORE numeric(10,8),
(
NZ_prd2_db_OLD(ADMIN)(> RCV_SCORE numeric(10,8),
DS_SNR_SCORE numeric(10,8),
DS_CER_SCORE numeric(10,8),
LOAD_DATE timestamp,
NZ_prd2_db_OLD(ADMIN)(> US_CCER_SCORE numeric(10,8),
CM_T3_SCORE numeric(10,8),
TOTAL_OPEN_TICKETS integer,
TOTAL_MODULATIONS_LAST_3_DAYS integer,
SUM_DURATION_MIN_PAST_3_DAYS numeric(10,2),
TOTAL_OSP_REF integer,
TOTAL_TSG_BUS_REF integer,
TOTAL_TSG_RES_REF integer,
TOTAL_TROUBLE_CALLS integer,
CRITICAL character varying(3),
DATE_STAMP timestamp,
KOM_LAST_MODIFIED_DATE timestamp
)
RCC character varying(50),
NZ_prd2_db_OLD(ADMIN)(> NWM_FACILITY_DESC character varying(50),
NZ_prd2_db_OLD(ADMIN)(> NWM_CMTS_DESC character varying(50),
NZ_prd2_db_OLD(ADMIN)(> NODE_DESC character varying(10),
NZ_prd2_db_OLD(ADMIN)(> DATA_DEVICES integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_CUSTOMERS integer,
NZ_prd2_db_OLD(ADMIN)(> NODE_HEALTH_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> NODE_HEALTH_SCORE_LAST_3_DAYS numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> FLAG integer,
NZ_prd2_db_OLD(ADMIN)(> TX_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> RCV_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> DS_SNR_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> DS_CER_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> US_SNR_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> US_CER_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> US_CCER_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> CM_T3_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> TOTAL_OPEN_TICKETS integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_MODULATIONS_LAST_3_DAYS integer,
NZ_prd2_db_OLD(ADMIN)(> SUM_DURATION_MIN_PAST_3_DAYS numeric(10,2),
NZ_prd2_db_OLD(ADMIN)(> TOTAL_OSP_REF integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_TSG_BUS_REF integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_TSG_RES_REF integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_TROUBLE_CALLS integer,
NZ_prd2_db_OLD(ADMIN)(> CRITICAL character varying(3),
NZ_prd2_db_OLD(ADMIN)(> DATE_STAMP timestamp,
NZ_prd2_db_OLD(ADMIN)(> KOM_LAST_MODIFIED_DATE timestamp
NZ_prd2_db_OLD(ADMIN)(> )
NZ_prd2_db_OLD(ADMIN)-> DISTRIBUTE ON RANDOM
NZ_prd2_db_OLD(ADMIN)-> ;
CREATE TABLE
NZ_prd2_db_OLD(ADMIN)=> \q
nz@<nz_server_name>:/nzscratch/test> nz_restore -db NZ_prd2_db_OLD -t NZ_table_1 -format binary -dir /nzscratch/test -thread 4
nz_restore of database NZ_prd2_db_OLD started on 2018-03-28 11:30:13
NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_prd2_db_OLD
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 4
Using stableTXid: 0
The lastTXid assigned was: 0
Debug Mode: Disabled
=======================================================================================
Info: table 1 of 1 NZ_table_1
Info: restore process started 2018-03-28 11:30:13
Info: # of bytes to be read 851,963,336
Info: reloading data ( thread 1 of 4 )
Info: reloading data ( thread 2 of 4 )
Info: reloading data ( thread 3 of 4 )
Info: reloading data ( thread 4 of 4 )
Info: data flowing ...
Info: reload finished ( thread 3 of 4 ) seconds 38
Info: reload results ( thread 3 of 4 ) INSERT 0 3302193
Info: reload finished ( thread 4 of 4 ) seconds 38
Info: reload results ( thread 4 of 4 ) INSERT 0 3306067
Info: reload finished ( thread 2 of 4 ) seconds 41
Info: reload finished ( thread 1 of 4 ) seconds 41
Info: reload results ( thread 2 of 4 ) INSERT 0 3963680
Info: reload results ( thread 1 of 4 ) INSERT 0 3963043
Info: data flow finished
Info: restore process ended 2018-03-28 11:30:54
Info: rate (restore file size / # of seconds elapsed) 20,779,593
Info: # of records reloaded 14,534,983
Info: # of seconds elapsed 41
=======================================================================================
nz_restore of database NZ_prd2_db_OLD finished on 2018-03-28 11:30:54
Total # of tables processed: 1
Total # of records reloaded: 14,534,983
Total # of seconds elapsed : 41
TOTAL restore file size : 851,963,336
rate (bytes per second) : 20,779,593
=======================================================================================
nz@<nz_server_name>:/nzscratch/test> nz_find_object NZ_table_1
The Object Name Is | It Is Of Type | Its 'objid' Is | In The Database
-----------------------------+---------------+----------------+------------------
NZ_table_1 | TABLE | 18572914 | NZ_prd2_db_OLD
NZ_table_1 | TABLE | 18413626 | NZ_PRD1_MARCH23
(2 rows)
No comments:
Post a Comment