Category Archives: nzsql

The nzsql command provides many command line options.

Using Command Inputs

Using the nzsql command, you can specify different input options:

�� To run a single query from the command line, enter:

nzsql -c “select * from test_table”

�� To read input from the current source, for example a script, enter:

nzsql

SELECT * FROM test_table;

eof

�� To read input (standard in), enter:

nzsql

�� To execute queries from a file (command line argument), enter:

nzsql -f foo.sql

�� To execute queries from a file (nzsql option), enter:

nzsql

\i foo.sql

Using Command Outputs

Using the nzsql command, you can specify different output options:

�� To save the resulting count value into a variable for later use, enter:

VAR1=‘nzsql -A -t -c “select count(*) from test_table”‘

�� To pipe the output to a printer, enter:

nzsql | lpr

�� To send the output to a file (command line argument), enter:

nzsql -o foo.out

�� To send the output to a file (nzsql option), enter:

nzsql

\o foo.out

Note: The nzsql command pipes interactive output (to a screen) through the more command so it can be paginated and scrolled for viewing. To change to another command, set the PAGER environment variable. For example, export PAGER=cat.

Using the nzsql Command Line Options

When you type nzsql -h, the system displays the command line options that you can use. For a list and descriptions of the all the command line options, see Table D-1 on page D-1. The following describes some useful command line options:

�� -A — Unaligned table output mode

Normally output is well formatted with white space added so that columns align. If you use the -A option, the nzsql command removes extra white space. Use this command

when you want to compare results between two systems or when you want to transfer

data.

�� -c — Run a single query and exit

This option lets you run a single query. When you combine it with options -A, and -t

(print rows only) you can create useful scripts. For example, to find out the number of records in a table, enter:

#!/bin/bash

export CNT=‘nzsql -A -t -c “select count (*) from $1″‘

echo “The number of records in table $1 is $CNT”

�� -E — Display queries that internal commands generate

This option lets you see how the SQL is being generated. For example, \l displays the

list of databases and when you add -E to the command line, the system shows you the

actual SQL used to generate the list.

nzsql -E

\l

********* QUERY *********

SELECT Database, Owner FROM _v_database

*************************

List of databases

database | owner

————–+——-

database_one | admin

database_two | admin

system | admin

(3 rows)

�� -f — Execute queries from a file

�� -F — Set the field separator

The default delimiter between output columns is a pipe (|) symbol. When used with the

-A option (unaligned output), you can specify a different delimiter string, such as a

space, tab, comma, colon, and so on. Note that you can use the -R to change

the record separator. The default is newline.

�� -H — HTML Table Output Mode

You can format the nzsql output to use HTML tags.

�� -t — Print rows only

Normally the nzsql command includes column headings and a summary row for all SQL

queries. Use the -t option to eliminate the column headings and summary row. Use this

option with the -A option to produce data in a transportable format.

�� -x — Expand table output

You can use this option to display the query results vertically instead of in the default table/2D grid.

Using Miscellaneous Command Options

The nzsql command has the following command line options that you use when running

queries:

�� — — Two dashes denote the beginning of a single-line comment.

�� /* — Forward slash and an asterisk denote the beginning of a multiline comment.

�� ‘literal’ — Use single quotes around literals. For example, ‘May 10, 2000’, ‘Netezza’. ‘US’. Use a pair of single quotes when you want to embed a single quote. For example, ‘Mark”s Test’.

�� ”label” — Use double quotes around labels. For example, SELECT lst_name AS

“Employee Last Name” FROM emp_table;

�� Identifiers — The system automatically converts identifiers, such as database, table, and column names, to the default system case, which is Upper on new systems. If you want to use mixed case and/or spaces, you must use double quotes around the identifier. For example, CREATE TABLE “Emp Table” (emp_id integer, emp_name char(20));

SELECT emp_id FROM “Emp Table”;

�� */ — Asterisk and forward slash denote the end of a multiline comment.

Using the nzsql Internal Slash Options

When you use the nzsql command in interactive mode, there are many options that you can use. These options, known as internal slash options, are called with a backslash (\) . Many of these options are the same as those available on the command line.

The following are some useful internal slash options. For a list and description of all the internal slash options, see Table D-2 on page D-2.

�� \d — Describe a table or view.

Displays the DDL for a specific table.

�� \dt and \dv — List tables or views.

Lists the tables or views in the current database.

�� \dSt and \dSv — List system tables or views.

Lists the Netezza internal tables or views if you are the admin user. If you are another user, you must have the appropriate privileges. Note that internal tables begin with _t_ and internal views begin with _v_.

Note: Do not modify these tables. Doing so could impact the integrity of your system.

�� \du and \dU — List users and users’ groups.

Displays a list of all users or a list of users and the groups in which they are members.

�� \dg and \dG — List groups and groups of users.

Displays a list of all groups or a list of all the groups and their members.

�� \dGr — List resource sharing groups.

Displays a list of the groups that are assigned to Guaranteed Resource Allocation

(GRA).

�� \echo — Write text to standard output.

Allows you to include descriptive text between SQL statements. This is especially useful when writing scripts, as in the following example:

nzsql \echo Rowcount before the truncate

SELECT COUNT(*) FROM customer;

\echo Rowcount after the truncate

TRUNCATE TABLE customer;

SELECT COUNT(*) FROM customer;

eof

When you run this script, the system displays the messages “Rowcount before (or after) the truncate count” before the two select statements.

�� \h [cmd] — Display help on SQL syntax.

Use this option to display help for SQL syntax for a specific command. The help displays a description of the command and the command syntax. ”

�� \l — List all databases.

Use this option to list all the databases and their owners.

�� \![cmd] — Issue shell command.

Use this option to run a shell command without terminating your nzsql session. You

can use this option to issue shell commands between SQL statements, which is especially useful in scripts.

nzsql \! date

SELECT COUNT(*) From customer;

\! date

eof

The example produces the following output:

Wed Jun 27 11:23:50 EDT 2007

count

——-

12399

(1 row)

Wed Jun 27 11:23:50 EDT 2007

You can use the \set command to store an often-used expression or SQL statement in a

variable. This variable is visible for the length of your connected session.

a. Set the variable:

\set my_sql ‘select * from sales_tbl where amt > ‘

b. Use the variable in a query:

:my_sql 4000;

city | state | region | quarter | amt

————+——-+————+———+——

New York | NY | Northeast | 3 | 4300

New York | NY | Northeast | 4 | 5000

Using the Query Buffer

Because the nzsql command is line oriented, it is difficult to edit a complex, multiline SQL statement. To make it easier, use the query buffer.

�� \e — Edit the current query buffer or file with an external editor.

When you exit the editor, the system automatically runs your query. Note that the query buffer stores only the last SQL statement.

The default editor is vi. To change to another editor, set the EDITOR environment variable.

For example, export EDITOR=emacs.

�� \p — Show the contents of the query buffer.

�� \r — Reset (clear) the query buffer.

�� \w — Write the query buffer to a file.

nzsql Exit Codes

The nzsql command returns one of the following error codes:

�� 0 — Success on the last statement executed (SQL or “\” commands, including “\q”).

�� 1 — Any type of failure (syntax error, database problems, and so on) on the last statement executed.

�� 2 — Any type of connection failure (bad database name, wrong user/password, database down, and so on).

�� 3 — The user cancelled a query by pressing Control-C.

When executing commands from a file (with the -f command line option) the nzsql command normally executes all statements. To have the nzsql command stop execution and exit if it encounters an error, use ‘-v ON_ERROR_STOP=1’ on the command line.

Continue reading →

You can use the nzsql command on the Netezza system or from a UNIX client system that
can access the Netezza host. The command uses a client/server model, which includes:

�� A server that manages database files, accepts connections to the database from client
applications, and performs actions on the database on behalf of the client.

�� A client application that can perform a variety of database operations. The client could be one of many tools, and is often created by the user.

Logging On

When you invoke the nzsql command, you must supply a database account 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 before you begin your nzsql session. For example, you can enter the following from a command window prompt:

nzsql -d sales -u mlee -pw blue

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
SALES(MLEE)=>

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

export NZ_USER=john
export NZ_PASSWORD=red
export NZ_DATABASE=sales
nzsql

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
SALES(JOHN)=>
Note: Throughout the remainder of this guide, the nzsql command output will be abbreviated to omit the “welcome” text for brevity in the examples.

The Netezza administrator creates and manages the database user accounts using SQL
commands or the Netezza NzAdmin and Web Admin administration interfaces. For a complete description of how to manage user accounts, see the Netezza System Administrator’s Guide.

The Netezza system has a default “admin” database user account who is the superuser of the Netezza databases. The admin user can connect to any database; load data; create, alter and drop any objects; create and manage new database users; and so on. Typically the admin user creates new accounts so that other users can access one or more databases and run queries. The admin user can also create accounts with administrative permissions so that other users can be allowed to perform tasks such as manage databases and user setup, backups, and other administrative tasks.

Session Management

Each client user who connects to the Netezza system opens a session. Users can view information about their sessions, as well as manage them to do such tasks as alter or drop their sessions. The admin account or any permitted user can also show, drop, and manage sessions (that is, change the priority and/or rollback a transaction) for a session. For a description of the SQL commands to manage sessions (ALTER SESSION, DROP SESSION, and SHOW SESSION)

SSL Support for Clients

Starting in Release 4.5, the Netezza system supports secure sockets layer (SSL) encryption and authentication for connections to the Netezza system. When you run the nzsql command, you can use the following two options to specify the security options for the connection:

�� -securityLevel specifies the security level that you want to use for the session. The argument has four values:

�� preferredUnsecured — This is the default value. Specify this option when you
would prefer an unsecured connection, but you will accept a secured connection if
the Netezza system requires one.

�� preferredSecured — Specify this option when you want a secured connection to
the Netezza system, but you will accept an unsecured connection if the Netezza
system is configured to use only unsecured connections.

�� onlyUnsecured — Specify this option when you want an unsecured connection to
the Netezza system. If the Netezza system requires a secured connection, the connection will be rejected.

�� onlySecured — Specify this option when you want a secured connection to the
Netezza system. If the Netezza system accepts only unsecured connections, or if
you are attempting to connect to a Netezza system that is running a release prior to
4.5, the connection will be rejected.

�� -caCertFile specifies the pathname of the root certification authority (CA) file. The CA file must be obtained from the Netezza system administrator and installed on the client system. The CA file authenticates the server (the Netezza host) to the client. The default value is NULL, which indicates that no peer authentication will occur.

When you invoke the nzsql command, you can specify these arguments on the command
line or you can specify the information in environment variables before you begin your nzsql session. The environment variables follow:

�� export NZ_SECURITY_LEVEL=level
�� export NZ_CA_CERT_FILE=pathname

These SSL security arguments are also used with the nzsql \c switch when a user attempts to connect to a different Netezza database. If you do not specify values for these fields, the Netezza system uses the values specified for the existing connection.

Understanding the nzsql Prompt

After you invoke the nzsql command, the prompt contains the name of the database and
your user name. In the following example, the database is system and the user is admin:
SYSTEM(ADMIN)=>
By default, the Netezza system uses uppercase letters to display SQL output. The system case can be configured to use lowercase instead, which was the default in earlier Netezza releases.

To connect to another database without exiting the nzsql command, use the \c option:
\c[onnect] [dbname [user] [password]]

For example, the follow command connects to the database named sales as the user mlee
with the password blue:

SYSTEM(ADMIN)=> \c sales mlee blue

You are now connected to database sales as user mlee.
SALES(MLEE)=>

Getting Command Feedback

When you issue a Netezza SQL command, you either succeed or receive an error. In either case, the system provides feedback that you can use in a script.

The system feedback for inserts, updates, and deletes shows you the number of rows acted upon. The feedback for inserts includes an extra zero before the actual number (due to a historical artifact). Samply commands (shown in bold) and the command feedback follows:

nzsql

CREATE TABLE test1 (col1 INTEGER, col2 INTEGER, col3 CHARACTER(40));
CREATE TABLE

INSERT INTO test1 VALUES (100, 200, ‘This is a test’);
INSERT 0 1

INSERT INTO test1 VALUES (101, 201, ‘Another test’);
INSERT 0 1

UPDATE test1 SET col2 = 999 WHERE col1 UPDATE 2

INSERT INTO test1 SELECT * FROM test1;
INSERT 0 2

delete from test1 where col1 > 0;
DELETE 4

TRUNCATE TABLE test1;
TRUNCATE TABLE

DROP TABLE test1;
DROP TABLE

Displaying SQL User Session Variables

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

SALES(MLEE)=> \set
VERSION = ‘Netezza SQL Version 1.1′
PROMPT1 = ‘%/%(%n%)%R%# ‘
PROMPT2 = ‘%/%(%n%)%R%# ‘
PROMPT3 = ‘>> ‘
HISTSIZE = ’500′
DBNAME = ‘SALES’
USER = ‘MLEE’
HOST = ’127.0.0.1′
PORT = ’5480′
ENCODING = ‘LATIN9′
NZ_ENCODING = ‘UTF8′
LASTOID = ’0′ Continue reading →

Netezza SQL is the Netezza Structured Query Language (SQL), which runs on the Netezza
data warehouse appliance. Throughout this blog, the term SQL refers to Netezza’s
SQL implementation. Several standards relate to the definition of Netezza SQL:
�� SQL-92 (also called SQL/2) is the operative ANSI/ISO standard for relational databases today. While no vendor supports the complete SQL-92 standard, Netezza SQL conforms to all the commonly supported components of SQL-92.

�� SQL/CLI — Callable language interface (CLI), which is an addition to the SQL/92 specification
in 1995 to define required functionality for CLIs to support client-server and
other network based access models. The CLI was largely driven by ODBC, but JDBC is
also a CLI-based interface.
�� SQL:1999 (also called SQL-99) — Added regular expression matching, recursive queries, triggers, and other object-oriented features.

If you have direct access to the Netezza appliance from a command shell, or if you have UNIX clients with the Netezza CLI tools, you can run SQL commands using the nzsql command line interpreter. You can also run Netezza SQL commands using common SQL tools and applications that support ODBC, JDBC, and OLE DB data connectivity APIs. The Netezza system can support multiple concurrent connections from clients. Within a connection, Netezza supports only one active SQL activity at a time.

This blog uses the nzsql command to show query and command examples Continue reading →