Consistency checking

The nz_md5_qsum utility is implemented as a UDF function that you must run on the SYSTEM database. The input for the utility is a SELECT statement.

SELECT <col_list> FROM <database>..<user_table>[WHERE ...]

The WHERE clause is optional. The user table that you supply to the SELECT query might have a maximum of 64 columns.

SELECT nz_md5_qsum ('SELECT * FROM test..table1');

The output of the nz_md5_qsum utility is a table with the following columns. Because it computes the checksum on the data set that is returned by the query, the nz_md5_qsum utility has a varying performance impact on the Netezza node when you invoke it for a large table.

1. Run the following command:

   tar xvf nz_md5_qsum.tar

2. As admin run the installQsum.pl script with a valid user name and password.
   If you want to install the nz_md5_qsum utility as a non-admin user, use one extra flag -fenced=1.
   Example:

   $  ./installQsum.pl -u testuser -pw password@123 -fenced=1
   Installing QSUM to database system
   Done

   To install this utility successfully as a non-admin user, you must have the following minimum permissions.

   YSTEM.ADMIN(ADMIN)=> \dpu testuser
                                               User object permissions for user 'TESTUSER'
    Database Name | Schema Name | Object Name | L S I U D T L A D B L G O E C R X A | D G U S T E X Q Y V M I B R C S H F A L P N S R
   ---------------+-------------+-------------+-------------------------------------+-------------------------------------------------
    GLOBAL        | GLOBAL      | TABLE       | X X                                 |
    GLOBAL        | GLOBAL      | PROCEDURE   |                           X         |
    GLOBAL        | GLOBAL      | GLOBAL      |                                     |         X X       X               X X   X
   (3 rows)
   Object Privileges
       (L)ist (S)elect (I)nsert (U)pdate (D)elete (T)runcate (L)ock
       (A)lter (D)rop a(B)ort (L)oad (G)enstats Gr(O)om (E)xecute
       Label-A(C)cess Label-(R)estrict Label-E(X)pand Execute-(A)s
   Administration Privilege
       (D)atabase (G)roup/Role (U)ser (S)chema (T)able T(E)mp E(X)ternal
       Se(Q)uence S(Y)nonym (V)iew (M)aterialized View (I)ndex (B)ackup
       (R)estore va(C)uum (S)ystem (H)ardware (F)unction (A)ggregate
       (L)ibrary (P)rocedure U(N)fence (S)ecurity Scheduler (R)ule

   Use this SQL commands to grant all these permissions:

   \c
   drop user testuser;
   create user testuser with password 'password1';
   
   grant create table  to testuser;
   grant create function to testuser;
   grant create procedure to testuser;
   grant create temp table to testuser;
   grant create view to testuser;
   grant create aggregate to testuser;
   grant execute on procedure to testuser;
   grant list, select on table to testuser;

Example:

\c
drop database db1;
create database db1;

\c db1
create table table1(c1 int, c2 varchar(10));
insert into table1 values(1, 'asdf');

\c system testuser password1
SELECT NZ_MD5_QSUM('SELECT * FROM db1..table1');

Output:

SYSTEM.ADMIN(TESTUSER)=> SELECT NZ_MD5_QSUM('SELECT * FROM db1..table1');
             DATASUM              |            SCHEMASUM             | NUMROWS | NUMCOLS
----------------------------------+----------------------------------+---------+---------
 0D030E080C00090104080D0605040D0E | 060E0D09090000000808070D0A070303 |       1 |       2
(1 row)