Netezza Important commands...

To find database size in Netezza

    SELECT ORX.database::nvarchar(64) AS"DatabaseName",
    case when sum(SOD.allocated_bytes)is null then 0 elseSUM(SOD.allocated_bytes)/1073741824end AS "AllocatedSpace_GB"
    FROM _V_SYS_OBJECT_DSLICE_INFO SODINNER JOIN _V_OBJ_RELATION_XDB ORXON ORX.objid = SOD.tblid
    GROUP BY "DatabaseName"
    ORDER BY "DatabaseName";

-- how-to create a database

CREATE DATABASE DatabaseName

;

-- how-to rename a database

ALTER DATABASE OldDatabaseName RENAME TO NewDatabaseName

;

-- how-to create a synomim

CREATE SYNONYM synonym_name FOR DatabaseName.SchemaName.TableName

;

-- how-to create a table



CREATE TABLE SVOC_OWNER.ExampleTable

(

    ByteIntCol        byteint            NOT NULL   

  , SmallIntCol       smallint           NOT NULL   

  , IntegerCol        integer            NOT NULL   

  , BigIntCol         bigint             NOT NULL   

  , NumericPSCol      numeric(38,38)     NOT NULL   

  , NumericPCol       numeric(38,0)      NOT NULL   

  , NumericCol        numeric            NOT NULL   

  , DecimalCol        numeric            NOT NULL   

  , FloatCol          float(15)          NOT NULL   

  , RealCol           real               NOT NULL   

  , DoubleCol         double             NOT NULL   

  , CharCol           char(1)        NOT NULL   

  , VarcharCol        varchar(1)     NOT NULL   

  , NcharCol          nchar(1)       NOT NULL   

  , NvarcharCol       nvarchar(1)    NOT NULL   

  , BooleanCol        boolean            NOT NULL   

  , DateCol           date               NOT NULL   

  , TimeCol           time               NOT NULL   

  , TimeTzCol         timetz             NOT NULL   

  , TimestampCol      timestamp          NOT NULL   

 )

DISTRIBUTE ON RANDOM ;


-- how-to copy table

CREATE TABLE NewTable AS SELECT * FROM TableToCopy ;





-- how-to or insert data from non-current db to current db table

INSERT INTO TableName SELECT * FROM DatabaseName..TableName

;

-- how-to drop a table

DROP TABLE DatabaseName..TableName

;

-- how-to change the ownership of a table

ALTER TABLE TableName OWNER TO NewOwner

;

-- how-to perform a simple select

SELECT * FROM TableName

WHERE

AND 1=1

AND WhereColumnName = 'WhereCondition'

AND GreaterThanColumnName > 0.0

ORDER BY WhereColumnName

;

-- how-to delete from table

DELETE FROM TableNameToDeleteFrom

WHERE FilterColumnName = 'FilterValue'

;

-- how-to call a stored procedure

CALL ProcName ;

EXEC ProcName ;

EXECUTE ProcName ;

-- example stored procedure

CREATE OR REPLACE PROCEDURE ProcName()

RETURNS INT4 LANGUAGE NZPLSQL AS

BEGIN_PROC

  DECLARE

    StrVar varchar;

  BEGIN

    StrVar := 'This string is quoted';

  END;

END_PROC

; --END PROC

-- a single line comment

/*

a multi-line comment

*/

-- example proc with parameters

CREATE OR REPLACE PROCEDURE ProcName (int, varchar(ANY)) RETURNS int

LANGUAGE NZPLSQL AS

BEGIN_PROC

  DECLARE

    pId ALIAS FOR $1;

    pName ALIAS FOR $2;

  BEGIN

    INSERT INTO t1 SELECT * FROM t2 WHERE id = pId;

  END;

END_PROC

;

-- Control structure

IF movies.genre = 'd' THEN

  film_genre := 'drama';

ELSIF movies.genre = 'c' THEN

  film_genre := 'comedy';

ELSIF movies.genre = 'a' THEN

  film_genre := 'action';

ELSIF movies.genre = 'n' THEN

  film_genre := 'narrative';

ELSE

-- An uncategorized genre form has been requested.

film_genre := 'Uncategorized';

END IF;

-- how-to document a stored procedure

COMMENT ON PROCEDURE customer() IS 'Author: bsmith

Version: 1.0 Description: A procedure that writes a customer name to

the database log file.';

-- how-to list all stored procedures

SHOW PROCEDURE ALL ;

-- how-to document a stored procedure

COMMENT ON PROCEDURE customer() IS 'Author: bsmith

Version: 1.0 Description: A procedure that writes a customer name to

the database log file.';



-- how-to convert date str into nzdate

select  to_date(substring(20090731 from 1 for 8),'YYYYMMDD') as NZDATE



-- select top

select a.* from some_schema.some_table a limit 10



-- START how to remove duplicates =================================

CREATE TABLE TmpTableDuplicates as

    SELECT col11,col2,col3 from DuplicatesContainingTable

    where FilterCol = 'FilterValue'

    group by 1,2,3 ;

DELETE FROM DuplicatesContainingTable where FilterCol = 'FilterValue' ;

INSERT INTO Source_table select * from TmpTableDuplicates ;

DROP TABLE TmpTableDuplicates ;

-- STOP how to remove duplicates =================================

-- 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;



--- HOW-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;


-- START SELECT INTO

INSERT INTO DatabaseNameTarget.SchemaNameTarget.TableNameTarget

SELECT ColumnName1 , ColumnName2

FROM DatabaseNameSource.SchemaNameSource.TableNameSource ;

-- STOP SELECT INTO


-- how-to remove duplicates

delete from TableWithDuplicates

where rowid not in

(

  select min(rowid) from TableWithDuplicates

  group by (DuplicateDefiningCol1 , DuplicateDefiningCol2 , DuplicateDefiningCol3) );


-- _V_USER : The user view gives information about the users in the netezza system.

select * from _v_user;



-- _V_TABLE: The table view contains the list of tables created in the netezza performance system.

select * from _v_table;



-- _V_RELATION_COLUMN: The relation column system catalog view contains the columns available in a table.

select * from _v_relation_column;



-- _V_TABLE_INDEX: This system catalog contains the information about the indexes created on table. netezza does not support creating indexes on a table as of now.

select * from _v_table_index;



-- _V_OBJECTS: Lists the different objects like tables, view, functions etc available in the netezza.

select * from _v_objects;

-- what is running currently

select * from _v_qrystat;

-- what has been running lately

select * from _v_qryhist;

-- 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

SQL query to find Distribution key of all tables in a Netezza Database

Run the following command in your connected database.

SELECT tab.tablename ,COALESCE(map.attname,'RANDOM') AS distribution_key       ,map.distseqno AS dist_seq_no FROM _v_table tab  LEFT OUTER JOIN _v_table_dist_map map  ON map.objid = tab.objid WHERE UPPER(objtype) IN ('TABLE','SECURE TABLE') ORDER BY tab.tablename, map.distseqno; 

    Sample output:

  TABLENAME            | DISTRIBUTION_KEY | DIST_SEQ_NO
----------------------------+-----------------------------+--------------------
 CBT_CUSTOMER     | CUSTOMER_ID            |           1
 CUSTOMER               | CUSTOMER_ID            |           1
 EMP_DETAILS          | DEPT_ID                        |           1
 MYTAB                      | ID                                     |           1
 PAYMENT                 | CUSTOMER_ID              |           1
 SALES_TAB              | CITY                                |           1
 SAMPLETABLE        | YEAR                              |           1
 TBL                             | I                                       |           1

After Looking at the output the question may arise, is there a way to tell if the distribution key was user picked or system picked? It means that by default if no distribution was explicitly specified by user Netezza would pick first column of the table as distribution key.

  Then here is the answer:          

The DIST_SEQ_NO is the order that columns were specified as part of the key if you have a multi-column distribution key. For single column distribution (which is what we'd recommend generally) this will just be 1.

Once a table is created we do not store the original SQL used to create it.. and becasue of this we don't have a way to tell what was user specified and what was system-picked to the best of my knowledge.

Just to clarify, the system may not always choose the first column as a distribution key if it's not specified.. it varies depending on how you're creating the table (DDL or CTAS) and the value of the parameter enable_random_table_distribute. The best idea is to always specify a key.