Search This Blog

2013-10-26

Netezza sql cheat sheet

-- File: NetezzaCheatSheet.sql v.1.6.0 docs at the end 
 
SELECT * FROM _V_OBJECTS
; 
 
SELECT * FROM _V_QRYSTAT;
 
-- how-to list the columns of a table 
SELECT ' , ' || NAME || '.' || ATTNAME  from _V_RELATION_COLUMN
WHERE 1=1
AND NAME LIKE '%HIST%'
ORDER BY NAME , ATTNUM
;
 
--how-to get the fully-qualified list of columns of a table 
SELECT  ' , ' || DATABASE || '.' || OWNER || '.' || NAME || '.' || ATTNAME , *  from _V_RELATION_COLUMN
WHERE 1=1
AND NAME ='SOR_ORDER'
ORDER BY NAME , ATTNUM
;
 
 
-- Query to get a list of tables in a database:
SELECT TABLENAME,OWNER,CREATEDATE FROM _V_TABLE 
WHERE 1=1 
AND OBJTYPE='TABLE'
AND TABLENAME LIKE '%LASER%'
;
 
 
SELECT * FROM SYSTEM.._V_QRYHIST
WHERE 1=1
AND QH_TSUBMIT > CURRENT_DATE
-- AND QH_SQL LIKE '%DISK_DEFECTS_20121214%'
ORDER BY 
-- QH_SESSIONID DESC
-- QH_RESROWS DESC
QH_ESTCOST ASC
; 
 
 
 
-- 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
 
-- how-to cast a nice date 
where date_column = to_date('2013-05-15 10:40:31' , 'YYYY-MM-DD HH24:MI:SS') 
 
-- how-to convert date 
SELECT * FROM TABLE_NAME WHERE 1=1 AND LOAD_TM > to_date ( '2013-04-16 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'
 
-- 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.8
; 
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;
 
-- Returns a list of all groups of which the user is a member 
SELECT * FROM _v_usergroups
; 
 
--- Returns a list of all system datatypes
SELECT * FROM _v_datatype
; 
 
--- returns the list of all active sessions 
SELECT * FROM _v_session
;
 
 
 
-- _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;
 
-- get the amount of locks on Netezza 
SELECT * FROM _t_pg_locks where username<>'ADMIN'
 
-- where the list of system tables is stored 
SELECT * FROM _v_sys_table
; 
-- Returns a list of all defined user privileges: \dpu <user>
_v_sys_user_priv 
 
 
nz_find_object_owners  [user]
 
select  o.objname,u.usename,oc.CLASSNAME,d.database,d.owner  from _t_object o 
 JOIN _t_user u
 ON o.objowner=u.usesysid
 JOIN _t_object_classes oc
 ON o.objclass=oc.objclass 
 JOIN _v_database d
 ON o.objdb=d.objid
 WHERE u.usename='KKONGAS'
 
; 
 
-- skew =  ((max_dataslice - min_dataslice) / avg_per_dataslice
select 
        t.tablename, 
        s.used_max, 
        s.used_min, 
        s.used_avg,
        round((used_max-used_min)/used_avg,2) skew,
        round(100*((used_max/used_avg)-1),2) scan_loss_pct, 
        round((used_max-used_avg)/104857600,4) scan_loss_seconds
from _v_sys_object_storage_sz2 s, _v_table t
where s.tblid = t.objid
  and scan_loss_seconds > 0
order by scan_loss_seconds desc;
 
 
-- 
SELECT
   _V_QRYHIST.QH_DATABASE
  , _V_QRYHIST.QH_TSTART
  , _V_QRYHIST.QH_TEND
  , EXTRACT (epoch from ( _V_QRYHIST.QH_TSTART - _V_QRYHIST.QH_TEND ))* INTERVAL '1 SECOND' AS DIFF_HHMMSS
  FROM _V_QRYHIST
where 1=1
AND QH_TSUBMIT > CURRENT_DATE
-- AND _V_QRYHIST.QH_DATABASE = 'SYSTEM'
-- AND _V_QRYHIST.QH_SQL LIKE '%INCR%'
-- ORDER BY QH_ESTCOST ASC
LIMIT 10
 
 
 
select * from _v_user;
select * from _v_table;
select * from _v_relation_column;
select * from _v_table_index;
select * from _v_objects;
 
 
-- get the time difference 
EXTRACT(epoch FROM DateCol1 -DateCol2)/3600 as difference_in_hours
EXTRACT(epoch FROM ( _V_QRYHIST.QH_TSTART - _V_QRYHIST.QH_TEND ))/3600 as difference_in_hours
 
 
-- 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
*/
 
-- GRANT THE PROPER OWNER TO IT
ALTER VIEW V_DB_GROOMER_HISTORY OWNER TO MAINTENANCE_OWNER 
;
 
/*
-- Purpose:
To provide a simple cheat sheet for netezza 
 
-- VersionHistory
 
1.6.0 --- YSG --- 2012-08-20 12:33:46 --- ADDED _v_usergroups
1.5.1 --- YSG --- 2012-06-18 13:30:16 --- locks 
1.5.0 --- YSG --- 2012-05-23 14:43:38 --- added important views
1.4.0 --- YSG --- 2012-05-18 15:26:35 --- how-to remove duplicates
1.3.0 --- YSG --- 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
*/

No comments:

Post a Comment

- the first minus - Comments have to be moderated because of the spammers
- the second minus - I am very lazy at moderating comments ... hardly find time ...
- the third minus - Short links are no good for security ...
- The REAL PLUS : Any critic and positive feedback is better than none, so your comments will be published sooner or later !!!!

Labels

perl (41) Cheat Sheet (25) how-to (24) windows (14) sql server 2008 (13) linux (12) oracle (12) sql (12) Unix (11) cmd windows batch (10) mssql (10) cmd (9) script (9) textpad (9) netezza (8) sql server 2005 (8) cygwin (7) meta data mssql (7) metadata (7) bash (6) code generation (6) Informatica (5) cheatsheet (5) energy (5) tsql (5) utilities (5) excel (4) future (4) generic (4) git cheat sheet (4) html (4) perl modules (4) programs (4) settings (4) sh (4) shortcuts (4) поуки (4) принципи (4) Focus Fusion (3) Solaris (3) cool programs (3) development (3) economy (3) example (3) freeware (3) fusion (3) logging (3) morphus (3) mssql 2005 (3) nuclear (3) nz (3) parse (3) python (3) sftp (3) sofware development (3) source (3) sqlplus (3) table (3) vim (3) .Net (2) C# (2) China (2) GUI (2) Google (2) GoogleCL (2) Solaris Unix (2) architecture (2) ascii (2) awk (2) batch (2) cas (2) chrome extensions (2) code2html (2) columns (2) configuration (2) conversion (2) duplicates (2) excel shortcuts (2) export (2) file (2) free programs (2) informatica sql repository (2) linux cheat sheet (2) mssql 2008 (2) mysql (2) next big future (2) nsis (2) nz netezza cheat sheet (2) nzsql (2) ora (2) prediction (2) publish (2) release management (2) report (2) security (2) single-click (2) sqlserver 2005 (2) sqlserver 2008 (2) src (2) ssh (2) template (2) tools (2) vba (2) video (2) xlt (2) xml (2) youtube videos (2) *nix (1) .vimrc (1) .virmrc vim settings configs (1) BSD license (1) Bulgaria (1) Dallas (1) Database role (1) Dense plasma focus (1) Deployment (1) ERP (1) ExcelToHtml (1) GD (1) GDP (1) HP-UX (1) Hosting (1) IDEA (1) INC (1) IT general (1) ITIL management bullshit-management (1) IZarc (1) Java Web Start (1) JavaScript anchor html jquery (1) Khan Academy (1) LINUX UNIX BASH AND CYGWIN TIPS AND TRICKS (1) Linux Unix rpm cpio build install configure (1) Linux git source build .configure make (1) ListBox (1) MIT HYDROGEN VIRUS (1) OO (1) Obama (1) PowerShell (1) Run-time (1) SDL (1) SIWA (1) SOX (1) Scala (1) Services (1) Stacks (1) SubSonic (1) TED (1) abstractions (1) ansible hosts linux bash (1) ansible linux deployment how-to (1) ansible yum pip python (1) apache (1) apache 2.2 (1) application life cycle (1) architecture input output (1) archive (1) arguments (1) avatar (1) aws cheat sheet cli (1) aws cli (1) aws cli amazon cheat sheet (1) aws elb (1) backup (1) bash Linux open-ssh ssh ssh_server ssh_client public-private key authentication (1) bash perl search and replace (1) bash stub (1) bin (1) biofuels (1) biology (1) books (1) browser (1) bubblesort (1) bugs (1) build (1) byte (1) cas_sql_dev (1) chennai (1) chrome (1) class (1) claut (1) cmdow (1) code generation sqlserver (1) command (1) command line (1) conf (1) confluence (1) console (1) convert (1) cool programs windows free freeware (1) copy paste (1) copy-paste (1) csv (1) ctags (1) current local time (1) cygwin X11 port-forwarding mintty xclock Linux Unix X (1) cygwin bash how-to tips_n_tricks (1) cygwin conf how-to (1) data (1) data types (1) db2 cheat sheet (1) db2 starter ibm bash Linux (1) debt (1) diagram (1) dictionaries (1) digital (1) disk (1) disk space (1) documentation (1) dos (1) dubai (1) e-cars (1) electric cars (1) electricity (1) emulate (1) errors (1) exponents (1) export workflow (1) extract (1) fast export (1) fexp (1) file extension (1) file permissions (1) findtag (1) firewall (1) for loop (1) freaky (1) functions (1) fusion research (1) german (1) git gitlab issues handling system (1) google cli (1) google code (1) google command line interface (1) gpg (1) ha (1) head (1) helsinki (1) history (1) hop or flop (1) host-independant (1) how-to Windows cmd time date datetime (1) ibm db2 cognos installation example db deployment provisioning (1) ideas (1) image (1) informatica oracle sql (1) informatica repo sql workflows sessions file source dir (1) informatica source files etl (1) install (1) isg-pub issue-tracker architecture (1) it management best practices (1) java (1) jump to (1) keyboard shortcuts (1) ksh (1) level (1) linkedin (1) linux bash ansible hosts (1) linux bash commands (1) linux bash how-to shell expansion (1) linux bash shell grep xargs (1) linux bash tips and t ricks (1) linux bash unix cygwin cheatsheet (1) linux bash user accounts password (1) linux bash xargs space (1) linux cheat-sheet (1) linux cheatsheet cheat-sheet revised how-to (1) linux how-to non-root vim (1) linux ssh hosts parallel subshell bash oneliner (1) london (1) make (1) me (1) metacolumn (1) metadata functions (1) metaphonre (1) method (1) model (1) movie (1) multithreaded (1) mysql cheat sheet (1) mysql how-to table datatypes (1) n900 (1) nano (1) neteza (1) netezza bash linux nps (1) netezza nps (1) netezza nps nzsql (1) netezza nz Linux bash (1) netezza nz bash linux (1) netezza nz nzsql sql (1) netezza nzsql database db sizes (1) non-password (1) nord pol (1) nps backup nzsql schema (1) number formatting (1) nz db size (1) nz table count rows (1) nzsql date timestamp compare bigint to_date to_char now (1) on-lier (1) one-liners (1) one-to-many (1) oneliners (1) open (1) open source (1) openrowset (1) openssl (1) oracle PL/SQL (1) oracle Perl perl (1) oracle installation usability (1) oracle number formatting format-model ora-sql oracle (1) oracle templates create table (1) oracle trigger generic autoincrement (1) oracle vbox virtual box cheat sheet (1) oracle virtual box cheat sheet (1) outlook (1) parser (1) password (1) paths (1) perl @INC compile-time run-time (1) perl disk usage administration Linux Unix (1) perl modules configuration management (1) permissions (1) php (1) picasa (1) platform (1) postgreSQL how-to (1) powerShell cmd cygwin mintty.exe terminal (1) ppm (1) predictions (1) prices (1) principles (1) productivity (1) project (1) prompt (1) proxy account (1) public private key (1) publishing (1) putty (1) qt (1) read file (1) registry (1) relationship (1) repository (1) rm (1) scala ScalaFmt (1) scp (1) scripts (1) scsi (1) search and replace (1) sed (1) sendEmail (1) sh stub (1) shortcuts Windows sql developer Oracle (1) sidebar (1) silicon (1) smells (1) smtp (1) software development (1) software procurement (1) sofware (1) sort (1) sql script (1) sql_dev (1) sqlcmd (1) sqlite (1) sqlite3 (1) sshd (1) sshd cygwin (1) stackoverflow (1) stored procedure (1) stub (1) stupidity (1) subroutines (1) svn (1) sysinternals (1) system design (1) tail (1) tar (1) temp table (1) templates (1) teradata (1) terminal (1) test (1) testing (1) theory (1) thorium (1) time (1) tip (1) title (1) tmux .tmux.conf configuration (1) tmux efficiency bash (1) tool (1) ui code prototyping tips and tricks (1) umask Linux Unix bash file permissions chmod (1) url (1) urls (1) user (1) utility (1) utils (1) vb (1) vbox virtual box cheat sheet (1) vim perl regex bash search for string (1) vim recursively hacks (1) vim starter (1) vim-cheat-sheet vim cheat-sheet (1) vimeo (1) visual stuio (1) warsaw (1) wiki (1) wikipedia (1) window (1) windows 7 (1) windows 8 (1) windows programs (1) windows reinstall (1) windows utility batch perl space Windows::Clipboard (1) wisdoms (1) workflow (1) worth-reading (1) wrapper (1) xp_cmdshell (1) xslt (1) youtube (1)

Blog Archive

Translate with Google Translate

My Blog List