IBM Netezza DBA SQL Queries

IBM Netezza DBA SQL Queries

SQL to find list of views, Owner and their definitions in a NZ DB:

SELECT VIEWNAME,OWNER,DEFINITION FROM _V_VIEW WHERE OBJTYPE='VIEW';

SQL to get a list of tables in a database:

SELECT TABLENAME, OWNER, CREATEDATE FROM _V_TABLE WHERE OBJTYPE='TABLE';

or connect to database like below

nzsql –d PROD

nzsql>\d

SQL to find a list of columns from a table or a view:

SELECT ATTNUM, ATTNAME FROM _V_RELATION_COLUMN WHERE NAME=UPPER('<TABLE NAME>')

ORDER BY ATTNUM ASC;

SQL to find list of user groups on the box:

SELECT GROUPNAME,OWNER,CREATEDATE,ROWLIMIT,SESSIONTIMEOUT,

QUERYTIMEOUT,DEF_PRIORITY,MAX_PRIORITY FROM _V_GROUP;

SQL to find list of users and the groups they are in, on the box:

SELECT GROUPNAME, OWNER, USERNAME FROM _V_GROUPUSERS where GROUPNAME='PROD_RO' ;

Netezza NZSQL command:--

\dt                          --- In nzsql session to find the list tables

\dv                         ---To get list of views

\dmv                      ---List of materialized views

\l                            ---List of databases

\dg                         ---List of groups

\du                         ---List of users

\dpu                       ---Permissions set to a user

\dT                         ---List of datatypes

\d <tab_name>      ---DESC the table

\act                        ---Show current active sessions

\df                          ---List functions

\l                            ---List databases

\dT                         ---List data types

\du                         ---List users

\dg                         ---List groups

\dpu                       ---List permissions granted to a user

\dpg                       ---List permissions granted to a group