Netezza NPS Release 7.2.1.3 summary of new features

---

The  summary of note worthy features of new Netezza NPS Release 7.2.1.3 (as of Feb 2017)

Refer do IBM Release notes: 

• New features and changes in Release 7.2.1.3

New feature	Why it's good ?	Examples
In-memory external tables	Reduced catalog growth and contention.
Table-oriented zone maps	Improved incremental loads to a table
New nzload -merge option	Less steps in ETL
New SQL commands: CREATE TABLE IF NOT EXISTS DROP TABLE IF EXISTS MERGE	More tools in developers toolkit
Reduced time requirements for major upgrades	Shorter system downtime

---

New features in General Purpose Scripts Toolkit Version: 7.2 (2016-07-04)  in /nz/support/bin/***

Script Name	New Option(s)	Comments
nz_csv		New script to dump out tables in in CSV format
nz_ddl_all_grants		New script to dump out all GRANT statements across all databases
nz_ddl_diff	-ignoreQuotes	Ignore quotes and case in object names/DDL
nz_genstats	-default	Have the system decide the default type of statistics to be generated
nz_groom	-scan+records -brief	Perform a table scan + record level groom of the table (if needed)
		Limit the output to only tables that are considered "groom worthy"
nz_health		Includes output from the nznpssysrevs command
		Includes output about the frequency/usage of the nz_*** scripts
nz_migrate	-fillRecord -truncString	Treat missing trailing input fields as null
		Truncate any string value that exceeds its declared storage
nz_plan		After each table ScanNode, show what is done with that data set
nz_rerandomize		New script to re-randomize the rows in a randomly distributed table
nz_scan_table_extents		New script to scan 1 table ... 1 dataslice ... 1 extent at a time
nz_zonemap		Include additional information with the existing "-info" option

---

Netezza: nzhealthcheck System Health Check tool

---

With Netezza NPS release 7.2  the new version of nzhealthcheck 2.3.1.2 tool has introduced a number of changes:

1)  creates 26 views and 1 table in a target database. (See list of table and views here) 

          TIP: Change target database by setting NZ_DATABASE variable if needed. 

2)  loads data into the target table leaving nzlog and data files behing that clutter /tmp directory. (See session example) 

          TIP: Schedule periodic /tmp directory cleanup :

          find /tmp -maxdepth 1 -name "tmp.*" -mtime +2 -print 2>/dev/null | xargs rm -rf 
    

             (step-by-step explanation on how this one-liner works)

Rules document verified by  nzhealthcheck 2.3.1.2 :

• nzhealthcheck 2.3.1.2-rules-doc.pdf

---

nzhealthcheck usage examples: 

/nz/support/bin/adm/nzhealthcheck/nzhealthcheck --help

Usage: nzhealthcheck [OPTION...] [sysinfo] [minisysinfo]

Netezza System Health Check

        Netezza System Health Check is a tool that scans the Netezza appliance

        for hardware and software issues. The results appear on-screen in a text

        report that lists the issues found and describes how that issue impacts

        NPS operations. The report also offers guidance to fix the issue.

Notes:

        To run the tool, log in as the nz user and run the nzhealthcheck command.

        No parameters are required. The Netezza system can be in any operational

        state, although some issues, especially related to SPU components, are

        found only when the system is in the Online state.

  sysinfo                    Produce Sysinfo report

  minisysinfo                Produce MiniSysinfo report

  -a, --detail               Show all rule results

  -S, --standAlone           Run nzhealthcheck in stand-alone mode

  -?, --help                 Give this help list

      --usage                Give a short usage message

  -V, -v, --version          Print version

 /nz/support/bin/adm/nzhealthcheck/nzhealthcheck --version

2.3.1.2

---

 /nz/support/bin/adm/nzhealthcheck/nzhealthcheck --detail

Please run with -S as this is nzhealthcheck standalone version

 /nz/support/bin/adm/nzhealthcheck/nzhealthcheck -S --detail

Netezza System Health Check 2.3.1.2

Collecting monitoring data

Please provide root password when prompted to enable host monitoring or hit ^D to skip

Password:

Evaluating troubleshooting rules

... full output in attachment ... 

• Netezza_System_Health_Check_Report_DETAIL.txt

---

/nz/support/bin/adm/nzhealthcheck/nzhealthcheck -S minisysinfo

Netezza System Health Check 2.3.1.2

 + Product     : IBM PureData System for Analytics N1001-005

 + Model       : P50X_A

 + HPF         : 5.3.4

 + FDT         : 4.1.1.1

 + NPS         : 7.2.1.3-P4 [Build 49731]

 + NPS State   : online

 + MTM(s)      : MTM has not been set

 + NzId        :

 + NZ Owner    : nz

 + OS          : Red Hat Enterprise Linux Server release 5.10 (Tikanga)

 + Kernel      : 2.6.18-371.9.1.el5

 + HealthCheck : 2.3.1.2 [20170217043257]

 + Hostname    : svr

 + NPS  Up Time: 6 days, 21 hrs, 22 mins, 15 secs

 + Host Up Time: Host1 : 581 days 8 hours 19 minutes 27 seconds

 + Host Up Time: Host2 : 581 days 8 hours 17 minutes 34 seconds

All done

---

/nz/support/bin/adm/nzhealthcheck/nzhealthcheck -S sysinfo

Netezza System Health Check 2.3.1.2

Collecting monitoring data

Please provide root password when prompted to enable host monitoring or hit ^D to skip

Password:

Preparing SysInfo Report

- Frontend Hosts Utilization and Statistics

--- Host DIMMs

--- Host Fans

--- Host Power

--- Host Power Supply

--- Host SAS Controllers

--- Host SAS Controllers Batteries

--- Host Disks

--- Host CPU Utilization

--- Host Memory Utilization

--- Host Network Interfaces Configuration

--- Host Network Interfaces - RX Stats

--- Host Network Interfaces - TX Stats

--- Host Cluster State

--- crmnode

--- drbd

--- Host Filesystem Utilization

--- Host Timeshift

--- Host Uptime

--- Host System

- Open files on host

- Chassis Network Subsystem State and Usage Statistics

--- Ports Configuration

--- Ports RX Stats

--- Ports TX Stats

--- Management Ports

- Management Network Subsystem State and Usage Statistics

--- Ports Configuration

--- Ports RX Stats

--- Ports TX Stats

--- Management Ports

- Chassis Components Configuration and State

--- Overall System Status

--- Blades

--- MMs

--- Fans

--- Blowers

--- PWRs

- Blade detailed configuration and usage statistics

--- CPU Information

--- Memory Usage (all values in kB)

--- Network Interfaces Configuration

--- Network Interfaces - RX Stats

--- Network Interfaces - TX Stats

--- SDR (reported via ipmitool)

- RPC and Outlets Configuration

- NPS SPUs

- NPS DACs

- NPS FPGAs

- Section Disk Enclosures

--- Disk Enclosure Slots

--- Disk Enclosure Fans

--- Disk Enclosure Management Modules

--- Disk Enclosure Power Units

--- Disk Enclosure Voltage

--- Disk Enclosure Temperature

- Disks in Enclosures

- Disks Logical Partitions

- SAS PHYs of ESMs

- SAS PHYs of HBAs

- SAS PHYs

- Switches Zonemaps

- SAS PHYs of Switches

- SAS PHYs Switches Errors

- SPU disk paths

--- Paths

- SAS Switch errors

- NPS version and state

- NPS Inventory (nzhw)

- NPS Data Slices (nzds)

- /nz/kit/bin/nzds -regenstatus results

- NPS Catalog Size

- NPS Catalog Files larger that 1M

- /nzlocal/scripts/hpf_health results

- Environment variables

- /opt/nz/fdt/sys_rev_check results

- /nz/kit/bin/nzstats results

- /opt/nz-hwsupport/pts/pts-check.pl results

- /nzlocal/scripts/hpf_ping.sh results

- Results of concheck

- Disk error correction delay

- Number of operations transferred by disks compared to other disks present in appliance

- Disk resets

- CallHome information

Sysinfo Report stored in /nz/support-IBM_Netezza-7.2.1.3.P4-170217-0432/bin/adm/nzhealthcheck/bin/..//Netezza_System_Health_Check_Sysinfo_Report.log

All done

• Netezza_System_Health_Check_Sysinfo_Report.txt

---

Views and a table create by nzhealthcheck 2.3.1.2.  TIP: Create reminder comments to differentiate these objects from the rest of the clutter. 

--- Table ---

• V_DISK_DOM

--- Views ---

• V_DFPGA_ERRORS

• V_DFPGA_ERRORS_BY_DATASLICE
• V_DFPGA_ERRORS_BY_DISK
• V_DFPGA_ERRORS_BY_ENCL
• V_DFPGA_ERRORS_BY_ERROR
• V_DFPGA_ERRORS_BY_LBA
• V_DFPGA_ERRORS_BY_LOCATION
• V_DFPGA_ERRORS_BY_SPA
• V_DFPGA_ERRORS_BY_SPU
• V_DFPGA_ERRORS_BY_SPU_DATASLICE
• V_DFPGA_ERRORS_BY_TABLE
• V_DFPGA_ERRORS_RETURNED
• V_DIR_BAD_DATA
• V_DIR_DUPLICATE
• V_DIR_DUPLICATE_KEY
• V_DISK_SMARTATTR_BYNAME
• V_DISK_SMARTATTR_DETAIL
• V_PART_BAD_DATA
• V_PART_DUPLICATE
• V_PART_DUPLICATE_KEY
• V_SCSI_DISK
• V_SPU_ORPHAN_TABLE
• V_ZMAP_BAD_DATA
• V_ZMAP_DUPLICATE
• V_ZMAP_DUPLICATE_KEY

• V_ZMAP_ORPHAN_EXTENT

DDL definition: 

• NZHEALTHCHECK_VIEWS_N_TABLE.ddl.zip

---

Rules can be manually modified to suffice the individual requirements. See Rules PDF document attached above. 

Location: /nz/support-IBM_Netezza-7.2.0.5.P1-150715-2158/bin/adm/nzhealthcheck/

---

REFERENCE: 

 System Health Check tool  (PureData System for Analytics 7.2.1)

Netezza: MERGE SQL statement

Netezza NPS release 7.2.1 comes with new MERGE SQL statement. Here are some usage examples.

• To build tables for test environment follow the link. 

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

-----  Using heap table ----

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

MERGE INTO my_target_table a

     USING my_source_table b

ON   a.UID   =  b.UID

WHEN MATCHED  AND a.DATAFIELD <> b.DATAFIELD THEN

       UPDATE SET a.DATAFIELD = b.DATAFIELD, a.UPDATED = CURRENT_TIMESTAMP

WHEN NOT MATCHED  THEN

       INSERT VALUES (b.UID,B.DATAFIELD,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)

;

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

-----  Using TET (transient external table) ---

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

MERGE INTO my_target_table A

     USING (SELECT * FROM external '/tmp/data.csv'

                                  ( UID       INT4

                                  , DATAFIELD CHARACTER VARYING(64))

                     using (Delim ',' SkipRows 0 MaxErrors 300 Logdir '/tmp'  )) B

ON   a.UID   =  b.UID

WHEN MATCHED  AND a.DATAFIELD <> b.DATAFIELD THEN

       UPDATE SET a.DATAFIELD = b.DATAFIELD ,a.UPDATED = CURRENT_TIMESTAMP

WHEN NOT MATCHED  THEN

       INSERT VALUES (b.UID,B.DATAFIELD,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)

;

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

-----  Using intermediate TEMP table ---

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

create temp table TEMP_source_table

as select * from external '/tmp/data.csv' (UID INT4, DATAFIELD CHARACTER VARYING(64)) 

   using (Delim ',' SkipRows 0 MaxErrors 300 Logdir '/tmp')

;

MERGE INTO my_target_table   A

     USING TEMP_source_table B

ON   a.UID   =  b.UID

WHEN MATCHED AND a.DATAFIELD <> b.DATAFIELD THEN

       UPDATE SET   a.DATAFIELD = b.DATAFIELD,a.UPDATED = CURRENT_TIMESTAMP

WHEN NOT MATCHED  THEN

       INSERT VALUES (b.UID,B.DATAFIELD,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)

;

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

Notice, Aginity Workbench 4.8.0 report only INSERTED records statistics, but UPDATED.  The nzsql reports both UPDATED and INSERTED records affected. 

REFER: 

• New features and changes in Release 7.2.1 - IBM

• MERGE - IBM

Oracle SQL Developer: setup tunnel session via gataway server

Problem:

You need to setup Oracle SQL Developer to have GUI interface for database development. Unfortunately, you don't have direct access to the Oracle database due to security restrictions. Instead, you are confined using a gateway machine to access Oracle database indirectly. 

Connection credentials: 

• Gateway server IP:           10.5.99.199
• Gateway server SSH:           mysshuser
• Oracle database user:           myoracleuser
• Oracle TNS connect string:  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.200)(PORT=1521))(CONNECT_DATA=(SID=mycorpdb))))

Required result: 

Seamlessly use Oracle SQL Developer from you personal laptop accessing Oracle database via middle man gateway server. 

Oracle SQL Developer setup steps:

1. In the main menu follow View -> SSH:  adding new SSH host: 

2. Enter hosts credentials:

3. Set Database connection. Reference newly created SSH host with Port Forwarding:

Netezza: find groups without users

List "empty" database groups with no users associated with them. Those groups can be safely dropped:

select * from _v_group

where GROUPNAME not in (select GROUPNAME from _v_groupusers)

  and grorsgpercent=0 --- exclude resource Groups

---

Get DDL for group creation. 

nz_ddl_group GRP_MYGROUP

\echo

\echo *****  Creating group:  "GRP_MYGROUP "

CREATE GROUP GRP_MYGROUP WITH QUERYTIMEOUT 30 DEFPRIORITY NORMAL MAXPRIORITY NORMAL RESOURCE MINIMUM 1 RESOURCE MAXIMUM 25 ;

List users in a group: 

nz_get_group_users GRP_MYGROUP

MYUSER_1

MYUSER_2

...

Get DDLfor group(s) permissions. This is handy to keep in case you need to recreate the group fast.

nz_ddl_grant_group         | grep -i GRP_MYGROUP

nz_ddl_grant_group DBASE1  | grep -i GRP_MYGROUP

Same as above only run for all available database in one shot: 

nzsql -l -A -r -t 

     | cut -d'|'-f1

          | xargs -I DB nz_ddl_grant_group DB

               | grep -i GRP_MYGROUP

                    | tee -a ~/$(date+'%Y%m%d%H%M%S')_$(hostname)_grant_GRP_MYGROUP.ddl.sql

.

REFERENCE: 

     The commands used can be found in post: 

               Netezza: General Purpose Scripts for use with NPS Versions 4.x, 5.x, 6.x and 7.x

Netezza: data transfer speed Fluid Query JDBC v.s. nz_migrate

TEST CASE:

Compare how fast data can be copied between two Netezza appliances: 

1. Using Netezza Fluid Query connection which is Java JDBC driver
2. Using nz_migrate, which is nzbackup/nzrestore behing the hood

RESULT:
nz_migrate in ASCII transfer mode is 20 times faster than JDBC, and 80 times faster in BINARY mode. 

DETAILS: 

Copying 1 GB table from one system to another:

Netezza: find ALTERED tables that need GROOM ... VERSIONS

Also see related: Netezza: ALTER TABLE to MODIFY a column datatype


In Aginity run SQL to list all ALTERED tables that need to be GROOM ... VERSIONS :

SELECT         

        tab.database as "DATABASE"  

       ,tab.schema   as "SCHEMA"                

       ,MAX (  

              CASE WHEN tab.objclass = 4910               THEN SUBSTR(tab.objname, 3)                        

                   WHEN tab.objclass in (4951,4959,4963)  THEN NULL                         

                   ELSE   tab.objname                    

            END ) AS "TABLENAME"

       ,TO_CHAR (nvl(SUM(used_bytes),0), '999,999,999,999,999,999')  AS "SIZE (BYTES)"               

       ,count(distinct(decode(objclass,4959,objid,4963,objid,null))) AS "# OF VERSIONS"

FROM            _V_OBJ_RELATION_XDB   AS tab        

left outer join _V_SYS_OBJECT_DSLICE_INFO on

(     tab.objid = _V_SYS_OBJECT_DSLICE_INFO.tblid  

and  _V_SYS_OBJECT_DSLICE_INFO.tblid > 200000         )

WHERE tab.objclass in (4905,4910,4940,4959,4951,4953,4961,4963)

  and tab.objid > 200000

GROUP BY "DATABASE",  "SCHEMA", tab.visibleid

HAVING   "# OF VERSIONS" >= 2

ORDER BY "DATABASE", "SCHEMA", "TABLENAME";

---

At Netezza host run the script to identify the altered tables: 

[nz@netezza ~]$ /nz/support/contrib/bin/nz_altered_tables

# Of Versioned Tables         18

     Total # Of Versions      38

  Database   |  Schema  |             Table Name       | Size (Bytes)       | # Of Versions

-------------+----------+------------------------------+--------------------+---------------

 TESTDB      | MYUSERSD | FILTER_DATA                  |         16,515,072 |      2

 TESTDB      | MYUSERSD | MASTER_DATA_TESTS            |          7,864,320 |      3

 TESTDB      | MYUSERSD | PREMIUM_TEST_TABLE_DATA      |          6,815,744 |      2

 TESTDB      | MYUSERSD | REPORT_MASTER                |         25,427,968 |      2

 TESTDB      | MYUSERSD | UTILI_TEST_TABLE_DATA        |          6,291,456 |      2

 TESTDB      | MYUSERSD | WORK_TEST_TABLE_DATA         |                  0 |      2

 TSTDB2      | MYUSERSD | AUDIT_IDW_DATALOAD           |         31,195,136 |      2

 TSTDB2      | MYUSERSD | ANOTHER_SUBSET_FILTER_DATA   |         40,501,248 |      2

 TSTDB2      | MYUSERSD | MASTER_MASTER_DATA_TESTS     |          5,898,240 |      3

 TESTDB5     | MYUSERSD | ORDER_ITEMS                  |    110,182,924,288 |      2

 TESTDB5     | MYUSERSD | LARGE_DATA_DIM               |          6,422,528 |      2

 TESTDB5     | MYUSERSD | ORGANIZATION_DIM             |         62,914,560 |      2

 TESTDB67    | ADMIN    | ALLEGED_DATA_SUBSET          |                  0 |      2

 TESTDB67    | ADMIN    | AUDIT_DOWNLOAD               |          1,703,936 |      2

 TESTDB67    | ADMIN    | TESTDATA_TABLE_1             |         30,146,560 |      2

 TESTDB67    | ADMIN    | TESTDATA_TABLE_2             |                  0 |      2

 TESTDB67    | ADMIN    | TESTDATA_TABLE_3             |                  0 |      2

 TESTDB67    | ADMIN    | TESTDATA_TABLE_4             |                  0 |      2

(18 rows)

---

At Netetezza host run the script to GROOM the altered tables:

[nz@netezza ~]$ /nz/support/contrib/bin/nz_altered_tables -groom

# Of Versioned Tables         18

     Total # Of Versions      38

  Database   |  Schema  |             Table Name       | Size (Bytes)       | # Of Versions

-------------+----------+------------------------------+--------------------+---------------

 TESTDB      | MYUSERSD | FILTER_DATA                  |         16,515,072 |      2

 TESTDB      | MYUSERSD | MASTER_DATA_TESTS            |          7,864,320 |      3

 TESTDB      | MYUSERSD | PREMIUM_TEST_TABLE_DATA      |          6,815,744 |      2

 TESTDB      | MYUSERSD | REPORT_MASTER                |         25,427,968 |      2

 TESTDB      | MYUSERSD | UTILI_TEST_TABLE_DATA        |          6,291,456 |      2

 TESTDB      | MYUSERSD | WORK_TEST_TABLE_DATA         |                  0 |      2

 TSTDB2      | MYUSERSD | AUDIT_IDW_DATALOAD           |         31,195,136 |      2

 TSTDB2      | MYUSERSD | ANOTHER_SUBSET_FILTER_DATA   |         40,501,248 |      2

 TSTDB2      | MYUSERSD | MASTER_MASTER_DATA_TESTS     |          5,898,240 |      3

 TESTDB5     | MYUSERSD | ORDER_ITEMS                  |    110,182,924,288 |      2

 TESTDB5     | MYUSERSD | LARGE_DATA_DIM               |          6,422,528 |      2

 TESTDB5     | MYUSERSD | ORGANIZATION_DIM             |         62,914,560 |      2

 TESTDB67    | ADMIN    | ALLEGED_DATA_SUBSET          |                  0 |      2

 TESTDB67    | ADMIN    | AUDIT_DOWNLOAD               |          1,703,936 |      2

 TESTDB67    | ADMIN    | TESTDATA_TABLE_1             |         30,146,560 |      2

 TESTDB67    | ADMIN    | TESTDATA_TABLE_2             |                  0 |      2

 TESTDB67    | ADMIN    | TESTDATA_TABLE_3             |                  0 |      2

 TESTDB67    | ADMIN    | TESTDATA_TABLE_4             |                  0 |      2

(18 rows)

A 'GROOM TABLE <tablename> VERSIONS;' will now be performed on each of the above tables.

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

Grooming TESTDB.MYUSERSD.FILTER_DATA    @ 2016-10-24 18:01:53

NOTICE:  Groom will not purge records deleted after transactions that started after transaction 0xcaf2a1, due to the backup at 2016-01-30 00:36:49.

NOTICE:  If this process is interrupted please either repeat GROOM VERSIONS or issue 'GENERATE STATISTICS ON "FILTER_DATA"'

NOTICE:  Groom processed 0 pages; purged 0 records; scan size unchanged; table size unchanged.

GROOM VERSIONS

Elapsed time: 0m1.769s

Grooming TESTDB.MYUSERSD.MASTER_DATA_TESTS      @ 2016-10-24 18:01:54

NOTICE:  Groom will not purge records deleted after transactions that started after transaction 0xcaf2a1, due to the backup at 2016-01-30 00:36:49.

NOTICE:  If this process is interrupted please either repeat GROOM VERSIONS or issue 'GENERATE STATISTICS ON "MASTER_DATA_TESTS"'

NOTICE:  Groom processed 35 pages; purged 0 records; scan size unchanged; table size shrunk by 1 extents.

GROOM VERSIONS

Elapsed time: 0m3.603s

Grooming TESTDB.MYUSERSD.PREMIUM_TEST_TABLE_DATA      @ 2016-10-24 18:01:58

NOTICE:  Groom will not purge records deleted after transactions that started after transaction 0xcaf2a1, due to the backup at 2016-01-30 00:36:49.

NOTICE:  If this process is interrupted please either repeat GROOM VERSIONS or issue 'GENERATE STATISTICS ON "PREMIUM_TEST_TABLE_DATA"'

NOTICE:  Groom processed 0 pages; purged 0 records; scan size unchanged; table size unchanged.

GROOM VERSIONS

Elapsed time: 0m2.188s

Grooming TESTDB.MYUSERSD.REPORT_MASTER     @ 2016-10-24 18:02:00

NOTICE:  Groom will not purge records deleted after transactions that started after transaction 0xcaf2a1, due to the backup at 2016-01-30 00:36:49.

NOTICE:  If this process is interrupted please either repeat GROOM VERSIONS or issue 'GENERATE STATISTICS ON "REPORT_MASTER"'

NOTICE:  Groom processed 106 pages; purged 0 records; scan size shrunk by 2 pages; table size shrunk by 2 extents.

GROOM VERSIONS

Elapsed time: 0m3.110s

....

---

Refer NPS 7.2 documentation:  GROOM TABLE and  ALTER TABLE