small test on the Netezza database using nz_backup

Problem:
I did  test on the nz_backup for backup  & restore.
steps:
1)took backup of table using nz_backup
2)restore the table using nz_restore ,it went fine
3)but the pre-request is the table to be available,if the table structure is NOT available it should throw the error.
4)when I change the table name on target it throws the error,if the source table & target  table name differant it should throw the error.
5)but there is a work around for this,if you take the backup if table TBL1 and backup piece should  be TBL1_1 and if the same table does not exist on the target ,it will throw the error,so the work around would be change the backup file name  according to Target table name.
lets say target table would be TBL2 then backup file should be TBL2_1


nz_backup -db NZ_DB1 -t Table_ACCOUNT_BK  -format binary -dir /nz/migration -thread 1

nz_restore -db NZ_DB1 -t Table_ACCOUNT_BK  -format binary -dir /nz -thread 1

nz_restore -db NZ_DB1 -t Table_ACCOUNT_BK1  -format binary -dir /nz -thread 1


NZ_DB1(ADMIN)=> create table Table_ACCOUNT_BK1 as select * from Table_ACCOUNT_BK where 1=2;
INSERT 0 0
NZ_DB1(ADMIN)=> \q
nz@nz_server_name:/nz> nz_restore -db NZ_DB1 -t Table_ACCOUNT_BK1  -format binary -dir /nz -thread 1

nz_restore of database NZ_DB1 started on 2018-04-18 15:20:44

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

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

ERROR: Unable to load table Table_ACCOUNT_BK1
ERROR: Missing data file: /nz/Table_ACCOUNT_BK1.1

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_DB1 finished on 2018-04-18 15:20:44

     Total # of tables processed: 0
     Total # of seconds elapsed : 0

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

*****
***** ERROR
*****
***** Problems were encountered during the running of this script.
***** Inspect the output from this script for more details.
***** Following is a list of tables that encountered problems.
*****

Table_ACCOUNT_BK1

nz@nz_server_name:/nz> ls -ltr
t
-rw-r--r--  1 nz    nz    195775113 Apr 18 15:17 Table_ACCOUNT_BK.1
nz@nz_server_name:/nz> cp -p Table_ACCOUNT_BK.1 Table_ACCOUNT_BK1.1
nz@nz_server_name:/nz> nz_restore -db NZ_DB1 -t Table_ACCOUNT_BK1  -format binary -dir /nz -thread 1

nz_restore of database NZ_DB1 started on 2018-04-18 15:22:00

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

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

Info:  table 1 of 1                                               Table_ACCOUNT_BK1
Info:  restore process started                                    2018-04-18 15:22:00
Info:  # of bytes to be read                                      195,775,113
Info:  reloading data              ( thread 1 of 1 )
Info:  data flowing ...
Info:  reload finished             ( thread 1 of 1 )   seconds    12
Info:  reload results              ( thread 1 of 1 )              INSERT 0 18025675
Info:  data flow finished
Info:  restore process ended                                      2018-04-18 15:22:12
Info:  rate     (restore file size / # of seconds elapsed)        16,314,592
Info:  # of records reloaded                                      18,025,675
Info:  # of seconds elapsed                                       12

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

nz_restore of database NZ_DB1 finished on 2018-04-18 15:22:12

     Total # of tables processed: 1
     Total # of records reloaded: 18,025,675
     Total # of seconds elapsed : 12

     TOTAL restore file size    : 195,775,113
     rate  (bytes per second)   : 16,314,592

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

nz@nz_server_name:/nz>

script to check the users associated with group in Netezza database

database &  table privileges in Netezza database:

select GROUPNAME,
       OBJECTNAME,
       DATABASENAME,
       OBJECTTYPE,
case   when GOPOBJPRIV = 0 then 'None'
       when GOPOBJPRIV = 1 then 'List'
       when GOPOBJPRIV = 2 then 'Select'
       when GOPOBJPRIV = 3 then 'List, Select'
       else GOPOBJPRIV||' Undefined'
       end  privileges
from   _v_group_priv
where GOPOBJPRIV <= 3
  and DATABASENAME NOT in ('SYSTEM')
  and OBJECTTYPE in ('TABLE','VIEW')
order by DATABASENAME;

users associated with group in Netezza database
select groupname, username from _V_GROUPUSERS where groupname <> 'PUBLIC'
order by groupname, username;

user Read write Privilges on database

select USERNAME,
       OBJECTNAME,
       DATABASENAME,
       OBJECTTYPE,
case   when UOPOBJPRIV = 0 then 'None'
       when UOPOBJPRIV = 1 then 'List'
       when UOPOBJPRIV = 2 then 'Select'
       when UOPOBJPRIV = 3 then 'List, Select'
       else UOPOBJPRIV||' Undefined'
       end  privileges
from   _v_user_priv
where UOPOBJPRIV <= 3
  and DATABASENAME not  in ('SYSTEM')
  and OBJECTTYPE in ('TABLE','VIEW')
order by DATABASENAME;

script to list Netezza object in database

Here is the various view used in Netezza to list the database objects


select count(*) from _v_table where objtype='TABLE';

select count(viewname) from _v_view where OBJTYPE='VIEW';

select count(*) from _v_synonym where OBJTYPE='SYNONYM';

select count(FUNCTION) from _v_function;

select count(*) from _V_AGGREGATE;


select count(PROCEDURE) from _v_procedure where OBJTYPE='PROCEDURE';

select count(*) from _v_sequence where OBJTYPE='SEQUENCE';


select count(*) from _v_library ;

Netezza database error.

Issue:
when the application job ran on the netezza system,it throws the below error and aborted the job.its a huge select statement

ERROR:  Records trailing spring space set to 512 is too small : Bump it up using the environment variable NZ_SPRINGFIELD_SIZE

Root cause:

This error can occur when a query includes multiple aggregates against varchar or nvarchar columns. As the query is being processed the system allocates memory for each row of the result set. This includes each of the GROUP BY columns and each of the aggregates. For queries doing aggregates on variable length columns, the total length of the record will vary during the process

solution

To avoid this error message, increase the value for this parameter as follows:

/nz/data/config/system.cfg file. (Create the file if it does not exist.)

system.maxSpringFieldSize = 1024
2. nzstop
3. nzstart

oracle database error

issue :
we recently faced issue with oracle database version 11204 running on AIX server which throwed the error and DB crashed

ORA-63999: data file suffered media failure
ORA-01114: IO error writing block to file 205 (block # 2089728)
ORA-01110: data file 205: '/apporadb/oradata/DB1.temp/temp05.dbf'
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 28: No space left on device
Additional information: -1
Additional information: 24576
DBW0 (ospid: 50593896): terminating the instance due to error 63999
Fri Apr 06 22:02:39 2018
Dumping diagnostic data in directory=[cdmp_20180406220239], requested by (instance=1, osid=50593896 (DBW0)), summary=[abnormal instance termination].
Instance terminated by DBW0, pid = 50593896

cause:

when we try to  look into the log initially nothing reported and later it got updated on the log when the DB crashed.

Solution:

when I checked on the  server one of the temp file eaten up the space on the file system and it grown up and filled the file system  to 100% used ,so incoming  DB connection unable to do thier operation and crashed the DB  and looks like it was bug on the system.

Its not even allow to resize the  temp file.

SQL> alter database tempfile '/appmktp/oradata/oradb.temp/temp05.dbf' resize 20000m;

alter database tempfile '/appmktp/oradata/oradb.temp/temp05.dbf' resize 20000m

*

ERROR at line 1:

ORA-00376: file 205 cannot be read at this time

ORA-01110: data file 205: '/appmktp/oradata/oradb.temp/temp05.dbf'

But  we are able to resize other  tempfiles.

moreover  it does not showing the  auto extend status

SQL> select sum(bytes/1024/1024) ,file_name,AUTOEXTENSIBLE from dba_temp_files group by file_name,AUTOEXTENSIBLE ;

SUM(BYTES/1024/1024)

--------------------

FILE_NAME

----------------------------------------------------------------------------------------------------

AUT

---

/appmktp/oradata/oradbtemp/temp05.dbf

               27000

/appmktp/oradata/oradbtemp/temp01.dbf

NO

               15360

/appmktp/oradata/oradbtemp/temp03.dbf

NO

               30720

/appmktp/oradata/oradbtemp/temp04.dbf

NO

               23552

/appmktp/oradata/oradb01/temp02.dbf

NO

SQL> alter database tempfile '/appmktp/oradata/oradbtemp/temp04.dbf' resize 25000m;

Database altered.

even I rebooted the DB and its NOT allowed  me  to resize the temp file.

However I can  dropped the temp file and recreated one.

SQL> alter database tempfile '/appmktp/oradata/oradbtemp/temp05.dbf' autoextend off;

alter database tempfile '/appmktp/oradata/oradbtemp/temp05.dbf' autoextend off

*

ERROR at line 1:

ORA-00376: file 205 cannot be read at this time

ORA-01110: data file 205: '/appmktp/oradata/oradbtemp/temp05.dbf'

SQL> alter database tempfile '/appmktp/oradata/oradbtemp/temp05.dbf' drop including datafiles;

Database altered.

Then  the issue got resolved and application able to run their query without any issues.

How to migrate the Netezza full database using nz_migrate

Here  is the script to migrate the full database using nz_migrate utility

pre -request.
This will create the new database and there should not be DB available on target side with same name.
it will be helpful to migrate the UDF and it will ask you to enter the target  OS user password to scp the files/ddl from source to target



nz_migrate -shost <source_server_name> -thost <target_server_name>  -sdb <source_db_name> -tdb <target_db_name> -CreateTargetDatabase yes  -table <tablename> -CreateTargetUDX YES  -tuser admin -tpassword 'password' -CreateTargetTable yes -TruncateTargetTable False -format binary  -threads 4




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

nz_migrate started on 2018-03-22 15:57:48

Migrating <sourcer_server_name>:"source_DB_name". --> <target_server_name>:"source_DB_name_NEW".

Data Format   : binary
Log Directory : /tmp/nz_migrate.20180322_155615.28562
Log File      : /tmp/nz_migrate.20180322_155615.28562/nz_migrate.output

Initiated From: SOURCE host
Top Level PID : 28562

SOURCE Version: 7.0     # of Dataslices: 46     Unloaded Via: nzsql
TARGET Version: 7.0     # of Dataslices: 184       Loaded Via: nzload
script version: 7.1

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


nz_migrate table 'Table_name'

.....processing table 1 of 1
.....migration process                              started at  2018-03-22 15:57:48
.....estimated # of records                                     165,915
.....nzload starting             ( thread 1 of 6 )
.....nzload starting             ( thread 2 of 6 )
.....nzload starting             ( thread 3 of 6 )
.....nzload starting             ( thread 4 of 6 )
.....nzload starting             ( thread 5 of 6 )
.....nzload starting             ( thread 6 of 6 )
.....unloading data              ( thread 1 of 6 )
.....unloading data              ( thread 2 of 6 )
.....unloading data              ( thread 3 of 6 )
.....unloading data              ( thread 4 of 6 )
.....unloading data              ( thread 5 of 6 )
.....unloading data              ( thread 6 of 6 )
.....data flowing.....
.....unload results              ( thread 3 of 6 )              INSERT 0 28965
.....unload finished             ( thread 3 of 6 )              elapsed seconds: 1
.....unload results              ( thread 2 of 6 )              INSERT 0 28825
.....unload finished             ( thread 2 of 6 )              elapsed seconds: 1
.....unload results              ( thread 1 of 6 )              INSERT 0 28572
.....unload finished             ( thread 1 of 6 )              elapsed seconds: 1
.....unload results              ( thread 6 of 6 )              INSERT 0 25393
.....unload finished             ( thread 6 of 6 )              elapsed seconds: 1
.....unload results              ( thread 4 of 6 )              INSERT 0 29011
.....unload finished             ( thread 4 of 6 )              elapsed seconds: 1
.....unload results              ( thread 5 of 6 )              INSERT 0 25149
.....unload finished             ( thread 5 of 6 )              elapsed seconds: 1
.....nzload finished             ( thread 3 of 6 )              elapsed seconds: 6
.....nzload successful           ( thread 3 of 6 )
.....nzload finished             ( thread 2 of 6 )              elapsed seconds: 6
.....nzload successful           ( thread 2 of 6 )
.....nzload finished             ( thread 1 of 6 )              elapsed seconds: 6
.....nzload finished             ( thread 6 of 6 )              elapsed seconds: 6
.....nzload successful           ( thread 1 of 6 )
.....nzload successful           ( thread 6 of 6 )
.....nzload finished             ( thread 4 of 6 )              elapsed seconds: 6
.....nzload successful           ( thread 4 of 6 )
.....nzload finished             ( thread 5 of 6 )              elapsed seconds: 6
.....nzload successful           ( thread 5 of 6 )
.....migration process                              ended at    2018-03-22 15:57:54
.....data flow finished
.....actual # of records unloaded                               165,915
.....
.....migration completed                                        TOTAL seconds: 6
.....
.....cksum process                                  started at  2018-03-22 15:57:54
.....cksum process                                  ended at    2018-03-22 15:57:54
.....confirmed cksum: 0.0 165915 Table_name
.....
.....cksum completed                                            TOTAL seconds: 0


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

nz_migrate finished on 2018-03-22 15:57:54

Migrating <sourcer_server_name>:"source_DB_name". --> <target_server_name>:"source_DB_name_NEW".

Data Format   : binary
Log Directory : /tmp/nz_migrate.20180322_155615.28562
Log File      : /tmp/nz_migrate.20180322_155615.28562/nz_migrate.output

     Total # of tables processed: 1
     Total # of records unloaded: 165,915

     # of seconds to unload/load the records : 6
     # of seconds to compare/cksum the tables: 0

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


Don't forget to run GENERATE [EXPRESS] STATISTICS on your tables!


################################################################################
################################################################################
################################################################################


Per your request ( -CreateTargetDatabase YES ), the target database
was created for you.  Additional details can be found here:

        Source DDL: /tmp/nz_migrate.20180322_155615.28562/source.ddl
     Target Output: /tmp/nz_migrate.20180322_155615.28562/target.ddl.out

################################################################################

 Counts Statements/Messages
======= ============================
     18 ALTER PROCEDURE
      2 ALTER SEQUENCE
    430 ALTER SYNONYM
     27 ALTER TABLE
      4 CREATE AGGREGATE
      1 CREATE DATABASE
    258 CREATE FUNCTION
      2 CREATE LIBRARY
     18 CREATE PROCEDURE
      2 CREATE SEQUENCE
    430 CREATE SYNONYM
     27 CREATE TABLE
     50 GRANT
      1 SET VARIABLE

################################################################################
################################################################################
################################################################################

The script completed successfully with no errors.

How to unload the data from Netezza to flat file

script to unload the data with specific delimiter  from Netezza database

nzsql -host <server_name> -u <username> -pw <password> -db <DBname>F 'delimiter' -A -t -c "select * from  <table_name>" -O  output_file_name.log 

steps to collect stats for Netezza database.

There is an utility to collect the stats on Netezza nz_genstats

z@server name:/export/home/nz> jobs -l
[1]+  9193 Running                 nohup /nz/support/bin/nz_genstats NZ_DB > NZ_DB_genstats_march30.log &
nz@server name:/export/home/nz> jobs -l
[1]+  9193 Running                 nohup /nz/support/bin/nz_genstats NZ_DB > NZ_DB_genstats_march30.log &
You have new mail in /var/spool/mail/nz
nz@server name:/export/home/nz> view NZ_DB_genstats_march30.log

This is how it looks on the database session

nz@server name:/export/home/nz> nzsession

ID      Type     User      Start Time              PID   Database Schema  State  Priority Name Client IP      Client PID Command
------- -------- --------- ----------------------- ----- -------- ------- ------ ------------- -------------- ---------- ------------------------
3046454 sql      ADMIN     30-Mar-18, 16:46:04 EDT 24892 NZ_DB ADMIN active normal             127.0.0.1      24891 GENERATE STATISTICS ON "

netezza database table restore using NZ_backup & NZ_restore

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)

All about Netezza database renaming & changing owner

Here is the step by step for Netezza DB renaming & changing ownership.


SYSTEM.ADMIN(ADMIN)=> select DATABASE,OWNER,CREATEDATE from _v_database where DATABASE='NZ_PRD1_NEW';
   DATABASE   | OWNER |     CREATEDATE
--------------+-------+---------------------
 NZ_PRD1_NEW | ADMIN | 2018-03-21 18:47:00
(1 row)

SYSTEM.ADMIN(ADMIN)=> alter database NZ_PRD1_NEW rename to NZ_PRD1_NEW1;
ALTER DATABASE


SYSTEM.ADMIN(ADMIN)=> select DATABASE,OWNER,CREATEDATE from _v_database where DATABASE='NZ_PRD1_NEW1';
   DATABASE    | OWNER |     CREATEDATE
---------------+-------+---------------------
 NZ_PRD1_NEW1 | ADMIN | 2018-03-21 18:47:00
(1 row)


SYSTEM.ADMIN(ADMIN)=> alter database NZ_PRD1_NEW1  owner to admin;
ALTER DATABASE

SYSTEM.ADMIN(ADMIN)=> select DATABASE,OWNER,CREATEDATE from _v_database where DATABASE='NZ_PRD1_NEW1';
   DATABASE    |  OWNER  |     CREATEDATE
---------------+---------+---------------------
 NZ_PRD1_NEW1 | ADMIN | 2018-03-21 18:47:00
(1 row)

SYSTEM.ADMIN(ADMIN)=> drop database NZ_PRD1_NEW1;
DROP DATABASE
SYSTEM.ADMIN(ADMIN)=> select DATABASE,OWNER,CREATEDATE from _v_database where DATABASE='NZ_PRD1_NEW1';
 DATABASE | OWNER | CREATEDATE
----------+-------+------------

(0 rows)