Netezza cheat sheets

If you want to databases 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 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 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('

‘) 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('

‘) ;

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

Leave a Reply

Close Menu