dbsql internal slash commands

The dbsql internal slash commands begin with a backslash (\) and simplify many of the common tasks and commands within the dbsql command terminal.

The following table describes the dbsql internal slash options. You can display this list using the \? command while you are logged in to the dbsql command interpreter.

Table 1. Internal slash option for dbsql
Option Description
\a Toggles between unaligned and aligned mode.
\act This option is not supported.
\c[onnect] [dbname [user] [password]] Connects to a new database.
\C title HTML table title.
\copy ... This option is not supported.
\d table Describes the table (or view, index, sequence, synonym) by displaying 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.

For the \d options, you can add a plus sign (+) for verbose output. For example, \d+ table.
\dO name Lists the columns for a table or a view in alphabetical order by column name.
\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 if you are the admin user. If you are another user, you must have the appropriate privileges.
\dM{t|v|i|s} This command is not supported on a Db2® warehouse.
\dp name Lists user permissions.
\dpr name Lists permissions that are granted to a role.
\dpu name Lists permissions that are granted to a user.
\dpg name Lists permissions that are granted to a group.
\dgp name Lists grant permissions for a user.
\dgpr name Lists grant permissions that are granted to a role.
\dgpu name Lists grant permissions that are granted to a user.
\dgpg name Lists grant permissions that are granted to a group.
\d{u|U|UR} List the database users/user groups/user roles.
\d{g|G|GR} List the database groups/group users/group roles.
\d{r|R|RG|RR} Lists the roles/role users/role groups/role roles (the roles assigned to a role).
\da[+] name Lists user-defined aggregates, + for more fields.
\dd [object] Lists comment for table, type, function, or operator.
\df[+] name Lists functions, + for more fields.
\dl[+] name This command is not supported on a Db2 warehouse.
\do This command is not supported on a Db2 warehouse.
\dT Lists data types.
\e [file] Edits the current query buffer or [file] with external editor.
\echo text Writes the text to standard output. Use this option to include descriptive text between SQL statements. This is useful when you are writing scripts, as in the following example:
   dbsql <<eof
 \echo Rowcount before the truncate
 SELECT COUNT(*) FROM customer;
 \echo Rowcount after the truncate
 TRUNCATE TABLE customer;
 SELECT COUNT(*) FROM customer;
 eof
\f sep Changes the field separator.
\g [file] Run a query, and if a file or pipe (|) character is specified, send the output to the file or pipe. This has the same effect as terminating the query with a semicolon.
\h This option is not supported on a Db2 warehouse. For more information about the SQL commands supported with Db2 environments, see the IBM Knowledge center.
\H Toggles HTML mode (the default is off).
\i file Reads and executes queries from file.
Note: For Db2 Warehouse, when running dbsql as user root or by using docker exec -it dashDB dbsql, you have to provide an absolute file path in the container file system. An example follows:
dbsql \-i /mnt/clusterfs/scratch/query.sql
\l Lists all databases.
\o [file] Sends all query results to [file], or pipe.
\O [filename] Send query output with errors to filename (or pipe).
\p Shows the content of the current query buffer.
\pset opt Set the table output options. The value opt can be set to one of the following: format, border, expanded, fieldsep, null, recordsep, tuples_only, title, tableattr, or pager.
\q Quits or closes the dbsql command line interpreter.
\qecho text Writes text to query output stream (see \o).
\r Resets (clears) the query buffer.
\s [file] Prints the history or saves it in [file].
\set var value Sets an internal variable. The \set specified without any variable or argument displays a list of the current session variables and their values.
\t Toggles the display to tuples/rows only. The default is off to show column headings and rows.
\time Toggles the display of the elapsed time for queries. The default is off to not show the elapsed time.
\T tags HTML table tags.
\unset var Unsets (deletes) the internal variable.
\w file Writes current query buffer to file.
\x Toggles expanded output (currently off).
\! [cmd] Runs a shell command.
Use this option to run a shell command without ending your dbsql session. You can use this option to issue shell commands between SQL statements, which is a useful technique for scripts. The following example calls the date command to record time values before and after a query:
   dbsql <<eof
 \!  date 
 SELECT COUNT(*) FROM customer;
 \!  date
 eof