-- File: NetezzaCheatSheet.sql v.1.4.0 docs at the end
-- how-to list the columns of a table
SELECT ',' || NAME || '.' || ATTNAME from _V_RELATION_COLUMN
WHERE 1=1
AND NAME='ST_SOR_OFFICE'
ORDER BY ATTNUM
;
-- 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('<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
*/
-- Purpose:
-- To provide a simple cheat sheet for netezza
-- VersionHistory
-- 1.5.0 --- 2012-05-23 14:43:38 --- added important views
-- 1.4.0 --- 2012-05-18 15:26:35 --- how-to remove duplicates
-- 1.3.0 --- 2012-05-18 13:44:02 --- added how-to copy table
-- 1.2.0 --- ysg --- added list cols of a table
-- 1.1.1 --- ysg --- create table example
-- 1.1.0 --- ysg --- added duplicates removal , date conversion
-- 1.0.0 --- ysg --- Initial creation
Posts
