CONNECT
Valid in: ESQL, OpenAPI, ODBC, JDBC, .NET
The CONNECT statement connects the application to a database and, optionally, to a specified distributed transaction. The embedded SQL CONNECT statement connects an application to a database, like the operating-system-level sql and isql commands. The CONNECT statement must precede all statements that access the database. The CONNECT statement cannot be issued in a dynamic SQL statement. To terminate a connection, use the DISCONNECT statement.
The CONNECT statement has the following format:
EXEC SQL CONNECT dbname
[AS connection_name]
[SESSION session_number]
[IDENTIFIED BY username]
[DBMS_PASSWORD = dbms_password]
[OPTIONS = flag {, flag}]
[WITH HIGHDXID = value, LOWDXID = value]
dbname
Specifies the database to which the session connects. Dbname can be a quoted or unquoted string literal or a host string variable. If the name includes any name extensions (such as a system or node name), string literals must be quoted.
connection_name
Specifies an alphanumeric identifier to be associated with the session. The connection name must be a string of up to 128 characters that identifies the session. If the as connection_name clause and the session clause are omitted, the default connection name is the specified database name.
Connection_name must be specified using a quoted string literal or a host language variable.
session_number
Specifies a numeric identifier to be associated with the session. The session number must be a positive integer literal or variable, and must be unique among existing session numbers in the application.
username
Specifies the user identifier under which this session runs. Username can be specified using a quoted or unquoted string literal or string variable.
dbms_password
Specifies the valid password either as string constant or a string program variable. This parameter allows the application to specify the password at connection time if required.
flag
Specifies runtime options for the connection. Valid flags are those accepted by the sql command. Flags specific to the Terminal Monitor are not valid. For more information about these flags, see the Command Reference Guide.
The maximum number of flags is 12.
Common flags are:
-uusername
Specifies the effective user for the session.
-Ggroupid
Specifies a group identifier.
-Rroleid
Specifies a role identifier for the session. If the role ID has a password, use the format:
'-Rroleid/password '
+user[=username[,password]]
Passes username and password as the DBMS_authentication user and password. If +user is specified, the database name must not include [user,password] syntax. If username is omitted, the name of the logged-in OS user is used. If no password is specified, the user is prompted to enter a password. The syntax ‑user[=username[,password]] is also valid.
The flags can be specified using quoted or unquoted character string literals or string variables.
value
Highdxid specifies the high-order 4 bytes of a distributed transaction ID. Lowdxid specifies the low-order 4 bytes of a distributed transaction ID. These options are used for two-phase commit of distributed transactions. For details, see the chapter “Working with Transactions and Handling Errors.”
Permissions
This statement is available to all users.
To use the IDENTIFIED BY clause, you must be one of the following:
• The DBA of the specified database
• A user with the SECURITY privilege
• A user that has been granted the DB_ADMIN privilege for the database
Locking
The CONNECT statement takes a database lock on the specified database. Unless an exclusive lock using the -l flag is explicitly requested, the database lock is a shared lock.
Related Statements
SET
DISCONNECT
Last modified date: 08/29/2024