Netezza: list users that own objects

PROBLEM:
Dropping a database user renders error:

ERROR:  DROP USER: user "JOHN.DOUGH" owns objects, cannot be removed

What objects does user own?

SOLUTION:

SELECT distinct OWNER, THE_OBJECT_TYPE, OBJNAME, DBNAME
FROM (
   SELECT
               usename AS OWNER,
               (CASE   WHEN objclass = 4902 then 'database'
                       WHEN objclass = 4903 then 'group'
                       WHEN objclass = 4904 then 'user'       END ) AS the_object_type,
               objname, null as dbname 
       FROM
               _t_object,
               _t_user
       WHERE   objowner = usesysid
        AND    objclass IN (4902, 4903, 4904)
        AND    objdb = 0
        AND    objowner <> (SELECT usesysid FROM _t_user WHERE UPPER(usename) = 'ADMIN')
   UNION ALL
       SELECT
               _t_user.usename AS OWNER,
               lower(the_class.objname) AS the_object_type,
               the_object.objname    as objname,
               the_database.objname  AS dbname
       FROM
               _t_object as the_object
                       LEFT OUTER JOIN _t_object the_class ON
                               (the_object.objclass = the_class.objid),
               _t_object the_database,
               _t_user
       WHERE   the_object.objowner = _t_user.usesysid
        AND    the_object.objclass NOT IN (4902, 4903, 4904)
        AND    the_object.objdb = the_database.objid
        AND    the_object.objowner <> (SELECT usesysid FROM _t_user WHERE UPPER(usename) = 'ADMIN')
) tab
WHERE 1=1
  and OWNER in 
     ( 
         'JOHN.DOUGH'
        ,'SUSAN.SMITH'
        ,'JUAN.RODRIGES'
     )
ORDER BY 1,2,3
;

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

Netezza: find object dependencies

SQL query to search for object name referenced in current_catalog database views and procedures source code definitions.  

---

---- Find object name (e.g. string) in views and stored procedures ---

select  (select DECODE (VAL,'sdw1','NZ1','NZ2') from _t_environ where NAME = 'HOSTNAME')|| ': HOST' AS HOST

        , '*'||p.str||'*'    AS SEARCH_STRING

        ,s.objtype

        , current_catalog as database

        ,s.objname

        ,s.sourcecode

        ,s.createdate as objcreated

from     (select 'Proc' as objtype,proceduresignature as objname, proceduresource as sourcecode, createdate from _v_procedure

union all select 'View' as objtype,viewname           as objname, definition      as sourcecode, createdate from _v_view) as s

CROSS JOIN (SELECT  'TABLE2SEARCH4_1'             as str

  union all SELECT 'VIEW2SEARCH4_1'

  union all SELECT 'TABLE2SEARCH4_2'

  union all SELECT 'VIEW2SEARCH4_2' ) p

where SQLX..REGEXP_LIKE(s.sourcecode,p.str,'i')

;

---

Extract database VIEWS referencing other views and tables.  

--- extract VIEWS definitions into a flat file, then parse file searching for table name

nz_view_references DBNAME > ~/$(date +'%Y%m%d%H%M%S')_$(hostname)_nz_view_references_DBNAME.txt &

egrep -ri "STR2SEARCH"  $(date +'%Y%m%d')*_$(hostname _sdw1_nz_view_references_*.txt

---

Extract entire appliance [database] DDL for all objects including views and procedures:

/nz/ support/bin/nz_ddl [DBNAME] > ~/$(date +'%Y%m%d%H%M%S' )_$(hostname)_nz_ddl.sql &

Netezza SQL: UNIQUE constraint clause

See related post: 

• Netezza SQL: PRIMARY KEY clause 
• Netezza SQL: FOREIGN KEY  clause

Netezza SQL compiles with ANSI SQL-92 standard implementing Postgrees SQL. The ANSI SQL-92 standard supported by most relational databases on the market. 

Netezza supports referential integrity (e.g. definitions for foreign, primary, and unique keys) as part of SQL-92 standard requirement. However, the keys are not enforced. This means that you can define the keys in SQL but they won't make any affect on the data. Nothing would prevent to violate referential integrity like inserting non matching values in child table that suppose to match to parent table. 

Let's experiment with UNIQUE KEY constraint:

/* Start time 10/15/2013 6:15:05 PM, end time 10/15/2013 6:15:05 PM. */

/* Duration 0.0270027 sec. */

create table test2delete (

        col1 smallint

       ,col3 varchar(30 )

);

/*

      NOTICE:  unique key constraints not enforced  <-- Netezza complains, but proceeds.

 */

/* Start time 10/15/2013 6:15:05 PM, end time 10/15/2013 6:15:06 PM. */

/* Duration 0.0370037 sec. */

alter table test2delete add constraint uk_test2delete unique (col1);

/* Start time 10/15/2013 6:15:06 PM, end time 10/15/2013 6:15:06 PM. */

/* Duration 0.2890289 sec. */

/* Records Affected: 1. */

insert into test2delete values (1,'mystring');

/* Start time 10/15/2013 6:15:06 PM, end time 10/15/2013 6:15:06 PM. */

/* Duration 0.0760076 sec. */

/* Records Affected: 1. */

insert into test2delete values (1,'mystring');

/* Start time 10/15/2013 6:15:06 PM, end time 10/15/2013 6:15:06 PM. */

/* Duration 0.0770077 sec. */

/* Records Affected: 1. */

insert into test2delete values (1,'mystring');

<-- All DUPLICATES inserted !!!!

---

To conclude, Netezza NPS 7.2 documentation emphasizes: 

Note: The system permits and maintains primary key, default, foreign key, unique, and references. Because IBM® Netezza® does not support constraint checking and referential integrity, you must ensure your own constraint checking and referential integrity.

Related documentation:   Column and table constraints

Netezza SQL: FOREIGN KEY clause

See related post: 

• Netezza SQL: PRIMARY KEY clause
• Netezza SQL: UNIQUE constraint clause

Netezza SQL compiles with ANSI SQL-92 standard implementing Postgrees SQL. The ANSI SQL-92 standard supported by most relational databases on the market. 

Netezza supports referential integrity (e.g. definitions for foreign, primary, and unique keys) as part of SQL-92 standard requirement. However, the keys are not enforced. This means that you can define the keys in SQL but they won't make any affect on the data. Nothing would prevent to violate referential integrity like inserting non matching values in child table that suppose to match to parent table. 

Let's experiment with FOREIGN KEY constraint:

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

/*

NOTICE:  foreign key constraints not enforced       <-- Netezza complains, but proceeds.

 */

/* Start time 4/25/2013 6:21:32 PM, end time 4/25/2013 6:21:32 PM. */

/* Duration 0.078125 sec. */

CREATE TABLE table2ref

(

        col1    smallint NOT NULL

       ,colref2 smallint

       ,col3      varchar(30 )

  ,CONSTRAINT fk_column_colref2          <-- THIS IS NAME OF REF CONSTRAINT

   FOREIGN KEY (colref2) 

   REFERENCES test2delete (col1)   <-- the ref table "test2delete" must exist. 

);                            if table "test2delete" does not exist SQL will fail.

/* Start time 4/25/2013 6:21:32 PM, end time 4/25/2013 6:21:37 PM. */

/* Duration 4.53125 sec. */                        

/* Records Affected: 1. */

insert into table2ref values (3,12345,'reftabstring');   <-- THIS IS NON-EXISTING KEY "12345"

                                  in table "test2delete.

                                  Nothing stops inserting non-matching value "12345"

---

To conclude, Netezza NPS 7.2 documentation emphasizes: 

Note: The system permits and maintains primary key, default, foreign key, unique, and references. Because IBM® Netezza® does not support constraint checking and referential integrity, you must ensure your own constraint checking and referential integrity.

Related documentation:   Column and table constraints

Netezza: nzbackup & nzrestore on NPS 7.2

See related post: Netezza: nzbackup & nzrestore on NPS 7.0.

QUESTION: 

What parallelism factor to pick running nzbackup with -streams option for database backup?

What parallelism factor to pick running  nzrestore with -streams option for database restore?

ANSWER:

Pick -streams AUTO  for both nzbackup and nzrestore .   

Advise: I would recommended to test running multiple nzbackup -streams AUTO concurrently in shell script. This may shrink overall backup time by saturating all available network bandwidth. This option yet to be tested with external backup storage.  Stay tuned ... 

---

EXPLANATION: 

This empirical test was inspired by poorly performing nzrestore from milti-stream backupset at NPS 7.0 and later releases.  At October 2013 it was proven that  nzrestore recovery time degrades by factor of 2 with each uptick of streams in source nzbackup dataset. For more details about this experiments see post Netezza: nzbackup & nzrestore on NPS 7.0.

Starting from Netezza NPS 7.2 database restore can be done in multi-stream mode. IBM did a great job improving nzbackup and nzrestore utilities.  This test proves that new milti-stream operations show highly improved performance. 

TEST CASES:

The matrix below explains various combination running  nzbackup followed by nzrestore with -streams option. The test was conducted using host server disk for target work space. The database used for testing has size of 500GB with three dozen of tables consisting of mix datatypes columns. 

RESULT: 

The greener, the better.  The nzbackup -streams AUTO exhibit the best backup performance: "23 min".  The recovery performance running nzrestore -streams AUTO is very close to backup speed: "33 min".  To validate the data test AUTO vs. AUTO was repeated 8 times showing very close results. The average number was derived for both backup and restore: 23 min / 33 min.  

Related documentation:  Database backup and restore

Aginity: visualize processing skew for running Netezza query

Aginity provides visualization tool to identify query processing skew.

Execute query as: 

You might also be interested to read how: Aginity: keep connection open to preserve TEMP tables for Netezza session

Netezza: enable CROSS JOIN control set ENABLE_CROSSJOIN_CONTROL

Enable CROSS JOIN control feature by adding enable_crossjoin_control parameter to /nz/data/postgresql.conf file:

###

### In the file: /nz/data/postgresql.conf

###

enable_crossjoin_control = true

---

BEFORE NPS REBOOT: 

/* NOTICE:  Cross join control not enabled, at runtime option will be ignored. */

/* Start time 5/11/2015 4:55:02 PM, end time 5/11/2015 4:55:02 PM. */

/* Duration 0.0312002 sec. */

alter user USERNAME  allow cross join FALSE;

---

AFTER NPS REBOOT: 

Testing with different user setting for ALLOW CROSS JOIN . See: ALTER USER.

Default: _V_USER.CROSS_JOIN_ALLOWED IS NULL	/* ERROR:  Execution of implicit cross joins is not permitted */ SELECT count (* ) FROM TABLE_1, TABLE _2; /* ERROR:  Execution of explicit cross joins is not permitted */ SELECT count (* ) FROM TABLE_1 cross join  TABLE_2;
alter user MYUSER allow cross join FALSE;	/* ERROR:  Execution of implicit cross joins is not permitted */ SELECT count (* ) FROM TABLE_1, TABLE_2; /* ERROR:  Execution of explicit cross joins is not permitted */ SELECT count (* ) FROM TABLE_1 cross join  TABLE_2;
alter user MYUSER allow cross join TRUE;	/* ERROR:  Execution of implicit cross joins is not permitted */ SELECT count (* ) FROM TABLE_1, TABLE_2; /* Success. Ok !*/ SELECT count (* ) FROM TABLE_1 cross join  TABLE_2;

  

---

SET CONTROL AT DATABASE GROUP AND USER LEVEL:

Replace NULL with TRUE|FALSE:   ALTER GROUP xyz ALLOW CROSS JOIN TRUE;  If still left NULL, the user defaults to FALSE.

  

--- fine grained control at GROUP level --- select groupname, cross_joins_allowed  from _v_group;	--- fine username control at USER level --- select username, cross_joins_allowed  from SYSTEM ..v_user;