Netezza - Installing UDF

Netezza is not showing UDF (User Defined Function) ?


Solution: You probably need to install SQL Extensions toolkit.

You may find the installer at below path.  Or just find libnetcrypto  folder.
You may find installer under libnetcrypto  folder or one folder beneath.


Login as nz user.

cd /export/home/nz/sqlext/libnetcrypto
Once you find "install", run below command.
 ./install -d dbname -schema schemaname  -u username -W password


Additional link -
https://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.sqltk.doc/t_sqlext_enabling.html#t_sqlext_enabling



[nz@hostname]$ cd /export/home/nz/apps/libnetcrypto/7.2.1.3/
[nz@hostname]$ ./install -d dw_audit
Installing libnetcrypto to default schema of database dw_audit in unfenced mode: .................................................. Done
[nz@hostname]$ pwd
/export/home/nz/apps/libnetxml/7.2.1.3
[nz@hostname]$ ./install -d databasename
Installing libnetxml to default schema of database databasename in unfenced mode: ............................................ Done

[nz@hostname]$ 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(ADMIN)=> \c databasename
You are now connected to database dw_audit.
databasename.NZUSER(ADMIN)=> select regexp_version();
                                    REGEXP_VERSION
--------------------------------------------------------------------------------------
 IBM Netezza SQL Extensions XML / Regular Expression Library Version 7.2.1.3 Build ()
(1 row)

databasename.NZUSER(ADMIN)=>  SELECT CRYPTO_VERSION();
                           CRYPTO_VERSION
--------------------------------------------------------------------
 IBM Netezza SQL Extensions Crypto Library Version 7.2.1.3 Build ()
(1 row)

databasename.NZUSER(ADMIN)=> select REGEXP_MATCH_COUNT('This is comcast sytem.','is');
 REGEXP_MATCH_COUNT
--------------------
                  2
(1 row)

Netezza System query

Useful Queries :

List of tables in a database. Below Query will give you list of all tables under given database.

SELECT * FROM _V_TABLE WHERE OBJTYPE='TABLE';

List of view in a database. Below query will give you list of all views under given database.

SELECT *  FROM _V_VIEW WHERE OBJTYPE = 'VIEW'

List of tables/views and columns
SELECT * FROM _V_RELATION_COLUMN  ORDER BY NAME,ATTNUM ASC;

List of User Groups

SELECT *  FROM _V_GROUP;


List of Users and their groups
SELECT * FROM _V_GROUPUSERS;

Database & Table Size
SELECT
A.DATABASE AS DATABASENAME
,(A.OBJNAME) AS TABLE_NAME
,A.OBJTYPE AS OBJECT_TYPE
,B.USED_BYTES/POW(1024,3) AS USED_SPACE_IN_GB
FROM _V_OBJ_RELATION_XDB A INNER JOIN
_V_SYS_OBJECT_STORAGE_SIZE B
ON    A.OBJID=B.TBLID
WHERE   OBJECT_TYPE IN ('TABLE')

Tables and Skew Size
SELECT TABLENAME,OBJTYPE,OWNER,CREATEDATE,USED_BYTES,SKEW FROM _V_TABLE_ONLY_STORAGE_STAT WHERE OBJCLASS = 4905 OR OBJCLASS = 4911 ORDER BY TABLENAME;

Database Size
SELECT _V_OBJ_RELATION_XDB.database  AS "DB Name",
case when sum(_V_SYS_OBJECT_DSLICE_INFO.allocated_bytes) is null then 0 else SUM(_V_SYS_OBJECT_DSLICE_INFO.allocated_bytes)/1073741824 end AS "AllocatedSpace(in GB)"
FROM _V_SYS_OBJECT_DSLICE_INFO  INNER JOIN _V_OBJ_RELATION_XDB  ON _V_OBJ_RELATION_XDB.objid = _V_SYS_OBJECT_DSLICE_INFO.tblid
GROUP BY _V_OBJ_RELATION_XDB.database
ORDER BY _V_OBJ_RELATION_XDB.database

Netezza resource group ?

Run  _v_group view and apply filter as grorsgpercent<>0 .

Example : Select * from _v_group where grorsgpercent<>0 ;

View "_v_group" will also give you all available group (after removing filter condition )



List of Group and Users in Netezza ?
Run view "_V_GROUPUSERS". This will give you list of all users and corresponding group/s. If user fall under multiple group, it will show all possible combination.