Feb 9, 2010 4:52 PM
Quick reference to nzsql commands?
-
Like (0)
As a data analyst with needs to access database hosted on Netezza platform from time to time, I got a glimpse of the wonder of nzsql. So I set out to search for some sort of quick reference of nzsql commands online but didn't see much. Can anybody point me to such a document or, recommend some kind of reading/book? Thanks!
nzsql is just plain ANSI SQL - it was originally derived from a Postgres engine so you will see some flavors of this.
You can get a really good picture of how to use it with the Netezza Database User's Guide - it is extensive and free from Netezza as a .pdf
For a flavor on applications and case studies (get ready for a shameless promotion) get the book "Netezza Underground" on Amazon.com
there is help built into nzsql. When you run nzsql, you see:
Welcome to nzsql, the Netezza SQL interactive terminal.
Type: \h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
If you type \h for help, it gives you a quick list of available commands for help:
system(admin)=> \h
Available help:
ALTER AGGREGATE CREATE MATERIALIZED VIEW GENERATE STATISTICS
ALTER CATEGORY CREATE SECURITY LEVEL GRANT
ALTER COHORT CREATE SEQUENCE INSERT
ALTER DATABASE CREATE SYNONYM RESET
ALTER FUNCTION CREATE TABLE REVERT
ALTER GROUP CREATE TABLE AS REVOKE
ALTER HISTORY CONFIGURATION CREATE USER ROLLBACK
ALTER PROCEDURE CREATE VIEW SELECT
ALTER KEYSTORE DELETE SET
ALTER SECURITY LEVEL DROP AGGREGATE SET AUTHENTICATION
ALTER SEQUENCE DROP CATEGORY SET CONNECTION
ALTER SESSION DROP COHORT SET HISTORY CONFIGURATION
ALTER SYNONYM DROP CONNECTION SET SESSION
ALTER TABLE DROP CRYPTO KEY SET SYSTEM DEFAULT
ALTER USER DROP DATABASE SET TRANSACTION
ALTER VIEW DROP FUNCTION SHOW
BEGIN DROP GROUP SHOW AGGREGATE
CALL DROP HISTORY CONFIGURATION SHOW AUTHENTICATION
COMMENT DROP PROCEDURE SHOW CATEGORY
COMMIT DROP KEYSTORE SHOW COHORT
CREATE AGGREGATE DROP SECURITY LEVEL SHOW CONNECTION
CREATE CATEGORY DROP SEQUENCE SHOW CRYPTO KEY
CREATE COHORT DROP SYNONYM SHOW FUNCTION
CREATE CRYPTO KEY DROP TABLE SHOW HISTORY CONFIGURATION
CREATE DATABASE DROP USER SHOW KEYSTORE
CREATE EXTERNAL TABLE DROP VIEW SHOW PROCEDURE
CREATE FUNCTION EXECUTE SHOW SECURITY LEVEL
CREATE GROUP EXECUTE AS SHOW SYSTEM DEFAULT
CREATE HISTORY CONFIGURATION EXECUTE PROCEDURE TRUNCATE
CREATE PROCEDURE EXPLAIN UPDATE
CREATE KEYSTORE EXTRACT USER
If you type \?, it gives you the quick reference to the "slash commands" available only in nzsql (not available in odbc-compliant tools connecting to a Netezza server):
system(admin)=> \?
\a toggle between unaligned and aligned mode
\act show current active sessions
\c[onnect] [dbname [user] [password]]
connect to new database (currently 'system')
\C <title> table title
\copy ... perform SQL COPY with data stream to the client machine
\d <table> describe table (or view, index, sequence, synonym)
\d{t|v|i|s|e|x} list tables/views/indices/sequences/temp tables/external tables
\d{m|y} list materialized views/synonyms
\dS{t|v|i|s} list system tables/views/indexes/sequences
\dM{t|v|i|s} list system management tables/views/indexes/sequences
\dp <name> list user permissions
\dpu <name> list permissions granted to a user
\dpg <name> list permissions granted to a group
\dgp <name> list grant permissions for a user
\dgpu <name> list grant permissions granted to a user
\dgpg <name> list grant permissions granted to a group
\d{u|U} list users/User Groups
\d{g|G|Gr} list groups/Group Users/Resource Group Users
\da[+] [name] list aggregates, + for additional fields
\dd [object] list comment for object
\df[+] [name] list functions, + for additional fields
\do list operators
\dT list data types
\e [file] edit the current query buffer or [file] with external editor
\echo <text> write text to stdout
\f <sep> change field separator
\g [file] send query to backend (and results in [file] or |pipe)
\h [cmd] help on syntax of sql commands, * for all commands
\H toggle HTML mode (currently off)
\i <file> read and execute queries from <file>
\l list all databases
\o [file] send all query results to [file], or |pipe
\p show the content of the current query buffer
\pset <opt> set table output <opt> = {format|border|expanded|fieldsep|
null|recordsep|tuples_only|title|tableattr|pager}
\q quit nzsql
\qecho <text> write text to query output stream (see \o)
\r reset (clear) the query buffer
\s [file] print history or save it in [file]
\set <var> <value> set internal variable
\t show only rows (currently off)
\time print time taken by queries
\T <tags> HTML table tags
\unset <var> unset (delete) internal variable
\w <file> write current query buffer to a <file>
\x toggle expanded output (currently off)
\! [cmd] shell escape or command
No other quick reference guides that i have seen, just the Database Users Guide (sql reference and nzsql reference).
You can log in to the page:
http://www.scribd.com/doc/45005661/Netezza-Database-Users-Guide
or you can download it from,
http://www.dl4all.com/u8s/tag/Netezza+Database+Users+Guide.html

