Netezza Versioned Tables

Versioned tables come about as a result of doing an
               ALTER TABLE <tablename> [ADD|DROP] COLUMN ...

          This results in multiple data stores for the table.  When you go to query
          the table, NPS must recombine the separate data stores back into a single
          entity.  This action will be performed automatically and on-the-fly.  But
          it does result in additional query overhead.  Therefore, it is a best
          practice to reconstitute the table as soon as practical by doing a

               GROOM TABLE <tablename> VERSIONS;

          Notes:  The maximum number of table versions allowed is 4 (which means you
                  can perform at most three ALTER TABLE commands before doing a GROOM,
                  since this number includes the original table version itself)


SELECT  the_table.database as "Database",
        MAX
        (   CASE WHEN the_table.objclass = 4910                THEN   SUBSTR(the_table.objname, 3)::nvarchar(128)
                 WHEN the_table.objclass in (4951,4959,4963)   THEN NULL
                 ELSE   the_table.objname::nvarchar(128)
            END
        ) AS "Table Name",
        TO_CHAR ( nvl(SUM ( used_bytes ),0), '999,999,999,999,999,999'  ) AS "Size (Bytes)",
count(distinct(case when objclass in (4959,4963) then objid else null end)) as "# Of Versions"
FROM    _V_OBJ_RELATION_XDB the_table
        left outer join _V_SYS_OBJECT_DSLICE_INFO on
        (
               the_table.objid = _V_SYS_OBJECT_DSLICE_INFO.tblid   and
               _V_SYS_OBJECT_DSLICE_INFO.tblid > 200000
        )
WHERE
            the_table.objclass in (4905,4910,4940,4959,4951,4953,4961,4963)
        and the_table.objid > 200000
GROUP BY
        "Database",
        the_table.visibleid
HAVING  "# Of Versions" >= 2
ORDER BY 1, 2;

Below is sample output:

# Of Versioned Tables         13
     Total # Of Versions      26

       Database        |                       Table Name                       |       Size (Bytes)       | # Of Versions
-----------------------+--------------------------------------------------------+--------------------------+---------------
 CELEBI_BSS_SIT_DDS_DB | SUBSCRIPTION_ACQUISITION_REVENUE_SUMMARY_FULL_11062014 |              566,755,328 |             2
 CELEBI_PREPROD_DDS_DB | COGNOS_CUBE_CONTROL                                    |                2,883,584 |             2
 CELEBI_PREPROD_DDS_DB | COGNOS_CUBE_CONTROL_HIST                               |                  262,144 |             2
 CELEBI_SOR_DB         | REV_GROUP_MASTER                                       |               24,117,248 |             2
 CELEBI_STG_DB         | STG_SAP_CSKU                                           |                  262,144 |             2

Check if statistics out of date

You can use query below to check if statistics are outdated.  From nzsql, connect to a database in particular and run the query below:

nzsql -d database <<eof

SELECT
        "Table Name",
        the_label as "Statistics State",
        count(*)  as "# Of Columns",
  TO_CHAR ( _v_table.reltuples, '999,999,999,999,999') AS "Table Rowcount"
FROM
(
SELECT
                _v_relation_column.name as "Table Name",
                CASE WHEN       _v_statistic.recent IS NULL THEN 'not maintained    '
                ELSE
                CASE WHEN       _v_statistic.recent =    85 THEN 'Full              '
                     WHEN       _v_statistic.recent =   341 THEN 'Express           '
                     WHEN       _v_statistic.recent =   149 THEN 'Basic             '
                     WHEN       _v_statistic.recent =     1 THEN 'Full Min/Max OK   '
                     WHEN       _v_statistic.recent =   257 THEN 'Express Min/Max OK'
                     WHEN       _v_statistic.recent =   129 THEN 'Basic Min/Max OK  '
                     WHEN       _v_statistic.recent =     0 THEN 'Full Outdated     '
                     WHEN       _v_statistic.recent =   256 THEN 'Express Outdated  '
                     WHEN       _v_statistic.recent =   128 THEN 'Basic Outdated    '
                     WHEN       _v_statistic.recent =   169 THEN 'Min/Max Only      '
                     WHEN       _v_statistic.recent =   170 THEN 'Unavailable       '
                     WHEN       _v_statistic.recent = 16554 THEN 'Unavailable       '
                     ELSE       _v_statistic.recent || ''
                     END
                END as the_label,
                _v_relation_column.objid
        FROM
                _v_relation_column
                left outer join _v_statistic on
                (   _v_relation_column.name   = _v_statistic.tablename   AND
                    _v_relation_column.attnum = _v_statistic.attnum
                )
        WHERE
                    _v_relation_column.objid > 200000
                AND upper(_v_relation_column.type) in ('TABLE','SECURE TABLE')
) the_subquery
 LEFT OUTER JOIN _v_table ON the_subquery."Table Name"  =  _v_table.tablename
WHERE trim(the_label) NOT IN ('not maintained','Full','Express','Basic','Unavailable')

GROUP BY "Table Name", "Statistics State", "Table Rowcount"
  ORDER BY "Table Name", "Statistics State";

eof

unload and load data using External Table

There a few ways you can transfer Netezza data across another Netezza database.  One way is to use nz_migrate.  Another way through Aginity Tools -> Import and the third way is through External Table in Aginity.  Note:  You can only use this method from Aginity

Below example, dumps the data from the query to your local PC temp directory while connecting to the source database

UNLOAD DATA

CREATE EXTERNAL TABLE ext_db 'c:\temp\ext_db.dat'

USING

(

remotesource 'odbc'

delimiter '|'

ignorezero false

ctrlchars true

escapechar '\'

logDir 'c:\temp'

boolStyle 'T_F'

encoding 'internal'

nullValue 'N'

maxErrors 1

)

AS

SELECT database, owner, cast(createdate as date) as ts  from _v_database;

While connecting your Aginity to your target NPS host database, this method can be used to load the data

to your say Development environment.

LOAD DATA (in different NPS host database)

create external table abc

(

       DATABASE NATIONAL CHARACTER VARYING(255),

       OWNER NATIONAL CHARACTER VARYING(255),

       TS DATE

)

using

(

dataobject ('c:\temp\ext_db.dat')

Remotesource 'odbc'

Encoding 'INTERNAL'

EscapeChar '\'

CrInString true

CtrlChars true

Delimiter '|'

NullValue 'NULL'

)

;

Netezza Groups

There two types of groups in Netezza.  One type is the typical permission, privileges on specific objects in the NPS.  Another group is called Resource Sharing Group (RSG) for workload management.  You can use this group to define resource utilisation percentages from total NPS resources.

Object Permission Group

CREATE GROUP CELEBI_USR_PETL_ST_GRP;

Or, to create group and add user(s)

CREATE GROUP FIRST_GROUP WITH USER1,USER2;

or add user in group using alter

ALTER GROUP FIRST_GROUP ADD USER USER1;

or drop user from group

ALTER GROUP FIRST_GROUP DROP USER USER1;


RSG Group

CREATE GROUP EBI_ENR_USER WITH
SESSIONTIMEOUT 30
QUERYTIMEOUT 300
DEFPRIORITY NORMAL
MAXPRIORITY NORMAL
RESOURCE MINIMUM 10
RESOURCE MAXIMUM 70
MAXIMUM 30 ;

How to lock Netezza userid

How do you lock Netezza userid?  You actually cannot find any way from NZ Admin tool or Aginity or even from the portal.  Then, how do you lock a Netezza userid?  Below command will show you:


SYSTEM(ADMIN)=> update _t_user set uselocked=true where usename='TEST1';  
UPDATE 1  


If you want to unlock, you can use NZ Admin tool.

find who owns what objects

How to you find out who owns what in the Netezza databases.  You need to know this because sometimes you want to drop a particular object, NPS returns error like below:

DROP USER: user "BIADM" owns objects, cannot be removed

So how do you find out which objects belongs to BIADM?  

Here's how:

SELECT OWNER, THE_OBJECT_TYPE, OBJNAME

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

        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 || ' in database ' ||  the_database.objname AS objname

        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 OWNER='BIADM'

ORDER BY 1,2,3;

Netezza Admin / DBA role privileges

In Netezza, there is a default admin user which you can use.  But how about creating a second admin user?  You can assigned the permissions below.

create group DBA_GROUP;

-- Object permission
grant all on USER to DBA_GROUP;
grant all on MANAGEMENT VIEW to DBA_GROUP;
grant all on MANAGEMENT table to DBA_GROUP;
grant all on SYSTEM VIEW to DBA_GROUP;
grant all on SYSTEM TABLE to DBA_GROUP;
grant all on PROCEDURE to DBA_GROUP;
grant all on LIBRARY to DBA_GROUP;
grant all on AGGREGATE to DBA_GROUP;
grant all on FUNCTION to DBA_GROUP;
grant all on SYNONYM to DBA_GROUP;
grant all on SEQUENCE to DBA_GROUP;
grant all on EXTERNAL TABLE to DBA_GROUP;
grant all on DATABASE to DBA_GROUP;
grant all on GROUP to DBA_GROUP;
grant all on TABLE,VIEW to DBA_GROUP;

-- Admin permission
grant ALL ADMIN to DBA_GROUP;

create user NZADMIN with password 'abc123';

alter group DBA_GROUP add user NZADMIN;

create group DBA_GRA_GROUP with resource minimum 50 resource maximum 100;

alter user NZADMIN in resourcegroup DBA_GRA_GROUP;