Netezza nzsql Command and its Usage

The Netezza nzsql command invokes a SQL command interpreter on the Netezza host or any Unix/Linux system that can act as a edge node to connect to Netezza host.

Read:

• Netezza Basic Commands
• Commonly used basic Netezza Linux Commands

You can use the nzsql command to perform all the sql related stuffs. E.g. create database objects, run queries, and manage various databases created on that particular server. You cannot manage the object created in different host or server.

Read:

• nzload Command and its Usage in Netezza
• Netezza nzsql Internal Slash Options

nzsql Command Usage

To run the nzsql command, enter:

nzsql -u [username] -pw [password] -d [database] -host [Netezza_host]

For example, you can connect to netezza server as shown below:

[charlie@netezza~]$ nzsql -u charlie -pw tango$1 -d MISSION -host netezzaserver01 
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

TRAINING.ADMIN(ADMIN)=>

Nzsql command requires the some paramters to connnect to database. When you invoke the nzsql command, you must supply a user name, password, and the name of the database to which you are connecting. You can enter this information on the nzsql command line, or you can specify the information in environment variables and export those variables before you begin your nzsql session to eliminate need to have to pass them on the command line each time. Above example, we have connected without exporting.

Read:

• Commonly used Netezza Basic Commands

The nzsql required below environmental variable to be exported to connect to database in case if you want to export the values before connecting to database:

• NZ_USER: This is the username of the server and database to which you are connecting
• NZ_PASSWORD: This is the password associated with username
• NZ_DATABASE: This is the database name to which you are connecting
• NZ_HOST: Name of the Netezza host to which you are connecting

You can set the variables in your command shell by using variables such as the following, and then use the nzsql command without any arguments:

export NZ_USER=charlie 
export NZ_PASSWORD=tango$1 
export NZ_DATABASE=MISSION 
export NZ_HOST=netezzaserver01

[charlie@netezza~]$ nzsql 
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

TRAINING.ADMIN(ADMIN)=>

Display SQL User Session Variables

You can display the current user-defined session variables by using the \set command with no parameters. For example:

TRAINING.ADMIN(ADMIN)=> \set 
VERSION= 'IBM Netezza SQL Version 1.1' 
PROMPT1= '%/%.%s%(%n%)%R%# ' 
PROMPT2= '%/%.%s%(%n%)%R%# ' 
PROMPT3= '>> ' 
HISTSIZE= '500' 
DBNAME= 'TRAINING' 
USER= 'ADMIN' 
HOST= '127.0.0.1' 
PORT= '5480' 
ENCODING= 'LATIN9' 
NZ_ENCODING= 'UTF8' 
TRAINING.ADMIN(ADMIN)=>

nzsql Command Feedback

nzsql provides feedback as each SQL command is executed on the terminal. Once the sql statement is executed, nzsql provides feedback and confirms command is executed properly.

TRAINING.ADMIN(ADMIN)=>insert into mytable values (100,'This is a test'); 
INSERT 0 1 
TRAINING.ADMIN(ADMIN)=>update mytable set column1=999 where column1=100; 
UPDATE 1 
TRAINING.ADMIN(ADMIN)=>insert into mytable values (200, 'Another test'); 
INSERT 0 1 
TRAINING.ADMIN(ADMIN)=>delete from mytable where column1 > 0; 
DELETE 2 
TRAINING.ADMIN(ADMIN)=>truncate table mytable; 
TRUNCATE TABLE 
TRAINING.ADMIN(ADMIN)=>drop table mytable; 
DROP TABLE

nzsql Command Options

The following table describes the nzsql command parameters:

Parameters	Description
-a	Echo all input from script
-A	Unaligned table output mode (-P format=unaligned)
-c <query>	Run only single query (or slash command) and exit
-d <dbname>	Specify database name to connect to (default: system)
-D <dbname>	Specify database name to connect to (default: system)
-schema <schemaname>	Specify schema name to connect to (default: $NZ_SCHEMA)
-e	Echo queries sent to backend
-E	Display queries that internal commands generate
-f <filename>	Execute queries from file, then exit
-F <string>	Set field separator (default: “|”)
-host <host>	Specify database server host (default: 127.0.0.1)
-h <host>	Specify database server host (default: 127.0.0.1)
-H	HTML table output mode (-P format=html)
-l	List available databases, then exit
-n	Disable readline
-o <filename>	Send query output to filename (or |pipe)
-O <filename>	Send query output with errors to filename (or |pipe)
-port <port>	Specify database server port (default: hardwired)
-P var[=arg]	Set printing option ‘var’ to ‘arg’ (see \pset command)
-q	Run quietly (no messages, only query output)
-r	Suppress row count in query output
-R <string>	Set record separator (default: newline) (-P recordsep=)
-Rev	Show version information and exit
-rev	Show version information and exit
-s	Single step mode (confirm each query)
-S	Single line mode (newline terminates query)
-t	Print rows only (-P tuples_only)
-time	Print time taken by queries
-T text	Set HTML table tag options (width, border) (-P tableattr=)
-u <username>	Specify database username (default: admin)
-U <username>	Specify database username (default: admin)
-v name=val	Set nzsqlvariable ‘name’ to ‘value’
-V	Show version information and exit
-w	Don’t require password, other mechanisms (Kerberos) will supply it
-W <password>	Specify the database user password
-x	Turn on expanded table output (-P expanded)
-X	Do not read startup file (~/.nzsqlrc)
-pw <password>	Specify the database user password

Netezza nzsql Command Examples

nzsql -A : Unaligned Outputs

You can use the -A option in nzsql command to obtain unalinged output. For example:

[nz@netezza~]$ nzsql -d TRAINING -A -c "SELECT * FROM TEST limit 5" 
ID|FLAG
1|T
1|T
0|F
1|T
0|F

nzsql -c : Run Single Query and Exit

You can use the -c option in nzsql to run single query. For example:

[nz@netezza~]$ nzsql -d TRAINING -c "SELECT count(*) FROM TEST" 
COUNT
-------
12(1 row)

nzsql -F : Set Field Seperator

You can use the -F option in nzsql to change the field seperator. For example:

[nz@netezza~]$ nzsql -d TRAINING -F',' -c "SELECT * FROM TEST limit 4"
ID, FLAG 
----,------
0, F 
0, F 
10, T 
0, F 
(5rows)

nzsql -t : Print rows only

You can use the -t option in nzsql to print rows only. You can combine this with the -A to remove the blank spaces. For example:

[nz@netezza~]$ nzsql -d TRAINING -A -t -c "SELECT * FROM TEST limit 4"
0|F
0|F
10|T
0|F