The nzsql command

The nzsql command invokes a SQL command interpreter on the Netezza Performance Server host or on a Netezza Performance Server client system. You can use this SQL command interpreter to create database objects, run queries, and manage the database.

To run the nzsql command, enter:

nzsql [options] [security options] [dbname [user] [password]]

The following table describes the nzsql command parameters. For more information about the command parameters and how to use the command, see the IBM® Netezza® Database User’s Guide.

Table 1. nzsql command parameters
Parameters	Description
-a	Echo all input from a script.
-A	Use unaligned table output mode. This is equivalent to specifying -P format=unaligned.
-csv	CSV (comma separated values) table output mode.
-c <query>	Run only a single query (or slash command) and exit.
-d <dbname> or -D <dbname>	Specify the name of the database to which to connect. If you do not specify this parameter, the nzsql command uses the value specified for the NZ_DATABASE environment variable (if it is specified) or prompts you for a password (if it is not).
-schema <schemaname>	Specify the name of the schema to which to connect. This option is used for Netezza Performance Server Release 7.0.3 and later systems that are configured to use multiple schemas. If the system does not support multiple schemas, this parameter is ignored. If you do not specify this parameter, the nzsql command uses the value specified for the NZ_SCHEMA environment variable (if it is specified) or a schema that matches the database account name (if it is not and if enable_user_schema is set to TRUE), or the default schema for the database (otherwise).
-e	Echo queries that are sent to the server.
-E	Display queries generated by internal commands.
-f <file name>	Run queries from a file, then exit.
-F <string>	Set the field separator. The default: is a vertical bar (\|). This is equivalent to specifying -P fieldsep=<string>.
-h	Display help for the nzsql command.
-H	Set the table output mode to HTML. This is equivalent to specifying -P format=html.
-host <host>	Specify the hostname of the database server.
-l	List available databases, then exit.
-n	Disable readline mode. This is required when input uses a double-byte character set such as Japanese, Chinese, or Korean
-o <file name>	Send query output to the specified file or, if a vertical bar (\|) is specified instead of a file name, to a pipe.
-O <file name>	Send query output and any error messages to the specified file or, if a vertical bar (\|) is specified instead of a file name, to a pipe.
-P opt[=val]	Set the printing option represented by opt to the value represented by val.
-port <port>	Specify the database server port.
-pw <password>	Specify the password of the database user. If you do not specify this parameter, the nzsql command uses the value specified for the NZ_PASSWORD environment variable (if it is specified) or prompts you to enter a password (if it is not).
-q	Run quietly, that is, without issuing messages. Only the query output is returned.
-r	Suppress the row count that otherwise is displayed at the end of the query output.
-R <string>	Set the record separator. The default is the newline character. This is equivalent to specifying -P recordsep=<string>.
-s	Use single-step mode, which requires that each query be confirmed.
-S	Use single-line mode, which causes a newline character to terminate a query.
-t	Print rows only This is equivalent to specifying -P tuples_only.
-time	Print the time that is taken by queries.
-T <text>	Set the HTML table tag options such as width and border. This is equivalent to specifying -P tableattr=<text>.
-u <username> or -U <username>	Specifies the database user name. If you do not specify this parameter, the nzsql command uses the value specified for the NZ_USER environment variable (if it is specified) or prompts you to enter a user name (if it is not).
-v <name>=<value>	Set the specified session variable to the specified value. Specify this parameter once for each variable to be set, for example: `nzsql -v HISTSIZE=600 -v USER=user1 -v PASSWORD=password`
-V	Display version information and exit.
-w	Do not require a password for the database user. The password is supplied by other mechanisms (Kerberos, for example).
-W <password>	Specify the password of the database user. (Same as -pw.)
-x	Expand table output. This is equivalent to specifying -P expanded.
-X	Do not read the startup file (~/.nzsqlrc).
-securityLevel <level>	Specify the security level (secured or unsecured) for a client connection to the Netezza Performance Server system. This option does not apply when you are logged in to the Netezza Performance Server system and running the command. preferredUnSecured You prefer an unsecured connection, but you will accept a secured connection if the system is configured to use only secured connections. This is the default. preferredSecured You prefer a secured connection, but you will accept an unsecured connection if the system is configured to use only unsecured connections. onlyUnSecured You require an unsecured connection. If the system is configured to use only secured connections, the connection attempt is rejected. onlySecured You require a secured connection. If the system is configured to use only unsecured connections or has a release level that is earlier than 4.5, the connection attempt is rejected.
-caCertFile <path>	Specify the path to the root CA certificate file on the client system. This option is used by Netezza Performance Server clients that use peer authentication to verify the Netezza Performance Server host system. The default value is NULL, which skips the peer authentication process.
-z	Set the field separator for an unaligned output to zero byte.
-0	Set the record separator for an unaligned output to zero byte.

Within the nzsql command interpreter, enter \? for help.

General

\g [FILE]: Run a query. And results to a file or a |pipe. This has the same effect as terminating the query with a semicolon.
\q: Quit nzsql.

Help

\?: List and display help about all backslash commands.
\h <COMMAND>: Display help for all SQL commands.

Query buffer

\e [FILE]: Edits the current query buffer (or file) with the external editor. (Not supported on Windows.)
\p: Displays the contents of the query buffer.
\s [FILE]: Displays history or saves it to a file. (Not supported on Windows.)
\w [FILE]: Writes the query buffer to a file.

Input/output

\copy ...: Performs SQL COPY with data stream to the client host.
\echo [STRING]: Writes a string to the standard output.
\i FILE: Reads and executes queries from <file>.
\o [FILE]: Sends all query results to a file or |pipe.
\O [FILE]: Sends query output with errors to a file or |pipe.
\qecho [STRING]: Writes a string to the query output stream (see \o).

Informational

\d NAME: Describes a table (or view, sequence, synonym, an index).
\dO NAME: Describes a table or view in a sorted order.
\d{t|v|i|s|e|x}: Lists tables/views/indices/sequences/temp tables/external tables.
\d{m|y}: Lists materialized views/synonyms.
\dS{t|v|i|s}: Lists system tables/views/indexes/sequences.
\dM{t|v|i|s}: Lists system management tables/views/indexes/sequences.
\dp NAME: Lists user permissions.
\dpu NAME: Lists permissions granted to a user.
\dpg NAME: Lists permissions granted to a group.
\dpr NAME: Lists permissions granted to a role.
\dgp NAME: Lists grant permissions for a user.
\dgpu NAME: Lists grant permissions granted to a user.
\dgpg NAME: Lists grant permissions granted to a group.
\d{u|U}: Lists users/User Groups.
\d{g|G|Gr}: Lists groups/Group Users/Resource Group Users.
\da[+] [NAME]: Lists aggregates, + for additional fields.
\dd [object]: Lists a comment for object.
\df[+] [NAME]: Lists functions, + for additional fields.
\dl[+] [NAME]: Lists libraries, + for additional fields.
\do: Lists operators.
\dT: Lists data types.
\l[+]: Lists all databases, + for additional fields.

Formatting

\a

Toggles between unaligned and aligned mode.

\C [STRING]

Sets table title, or unset if none.

\f [STRING]

Shows or sets field separator for unaligned query output.

\H

Toggles HTML output mode (currently off).

\pset [NAME [VALUE]]

Table 2.
Options	Description
\pset	Shows all current print settings.
\pset border [<numeric>]	Sets border width.
\pset columns [<numeric>]	Sets target column width.
\pset csv_fieldsep [<character>]	Sets the field separator for the csv print format. <character> could be any one-byte character except '"', '\n', '\r'.
\pset expanded [on \| off \| auto]	Sets the print expanded representation.
\pset x [on \| off \| auto]
\pset vertical [on \| off \| auto]
\pset fieldsep [<string>]	Sets the field separator of a table.
\pset fieldsep_zero	Sets the field separator of a table to zero byte.
\pset footer [on \| off]	Enables/disables the "(x rows)" footer.
\pset format [unaligned \| aligned \| html \| latex \| latex-longtable \|asciidoc \|csv \|troff-ms \| wrapped]	Sets the printing format of a table. If no option supplied, it shows the current setting.
\pset linestyle [ascii \| old-ascii \| unicode]	Sets the border line styles.
\pset null [<string>]	Sets the string to be printed in place of a null value.
\pset numericlocale [on \| off]	Sets the comma separated mode for number.
\pset pager [on \| off \| always]	Sets whether to use pager for the output.
\pset pager_min_lines [<numeric>]	Sets the minimum number of pager lines.
\pset recordsep [<string>]	Sets the record (row) separator of a table.
\pset recordsep_zero	Sets the field separator of a table to zero byte.
\pset tableattr [<string>]	Specifies the attributes that are to be placed inside the HTML table tag in the html output format.
\pset title [<string>]	Sets the table title for any subsequently printed tables.
\pset tuples_only [on \| off]	Sets the print only data (no headers and footers) mode.
\pset t [on \| off]	Sets the print only data (no headers and footers) mode.
\pset unicode_border_linestyle [single \| double]	Sets the border line style of a Unicode table. If no option is supplied, it shows the current setting.
\pset unicode_column_linestyle [single \| double]	Sets the column line style of a Unicode table. If no option is supplied, it shows the current setting.
\pset unicode_header_linestyle [single \| double]	Sets the header line style of a Unicode table. If no option is supplied, it show the current setting.

For more information, you can see this page.

\t [on|off]

Shows only rows (currently off).

\T [STRING]

Sets the HTML <table> tag attributes, or unsets if none.

Connection

\act: Displays current active sessions.
\c[onnect] [DBNAME [USER] [PASSWORD]]: Connects to new database (currently 'TEST_NZSQL_PSET_DB').

Operating system

\time [on|off]: Toggles timing of commands (currently off).
\! [COMMAND]: Shell escape or command. (Not supported on Windows.)

Variables

\set [NAME [VALUE]]: Sets internal variable, or lists all if no parameters.
\unset NAME: Unsets (deletes) an internal variable.

nzsql behavior differences on UNIX and Windows clients

Starting in NPS release 7.2.1, the nzsql command is included as part of the Windows client kit. In a Windows environment, note that there are some behavioral differences when users press the Enter key or the Control-C key sequence than in a UNIX nzsql command line environment. The Windows command prompt environment does not support many of the common UNIX command formats and options. However, if your Windows client is using a Linux environment like cygwin or others, the nzsql.exe command could support more of the UNIX-only command line options noted in the documentation.

In a UNIX environment, if you are typing a multiline SQL query into the nzsql command line shell, the Enter key acts as a newline character to accept input for the query until you type the semi-colon character and press Enter. The shell prompt also changes from => to -> for the subsequent lines of the input.

MYDB.SCH(USER)=> select count(*) (press Enter)
MYDB.SCH(USER)-> from ne_part (press Enter)
MYDB.SCH(USER)-> where p_retailprice < 950.00 (press Enter)
MYDB.SCH(USER)-> ; (press Enter)

 COUNT
-------
  1274
(1 row)

In a UNIX environment, if you press Control-C, the entire query is cancelled and you return to the command prompt:

MYDB.SCH(USER)=> select count(*) (press Enter)
MYDB.SCH(USER)-> from ne_part (press Enter)
MYDB.SCH(USER)-> where p_retailprice < 950.00 (press Control-C)
MYDB.SCH(USER)=>

In a Windows client environment, if you are typing a multiline SQL query into the nzsql command line shell, the Enter key acts similarly as a newline character to accept input for the query until you type the semi-colon character and press Enter.

MYDB.SCH(USER)=> select count(*) (press Enter)
MYDB.SCH(USER)-> from ne_part (press Enter)
MYDB.SCH(USER)-> where p_retailprice < 950.00 (press Enter)
MYDB.SCH(USER)-> ; (press Enter)

 COUNT
-------
  1274
(1 row)

However, in a Windows environment, the Control-C or Control-Break key sequences do not cancel the multiline query, but instead, cancel only that line of the query input:

MYDB.SCH(USER)=> select count(*) (press Enter)
MYDB.SCH(USER)-> from ne_part (press Enter)
MYDB.SCH(USER)-> where p_retailprice < 950.00 (press Control-C)
MYDB.SCH(USER)-> ; (press Enter)

 COUNT
-------
 100000
(1 row)

The Control-C (or a Control-Break) cancelled the WHERE clause on the third input line, and thus the query results were larger without the restriction. In a single input line (where the prompt is =>, note that Control-C cancels the query and you return to the nzsql command prompt.

MYDB.SCH(USER)=> select count(*) from ne_part (press Control-C)
MYDB.SCH(USER)=>

nzsql requires the more command on Windows

When you run the nzsql command on a Windows client, you could see the error more not recognized as an internal or external command. This error occurs because nzsql uses the more command to process the query results. The error indicates that the nzsql command could not locate the more command on your Windows client.

To correct the problem, add the more.com command executable to your client system's PATH environment variable. Each Windows OS version has a slightly different way to modify the environment variables, so refer to your Windows documentation for specific instructions. On a Windows 7 system, you could use a process similar to the following:

Click Start, and then type environment in the search field. In the search results, click Edit the system environment variables. The System Properties dialog opens and displays the Advanced tab.
Click Environment variables. The Environment Variables dialog opens.
In the System variables list, select the Path variable and click Edit. The Edit System Variable dialog opens.
Place the cursor at the end of the Variable value field. You can click anywhere in the field and then press End to get to the end of the field.
Append the value C:\Windows\System32; to the end of the Path field. Make sure that you use a semi-colon character and type a space character at the end of the string. If your system has the more.com file in a directory other than C:Windows\System32, use the pathname that is applicable on your client.
Click OK in the Edit System Variable dialog, then click OK in the Environment Variables dialog, then click OK in the System Properties dialog.

After you make this change, the nzsql command should run without displaying the more not recognized error.