Using nzsql Commands

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 < foo.sql

�� 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.

No comments:

Post a Comment