Qshell db2 utility

The db2 utility uses the SQL CLI (Call Level Interface) and allows you to run SQL statements directly, interactively, or from a file.

When processing SQL interactively or from a file, the db2 utility treats the backslash character at the end of a line as a continuation character. The backslash and newline character are removed and the remaining text is used as the SQL statement.

Syntax

db2 [General Options] [Delimiter Options] [Connection Options] [SQL Source Options]

General options

-v: Echo the SQL statement to standard output.
-S: Suppress spaces and padding in output, useful for viewing LOB columns containing text data.

Delimiter options

Only one of the following can be specified:

-T, character: Specified character is used as termination character.
-t: Use the semicolon as the statement termination character.
-d: Use exclamation point (!) as the termination character.

Connection Options

-r rdbname: Connect to specified remote database (must be name in WRKRDBDIRE). If not specified connection is to local database.
-u username: The user profile name for connecting to remote database, can only be used with -r option.
-p username: The password to use on remote database connection.

SQL source options

SQL Statement: SQL statement text. If statement contains spaces or shell characters, be sure to correctly quote on Qshell command line.
-f filename default_lib: Read and run SQL statements from the specified file. Default_lib parameter is optional. When specified, it is used as the default library/schema for all statements.
-i: Enter SQL statements interactively. Enter quit or exit to end interactive SQL session.

Special character and command support

Lines starting with two dashes ( -- ) are comments
Lines starting with an exclamation point are qshell commands
Lines starting with 'at' symbol (@) are CL commands
Connect command is ignored, utility uses local connection unless -r option is specified
Echo command is a command built in to the db2 utility and echoes the text
Exit or quit commands will end the db2 SQL session
Help and ? commands will list basic help
Terminate command is ignored

Example

db2 select constraint_name from qsys2.syscst
db2 -t -f mysqlfile.txt

Contents of mysqlfile.txt:

select constraint_name from qsys2.syscst;
create table qgpl.testtable (c1 integer);