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'
No comments:
Post a Comment