Commonly used Netezza Basic Commands

IBM Netezza
Reading Time: 4 minutes
\a toggle between   unaligned and aligned mode
\act show current active   sessions
\c[onnect]   [dbname [user] [password]] connect to new   database (currently ‘RJEYARAJ_DB’)
\C   <title> HTML 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
\dl[+]   [name] list libraries; + 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

nzrev Command

Check the Netezza build version

[[username] ~]$ nzrev
Release [Build 46322]

nzstats Command

Provide the statistics about the Netezza appliance

[[username] ~]$ nzstats
Field Name Value
-------------------- --------------------------------------------
Name netezza 01
Description <sys description>
Contact <contact name>
Location <sys location>
IP Addr
Up Time 34640 secs
Up Time Text 9 hrs, 37 mins, 20 secs
Date 21-Oct-16, 00:53:47 EDT
State 8
State Text Online
Model IBM Netezza 100-1
Serial Num <serial #>
Num SFIs 0
Num SPAs 1
Num SPUs 1
Num Data Slices 8
Num Hardware Issues 0
Num Dataslice Issues 0

nzhw Command

You can verify the Netezza hardware and check the issues if any

[[username] ~]$ nzhw
Description HW ID Location Role State Security
----------- ----- ---------- ------ ------ --------
Rack 1001 rack1 Active Ok N/A
SPA 1002 spa1 Active Ok N/A
SPU 1003 spa1.spu1 Active Online N/A
Disk 1004 spa1.disk1 Active Ok Disabled
Disk 1005 spa1.disk2 Active Ok Disabled
Disk 1006 spa1.disk3 Active Ok Disabled

nzstate Command

You can verify the status of the Netezza system with help of this command

[[username] ~]$ nzstate
System state is 'Online'

nzsql Command

nzsql is Netezza command line interface. You can use those command to connect to Netezza data warehouse system.

Read: nzsql command and its usage

[[username] ~]$ nzsql -u username -pw password -db training
Welcome to nzsql, the IBM 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

nzload Command

This command is used to get data into the Netezza data warehouse system.

[[username] ~]$ nzload -h
Usage: nzload [-h|-rev] [<options>]

nzsession Command

This command is used to maage the Netezza sessions.

[[username] ~]$ nzsession
ID Type User Start Time PID Database Schema State Priority Name Client IP Client PID Command
—– —- —– ———————– —– ——– —— —— ————- ——— ———- ————————
16634 sql ADMIN 21-Oct-16, 01:03:48 EDT 16113 SYSTEM ADMIN active normal 16112 SELECT session_id, client

nzpassword Command

Encrypt the password with help of nzpassword command.

nzbackup and nzrestore Commands

With help of these command you can backup your Netezza data warehouse and restore whenever you want it back.

groom table command

You can use this command to reclaim the Netezza space.

NOTICE: Groom will not purge records deleted by transactions that started after 2016-10-21 01:22:05.
NOTICE: Groom processed 2 pages; purged 0 records; scan size unchanged; table size unchanged.