Netezza Metadata Queries

Lifted from netezzaforum.com
Query to get a list of views and thier definitions in a database:
SELECT VIEWNAME,OWNER,CREATEDATE,DEFINITION FROM _V_VIEW WHERE OBJTYPE='VIEW';
Query to get a list of tables in a database:
SELECT TABLENAME,OWNER,CREATEDATE FROM _V_TABLE WHERE OBJTYPE='TABLE';
Query to get 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;
Query to get list of user groups on the box:
SELECT GROUPNAME,OWNER,CREATEDATE,ROWLIMIT,SESSIONTIMEOUT,
QUERYTIMEOUT,DEF_PRIORITY,MAX_PRIORITY FROM _V_GROUP;
Query to get list of users and the groups they are in, on the box:
SELECT GROUPNAME,OWNER,USERNAME FROM _V_GROUPUSERS;
(Does not give any LDAP users in this query)
Query to find the number of rows in a table without actually querying the table:
(Sometimes needed for some really huge tables of rowcount > 80 Billion)
SELECT RELNAME TABLE_NAME,
CASE
WHEN RELTUPLES < 0
THEN ((2^32) * RELREFS) + ((2^32) + RELTUPLES )
ELSE ((2^32) * RELREFS) + ( RELTUPLES )
END NUM_ROWS
FROM
_T_CLASS,
_T_OBJECT
WHERE
_T_OBJECT.OBJID=_T_CLASS.OID AND
_T_OBJECT.OBJCLASS=4905 — DISPLAY ONLY TABLES
AND RELNAME = UPPER('<TABLE NAME>');
Query to check if any of the SPU's are running slower than the rest:
(This actually gives the read-write speed of each SPU that is online)
SELECT HWID, BYTE_COUNT/TOTAL_MSEC
FROM
_VT_DISK_TIMING
ORDER BY 2;
One more query.. To get the list of tables and thier skew and size:
SELECT TABLENAME,OBJTYPE,OWNER,CREATEDATE,USED_BYTES,SKEW
FROM _V_TABLE_ONLY_STORAGE_STAT
WHERE OBJCLASS = 4905 OR OBJCLASS = 4911
ORDER BY TABLENAME;
Use \dt in nzsql session to get 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 <tablename> - describes the table
\act - show current active sessions
\d - describe table(or view,sequence)
\dt , \dv , \ds , \de - list tables,views,sequences,temp tables
\dSt , \dSv - list system tables and views
\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 granged to a group