nzsql Part-1

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

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 < 1000;
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'