Skip navigation
38562 Views 3 Replies Latest reply: Feb 14, 2011 2:38 AM by lrubita RSS
pekdan New Enzee 1 posts since
Feb 9, 2010
Currently Being Moderated

Feb 9, 2010 4:52 PM

Quick reference to nzsql commands?

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!

  • David Birmingham Active Enzee 429 posts since
    Sep 24, 2007
    Currently Being Moderated
    1. Feb 9, 2010 5:20 PM (in response to pekdan)
    Re: Quick reference to nzsql commands?

    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

  • Chris Rodgers Active Enzee 185 posts since
    Sep 19, 2006
    Currently Being Moderated
    2. Feb 9, 2010 5:22 PM (in response to pekdan)
    Re: Quick reference to nzsql commands?

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

  • lrubita New Enzee 7 posts since
    Jan 28, 2011

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 4 points
  • Helpful Answers - 2 points