IBM PureData System for Analytics, Version 7.1

Slash options

When you use the nzsql command in interactive mode, there are some options that you can specify. These options are called slash options because each is preceded by a backslash (\). Many slash options correspond to options available for commands entered using the command line interface.

The following list describes the most commonly used slash options. For a complete list, see Table 1.

\d

Describe a table, view, sequence, or synonym.

Displays the DDL for the specified object. All the \d description options are valid only for the current database and current schema. If you have multiple schemas in a database, use the SET SCHEMA command to connect to the schema that contains the objects that you want to describe.

\dt

List tables.

Lists the tables in the current database.

\dv

List views.

Lists the views in the current database.

\dSt

List system tables.

Lists the IBM® Netezza® internal tables if you are the admin user. If you are another user, you must have the appropriate privileges. Internal tables begin with _t_.

Restriction: Do not modify these tables because changes can affect the integrity of your system.

\dSv

List system views.

Lists the Netezza internal views if you are the admin user. If you are another user, you must have the appropriate privileges. Internal views begin with _v_.

\du and \dU

List users and the groups that the users belong to.

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 with Guaranteed Resource Allocation (GRA) percentages.

\echo <text>

Write text to standard output.

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

   nzsql <<eof
 \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. For a list of all the SQL commands and their syntax, see Netezza SQL command reference

\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 ending your nzsql session. You can use this option to issue shell commands between SQL statements, which is a useful technique for scripts.

   nzsql <<eof
 \!  date 
 SELECT COUNT(*) FROM customer;
 \!  date
 eof

The example produces the following output:

   Wed Jun 27 11:23:50 EDT 2014
 count
-------
 12399
(1 row)
Wed Jun 27 11:23:50 EDT 2014

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.

Set the variable:

   \set my_sql 'SELECT * FROM sales_tbl WHERE amt > '

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