[HANA] hdbsql options

SAP HANA HDBSQL Options

Set information for the database and for database objects by using SAP HANA HDBSQL commands.

In addition to SAP HANA HDBSQL commands, you can also enter an SQL statement or a database procedure. The statement or procedure must be enclosed in quotation marks.

Configuration Options

Use the following options to modify the operation of SAP HANA HDBSQL commands.

Database SessionOptionDescription

-attemptencrypt	Specifies that encrypted data transmission is used. If the connection fails, then it attempts to use unencrypted connections.
-d <database-name>	Specifies the name of the multitenant database container in a multiple-container system.
-e	Specifies that encrypted data transmission is used.
-i <instance-number>	Specifies the instance number of the system.
-n <host>[:<port>]	Specifies the name of the computer on which the system is installed and optionally, the port number.
-p <database-user-password>	Specifies the password for logging on to the database.
-proxyhost<hostname>	Connects to a SOCKS5 proxy located at <hostname>.
-proxypassword<pwd>	(Optional) Authenticates against a SOCKS5 proxy with <pwd>.
-proxyport<port>	(Optional) Connects to a SOCKS5 proxy using <port>. The default is 1080.
-proxyservicename<proxy-service-name>	(Optional) Specifies the proxy service name registered in Kerberos (for METHOD 01 authentication). The default is socks@proxyhost (in GSSAPI format). On Microsoft Windows, this option is mandatory, with the default being service/proxyhost@REALM (in SSPI format).
-proxyuserid<userid>	(Optional) Authenticates against a SOCKS5 proxy using <userid>.
-r	Enforces the execution of SQL statements as statements rather than as prepared statements.
-r	Suppresses the use of prepared statements.
-saml-assertion <file>	Uses a file to provide a SAML assertion.
-S <sql-mode>	Specifies the SQL mode, either INTERNAL or SAPR3.
-u <database-user>	Specifies the user name for logging on to the database.
-U <user-store-key>	Uses credentials from the user store.
-V <variable-definition>[,…] [prompt] | [noprompt]	Specifies a variable. <variable-definition> can be one of the following: ExplicitExplicitly define the variable using <variable-name>=<variable-value>, with no spaces between the variable name, the equal sign (=), and the value.PositionalDefine the variable according to its position on the command line by using <variable-value>. The variable declaration's position on the command line is relative to other positional variables and determines which parameter it is replacing. For example, <value 1>, <value 2>means that &1 in the SQL script is replaced by <value 1>, and &2 is replaced by <value 2>. Spaces are only allowed if the entire list of definitions is quoted and special characters are escaped. Specify [prompt] to be prompted for a definition whenever an undefined variable is encountered. Specifying [noprompt] means that undefined variables are ignored and the command may fail if it contains undefined variables. The default is [noprompt].
-z	Switches off AUTOCOMMIT mode.

Input and OutputOptionDescription

-c <separator>	Specifies the separator used to separate individual commands when importing commands from a file. The default value is ;.
-I <file>	Imports commands from a batch file.
-m	Activates multiple-line mode for entering SAP HANA HDBSQL commands.
-o <file>	Writes the results to a file.
-x	Suppresses additional output, such as the number of selected rows in a result set.
-resultencoding<encoding>	Forces output encoding for result data. Can be one of UTF8, LATIN1, or AUTO(the default).
-qto | -querytimeout	Sets a server-side timeout for all SQL operations, in seconds. If any SAP HANA HDBSQL SQL operation exceeds the maximum timeout value on the server, then it is canceled with a server error message. Setting a timeout value of 0 disables the timeout (the default).
-quiet	Hides the SAP HANA HDBSQL welcome banner.
-separatorownline	Deprecated, do not use. Nested BEGIN...END blocks are supported by default without modifying the input.
-strictSeparatorLine	Removes the parsing of a single quote, double quote, and BEGIN...END nesting. Separator line matching is strict and no leading or trailing spaces are allowed. When -c is not used, the default separator is a semi-colon on its own line. Batch tracing (-f or -fn) also produces output for comment-only batches even when they are not sent to the server.
-br <reset-command>	Specifies a reset command (for example, reset), which tells SAP HANA HDBSQL to ignore the most recent query sent to the server. To specify batchreset, you must also set the separatorownline option. The reset command should not be an SQL statement or a separator. Setting batchreset on the same line as the separator command results in an error because it is not a valid query.
-printoutput { OFF| MESSAGE }	Specifies how to handle the SQLSCRIPT_PRINT library output. The parameters are case sensitive. MESSAGE This is the default setting if you do not specify -printoutput. MESSAGE causes the SQLSCRIPT_PRINT library output to be printed on standard output. The -x option suppresses the SQLSCRIPT_PRINT library output even if -printoutput MESSAGE is set. OFF OFF suppresses output from the SQLSCRIPT_PRINT library when you call a stored procedure.
-history <#>	Specifies the number of items to keep in the history buffer. The default is 50.
-nochop	Specifies not to remove trailing blanks from a character output.
-serverstats | -sstats	Retrieves server statistics, including CPU time, processing time, and memory usage.

Formatting OutputOptionDescription

-A	Returns the result set in an aligned format.
-a	Suppresses the output of the column names in the result set.
-C	Suppresses escape output format.
-b<maximum-length> | all	Defines the maximum number of characters for the output of LOB values (the default value is 32 bytes). Specifying -b all displays the whole binary length.
-f	Returns all SQL statements that are sent to the database instance.
-fn	Returns all SQL statements that are sent to the database instance and formats them with numbered lines. Numbered lines make it easier to determine on which file line a potential error has occurred.
-F<separator>	Specifies which string SAP HANA HDBSQL uses as a separator between the individual columns of the result set. The default value is |.
-g <null-value>	Specifies the character for NULL values in the result set. The default value is ?.
-p <prefix>	Use <prefix> as the row prefix for printing. The default value is |.
-q <suffix>	Use <suffix> as the row suffix for printing. The default value is |.
-oldexectimes	Uses SAP HANA 1.0 execution-only timing. SAP HANA HDBSQL in SAP HANA 1.x only reports time for client and server executions, not fetches for result sets. As of SAP HANA 2.0, SAP HANA HDBSQL includes times for executions and fetches by default.
-Q	Outputs each column of the result set in a new row.
-j	Switches off the page-by-page scroll output. In the interactive prompt, the result output is always paged to the utility specified by the PAGER environment variable, or the “more” utility (“less” on Mac OS) if unspecified.

NoteBy default, SAP HANA HDBSQL removes whitespace of the end of column data values. To prevent this, add -Z CHOPBLANKS=0 to the SAP HANA HDBSQL command line.

OtherOptionDescription

-h	Displays the help.
-t	Outputs debug information.
-T <file>	Activates the SQLDBC trace, which writes the trace data to the specified file.
-v	Displays version information about the SAP HANA HDBSQL program.

SSL OptionsOptionDescription

-sslprovider<provider>	Specifies the cryptographic service provider that is used for SSL connections (one of commoncrypto, sapcrypto, or mscrypto).
-sslkeystore <key-store-file>	Specifies the SSL keystore name.
-ssltruststore<trust-store-file>	Specifies the SSL truststore name.
-ssltrustcert<certificate-file>	Skips certificate validation.
-sslhostnameincert<hostname>	Specifies the hostname of the server for which the certificate has been granted.
-sslcreatecert	Creates a self-signed certificate.

Interactive Options

Use the following options when operating SAP HANA HDBSQL in interactive mode.

CommandDescription

\? \h[elp]	Displays all HDBSQL commands.
\a[utocommit][ON|OFF]	Switches AUTOCOMMIT mode on or off.
\al[ign][ON|OFF]	Controls whether SQL statement results are formatted.
\es[cape][ON|OFF]	Switches the escape output format on or off.
\c[onnect]	Logs a user onto the database.
\dc[<pattern>]	Lists all table columns that correspond to the specified [<pattern>] and to which the current user has access. [<pattern>] is specified as follows: [<schema>.] [<object-name>]. The following placeholders are supported: For one character: _ For any number of characters: % If a pattern is not specified, then the system returns information about all table columns to which the current user has access. This command returns the following information: Column name Data type Column length Null value permitted or not Position of the column in primary key of table (if applicable)
\de[<pattern>]	Lists all the indexes of database objects that correspond to the specified [<pattern>]. [<pattern>] is specified as follows: [<schema>.] [<object-name>]. The following placeholders are supported: For one character: _ For any number of characters: % If a pattern is not specified, then the system returns information about all indexes for database objects to which the current user has access. This command returns the following information: Index name Columns contained in the index Position of column in the index Specifies whether the index is UNIQUE Sort sequence
\di[sconnect]	Logs the user off of the database.
\dp[<pattern>]	Lists all database procedures that correspond to the specified [<pattern>]. [<pattern>] is specified as follows: [<schema>.] [<object-name>]. The following placeholders are supported: For one character: _ For any number of characters: % If a pattern is not specified, then the system returns information about all database procedures to which the current user has access. This command returns the following information: Schema name Name of the database procedure Package to which the database procedure is assigned
\ds [<name>]	Lists all schemas that correspond to the specified [<name>]. [<name>] is specified as follows: [<schema>.] [<object-name>]. The following placeholders are supported: For one character: _ For any number of characters: % If a pattern is not specified, then the system returns information about all schemas to which the current user has access. This command returns the following information: Schema Name Owner
\dt[<pattern>]	Lists all tables that correspond to the specified [<pattern>]. [<pattern>] is specified as follows: [<schema>.] [<object-name>]. The following placeholders are supported: For one character: _ For any number of characters: % If a pattern is not specified, then the system returns information about all tables to which the current user has access. This command returns the following information: Schema name Table name Table type
\du [<name>]	Lists all database users that correspond to the specified [<name>]. [<name>] is specified as follows: [<schema>.] [<object-name>]. The following placeholders are supported: For one character: _ For any number of characters: % If a name is not specified, then the system returns information about all database users to which the current user has access. This command returns the following information: Name of the database user User properties
\dv[<pattern>]	Lists all views that correspond to the specified [<pattern>]. [<pattern>] is specified as follows: [<schema>.] [<object-name>]. The following placeholders are supported: For one character: _ For any number of characters: % If a pattern is not specified, then the system returns information about all views to which the current user has access. This command returns the following information: Schema name View name View types
\e[dit] [<file>]	Writes the command buffer to the specified file where you can edit it with an editor.
\f[ieldsep]<separator>	Uses the specified separator character to separate the individual fields of the result. The default is ,.
\g	Executes the commands in the command buffer and returns the results.
\hi[story] <#>	Specifies the number of items to keep in the history buffer. The default is 50.
\i[nput] <file>	Imports commands from the specified batch file.
\m[ode] { INTERNAL | SAPR3 }	Changes the SQL mode.
\mu[ltiline] { ON | OFF }	Switches multiple line mode on or off.
\o[utput]<file>	Redirects the result to a file.
\pa[ger]	Displays results consecutively (not page by page).
\p[rint]	Displays the current command buffer.
\qto |\querytimeout	Sets a server-side timeout for all SQL operations, in seconds. If any SAP HANA HDBSQL SQL operation exceeds the maximum timeout value on the server, then it is canceled with a server error message. Setting a timeout value of 0 disables the timeout. This is the default.
\q[uit]	Exits HDBSQL.
\r[eset]	Deletes the current command buffer.
\read <file>	Reads commands from the specified batch file.
\ro[wsep]<separator>	Uses the specified separator character to separate the individual rows of the result.
\s[tatus]	Displays general information about the database.
\vl-	Lists all currently defined variables.
\vd <variable-name><value>	Defines a <variable-name> to be replaced by <value>, or replaces the value stored with a new value if <variable-name> already is defined.
\vu <variable-name>	Undefines the specified variable.
\vc	Clears all of the currently defined variables.
\vs { ON | OFF}	Switches variable substitution on or off.
\ve { ON | OFF}	Switches variable escaping on or off.
\vp { ON | OFF}	Switches variable prompting on or off.