sql Command--Start the Line-based SQL Terminal Monitor
The sql command invokes the line-based Terminal Monitor for SQL.
This command has the following format:
sql [SQL option flags] [line-mode flags] dbname [<altin] [>altout]
The sql command accepts a variety of flags that define how the DBMS Server and Terminal Monitor operate during your session.
SQL option flags
Specifies flags that can be used with the line-based Terminal Monitor and other commands where noted. The SQL option flags determine the format of output or the behavior of the DBMS. You can specify a maximum of twelve SQL option flags. The flags are as follows:
-cN
Sets the minimum field width for printing character columns to N. The default is 6.
-fkxM.N
Sets floating-point output column width to M characters (total), including N decimal places, and (if warranted) e+-xx and the decimal indicator character itself.
k can be 4 or 8 to apply to f4’s or f8’s respectively.
x can be E, F, G or N (uppercase or lowercase) to specify an output format. E indicates exponential format. F indicates floating-point format. G indicates the floating-point format and guarantees decimal alignment. N indicates floating-point format, decimal alignment, and right-justification.
If you specify N or G and the number is too large for the format indicated by the flag, it is displayed in exponential format. To prevent this format overflow, M should be greater than or equal to N + 7.
The default display format for both f4 and f8 is n10.3, unless your computer supports the IEEE standard for floating-point numbers, in which case the display format for f4 and f8 is n11.3.
-ikN
Sets integer output column width to N. k can be 1, 2, or 4 for i1’s, i2’s, or i4’s, respectively. The default for N is 6 for i1 and i2 fields, and 13 for i4 fields.
-tN
Sets the minimum field width for printing text columns to N. The default is 6.
+U|-U
Enables (+U) or disables (-U) user updating of the system catalogs, and takes an exclusive lock on the database. To update system catalogs, you must have the update system tables privilege obtained through accessdb.
+Y|-Y
Enables (+Y) or disables (-Y) user updating of the system catalogs, but does not take an exclusive lock on the database.
-uusername
Specifies the effective user for the session.
Double quotes used for delimited names must be escaped. For example: sql mydb –u\"milo-032345\"
-Ggroupid
Specifies a group identifier.
-Rroleid
Specifies a role identifier for the session.
-l
Locks the database for your exclusive use. When you specify this flag, no one else can open the database while you are in it. If you attempt to take an exclusive lock on a database that is in use, the system informs you that the database is temporarily unavailable.
+w|-w
Indicates to wait (+w) or not wait (-w) for the database. The default is ‑w. If you specify +w, you must wait if certain processes are running (sql –l, sql –U, verifydb, rollforwarddb, or sysmod) on the given database, before the operation proceeds.
-string_truncation = fail | ignore
Sets error handling mode for string truncation errors. This error occurs if you attempt to insert a string into a table column that is too short to contain the value.
fail—Aborts the statement and issues an error message. The FAIL option is supported only for singleton INSERTs and COPY. It is supported only in cases where the literal string value can be validated, so it is not supported for CREATE TABLE AS SELECT, INSERT...SELECT, nor for updates where the string value originates from an expression from another table.
ignore—(Default) Truncates and inserts the string without issuing an error message.
line-mode flags
Specify flags that affect Terminal Monitor behavior and that can be used with the line-based Terminal Monitor only. The flags are as follows:
+a|-a
Sets (+a) or clears (-a) the autoclear option in the terminal monitor. When disabled, the query buffer is never automatically cleared; it is as if the \append command was inserted after every \go. This flag requires the query buffer to be cleared using \reset after every query. The default is +a.
+d|-d
Displays (+d) or does not display (-d) the dayfile (a text file that displays the date and time and other information). The default is +d.
+s|-s
Displays (+s) or does not display (-s) status messages. -s suppresses all status messages including login and logout messages, the dayfile, and prompts. Error messages are not suppressed. The default is +s.
-S
Runs the Terminal Monitor in silent mode, which shows query output only, and suppresses headers, footers, separators, lines, and row counts. This allows simple reports to be created as SQL scripts and then run without having to edit the output.
-vX
Sets the column separator to the character specified by X. The default is vertical bar (|).
-Ppassword
Specifies the user password.
-Rrole-name/role-password
Identifies the role name and optional role password. Separate the name and password with a slash (/).
-history_recall | ‑nohistory_recall
(Linux only) Invokes the terminal monitor with or without history recall functionality. History recall lets you retrieve the history of commands typed in the session, and perform other functions.
left- and right- arrow
Browses the line entered.
Backspace
Erases a character to the left of the cursor.
Up- and Down- arrow
Retrieves the history of the commands typed in this session.
Ctrl+U
Erases the line.
Ctrl+K
Erases the line from the cursor to the end.
dbname
Specifies the name of the database.
<altin
Specifies a file from which the Terminal Monitor reads commands. The file must contain all terminal monitor commands needed to run the session.
>altout
Directs output from the Terminal Monitor to the specified file. If you specify this parameter, you will not see any output.