dbsql command

Use the dbsql command to issue a single query or command, or to open an interpreter from which you can issue queries and commands interactively.

You can use the dbsql command to run SQL queries. You can also use it to run internal commands called slash commands, because each is preceded by a backslash (\). For more information about slash commands, see dbsql internal slash commands.

You can run the dbsql command from remote systems that have installed the IBM® Db2® Warehouse on Cloud support tools. Based on your database user account privileges, you can use the SQL command interpreter to run queries, or to perform administrative actions such as creating and dropping objects and managing the database.

The basic command syntax is as follows:

dbsql [options] [security options]

The way in which you run the command depends on where you obtained the IBM Db2 Warehouse on Cloud support tools. For information about how to run the command, see Db2 support tools overview.

Note: When running dbsql, either directly as the root user or by using the docker exec -it Db2wh dbsql command, you must specify its absolute path in the container file system, for example:

dbsql -f /mnt/clusterfs/scratch/query.sql

If you invoke a query directly from a docker exec command, the following rules apply:

If you are using a product container, enclose the query in single quotation marks, as in the following example:
```
docker exec -it Db2wh dbsql ... -c '"SELECT * FROM test_table"'
```
If you are using a client container, there is no need to enclose the query in single quotation marks.
If a query contains a mix of single and double quotation marks, escape the double quotation marks with a backslash (\), as in the following example:
```
docker exec -it Db2wh dbsql ... -c '"CREATE' SCHEMA '\"MixedCase'\''WithQuotes\""'
```
If a query contains single quotation marks, enclose the query in escaped double quotation marks, as in the following example:
```
docker exec -it Db2wh dbsql ... -c "\"INSERT INTO dbsql_tbl VALUES(1, 'aaaaa');\""
```

Table 1. dbsql command options
Options	Description
-a	Echo all input from a script.
-A	Use unaligned table output mode. This is equivalent to specifying -P format=unaligned.
-c <query>	Run only a single query (or slash command) and exit. If you invoke a query directly from the command line, you need to enclose the query in double quotes.
-caCertFile <certfile>	Use CA certificate provided in <certfile> to verify database server. Must enable ssl.
-d <dbname> or -D <dbname>	Specify the name of the database to which to connect. If you do not specify this parameter, the dbsql command prompts you to specify a database.
-schema <schemaname>	Specify the name of the schema to which to connect. If you do not specify this parameter, the dbsql command uses the default schema, which is a schema that matches the database user name.
-e	Echo queries that are sent to the server.
-E	Display queries that internal commands generate.
-f <file name>	Run queries from the specified file and then exit. You must specify the file's absolute path in the container file system, for example: `dbsql -f /mnt/clusterfs/scratch/query.sql`
-F <string>	Set the field separator. The default: is a vertical bar (\|). This is equivalent to specifying -P fieldsep=<string>. For any binary, control, or non-printable character, prefix the value with a dollar sign ($) character. For example, specify dbsql -F $'\t' for a Tab value.
-h (or -? or --help)	Display help for the dbsql command.
-host <host> or -h <host>	Specify the hostname of the database server. The default is the domain socket.
-H	Set the table output mode to HTML. This is equivalent to specifying -P format=html.
-l	List the available databases, then exit.
-n	Disables readline. Required when dbsql is used with an input method such as Japanese, Chinese, or Korean.
-o <filename>	Send query output to the specified file or, if a vertical bar (\|) is specified instead of a file name, to a pipe.
-O <filename>	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.
-port \| -p `<port>`	Specify the database server port. This cannot be a port for which SSL is configured. The default is 50000.
-P var[=arg]	Set the printing option represented by var to the value represented by arg. The var options include the following: fieldsep=`string` Set the field separator. The default is a vertical bar (\|). recordsep=`string` Set the record separator. The default is the newline character. border=`value` Set the HTML table border size. expanded Expand table output. tuples_only Print rows only. title=`string` Set the HTML table title. tableattr=`text` Set the HTML table tag options such as width and border. null=`string` Display specified string as null value. format=`format` Specify unaligned to use unaligned table output mode, html to use HTML table output mode, or latex to use LaTeX format.
-pw <password>	Specify the password of the database user. If you do not specify this parameter, the dbsql command prompts you to enter a password.
-q	Run quietly, that is, without issuing messages. Only the query output is returned.
-r	Suppress the row count that is displayed by default 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>.
-rev or -Rev	Show the software version information and exit.
-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.
-ssl	Enable ssl-secured connection.
-t	Print rows only. This is equivalent to specifying -P tuples_only.
-terminator <char>	Set SQL query terminator to a specified character , Default is ';'
-time	Print the elapsed time for the query to complete.
-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 dbsql command prompts you to enter a user name. Note: The user name parameter is case sensitive.
-v <name>=<value>	Set the specified dbsql session variable to the specified value. Specify this parameter once for each variable to be set, for example: `dbsql -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> or -pw <password>	Specify the password of the database user.
-x	Enable expanded table output. This is equivalent to specifying -P expanded.
-X	Do not read the startup file (~/.dbsqlrc).
-securityLevel <level>	This option is not supported for Db2 environments.
-caCertFile <path>	This option is not supported for Db2 environments.
-nps	This option sets the SQL_COMPAT variable to NPS. `SQL_COMPAT=NPS`.
-dac	This option disables AUTOCOMMIT.

Examples

You can open an interactive SQL terminal by not specifying the -c option, for example:

dbsql -d bludb -schema ap_region -u adminid -pw password

A root user can, as an alternative, issue the following command from the image container host's command line:

docker exec -it Db2wh dbsql -d bludb -schema ap_region -u adminid -pw password