CONNECT (type 1) statement
The CONNECT (Type 1) statement connects an application process to the identified application server according to the rules for remote unit of work.
An application process can only be connected to one application server at a time. This is called the current server. A default application server may be established when the application requester is initialized. If implicit connect is available and an application process is started, it is implicitly connected to the default application server. The application process can explicitly connect to a different application server by issuing a CONNECT statement. A connection lasts until a CONNECT RESET statement or a DISCONNECT statement is issued or until another CONNECT statement changes the application server.
Although an interactive SQL facility might provide an interface that gives the appearance of interactive execution, this statement can only be embedded within an application program. It is an executable statement that cannot be dynamically prepared. When invoked using the command line processor, additional options can be specified.
For more information, refer to Using command line SQL statements and XQuery statements.
CONNECT processing goes through two levels of access control. Both levels must be satisfied for the connection to be successful.
- CONNECT authority
- SECADM authority
- DBADM authority
- SYSADM authority
- SYSCTRL authority
- SYSMAINT authority
- SYSMON authority
- CONNECT (with no operand)
- Returns information about the current server. The information is returned in the SQLERRP field
of the SQLCA as described in "Successful Connection".
If a connection state exists, the authorization ID and database alias are placed in the SQLERRMC field of the SQLCA. If the authorization ID is longer than 8 bytes, it will be truncated to 8 bytes, and the truncation will be flagged in the SQLWARN0 and SQLWARN1 fields of the SQLCA, with 'W' and 'A', respectively.
If no connection exists and implicit connect is possible, then an attempt to make an implicit connection is made. If implicit connect is not available, this attempt results in an error (no existing connection). If no connection, then the SQLERRMC field is blank.
The territory code and code page of the application server are placed in the SQLERRMC field (as they are with a successful CONNECT statement).This form of CONNECT:
- Does not require the application process to be in the connectable state.
- If connected, does not change the connection state.
- If unconnected and implicit connect is available, a connection to the default application server is made. In this case, the country or region code and code page of the application server are placed in the SQLERRMC field, like a successful CONNECT statement.
- If unconnected and implicit connect is not available, the application process remains unconnected.
- Does not close cursors.
- TO server-name or host-variable
- Identifies the application server by the specified server-name or a
host-variable which contains the server-name.
If a host-variable is specified, it must be a character string variable with a length attribute that is not greater than 8, and it must not include an indicator variable. The server-name that is contained within the host-variable must be left-aligned and must not be delimited by quotation marks.
Note that the server-name is a database alias identifying the application server. It must be listed in the application requester's local directory.
When the CONNECT statement is executed, the application process must be in the connectable state.
- Successful Connection
- If the CONNECT statement is successful:
- All open cursors are closed, all prepared statements are destroyed, and all locks are released from the previous application server.
- The application process is disconnected from its previous application server, if any, and connected to the identified application server.
- The actual name of the application server (not an alias) is placed in the CURRENT SERVER special register.
- Information about the application server is placed in the SQLERRP field of the SQLCA. If the application server is an IBM® product, the information has the form pppvvrrm:
- The SQLERRMC field of the SQLCA is set to contain the following values (separated by X'FF')
- The country or region code of the application server.
- The code page of the application server,
- The authorization ID (up to first 8 bytes only),
- The database alias,
- The platform type of the application server.
- The agent ID. It identifies the agent executing within the database manager on behalf of the application. This field is the same as the agent_id element returned by the database monitor.
- The agent index. It identifies the index of the agent and is used for service.
- If, as indicated by token 10, the server instance operates in a partitioned environment, this token represents the member number. If the server instance operates in a BigSQL environment, as indicated by SQLWARN0 set to 'W' and SQLWARN4 set to 'B', this token represents the member number.
- The code page of the application client.
- If this value is non-zero but neither SQLWARN0 nor SQLWARN4 is set, it represents the number of members in a partitioned environment.
- The SQLERRD(1) field of the SQLCA indicates the maximum expected difference in length of mixed character data (CHAR data types) when converted to the database code page from the application code page. A value of 0 or 1 indicates no expansion; a value greater than 1 indicates a possible expansion in length; a negative value indicates a possible contraction.
- The SQLERRD(2) field of the SQLCA indicates the maximum expected difference in length of mixed character data (CHAR data types) when converted to the application code page from the database code page. A value of 0 or 1 indicates no expansion; a value greater than 1 indicates a possible expansion in length; a negative value indicates a possible contraction.
- The SQLERRD(3) field of the SQLCA indicates whether or not the database on the connection is
updatable. A database is initially updatable, but is changed to read-only if a unit of work
determines the authorization ID cannot perform updates. The value is one of:
- 1 - updatable
- 2 - read-only
- The SQLERRD(4) field of the SQLCA returns certain characteristics of the connection. The value
is one of:
- N/A (only possible if running from a client which is not at the latest level, is one-phase commit, and is an updater).
- one-phase commit.
- one-phase commit; read-only (only applicable to connections to DRDA1 databases in a TP Monitor environment).
- two-phase commit.
- The SQLERRD(5) field of the SQLCA returns the authentication type for the connection. The value
is one of:
- Authenticated on the server.
- Authenticated on the client.
- Authenticated on the server with encryption.
- Authenticated using an external Kerberos security mechanism.
- Authenticated using an external GSS API plug-in security mechanism.
- Authenticated on the server, which accepts encrypted data.
- Authentication not specified.
- The SQLERRD(6) field of the SQLCA returns the database partition number of the database partition to which the connection was made if in a partitioned database environment. Otherwise, a value of 0 is returned.
- The SQLWARN1 field in the SQLCA will be set to 'A' if the authorization ID of the successful connection is longer than 8 bytes. This indicates that truncation has occurred. The SQLWARN0 field in the SQLCA will be set to 'W' to indicate this warning.
- Unsuccessful Connection
- If the CONNECT statement is unsuccessful:
- The SQLERRP field of the SQLCA is set to the name of the module at the application requester that detected the error. The first three characters of the module name identify the product.
- If the CONNECT statement is unsuccessful because the application process is not in the connectable state, the connection state of the application process is unchanged.
- If the CONNECT statement is unsuccessful because the server-name is
not listed in the local directory, an error message (SQLSTATE 08001) is issued and the connection
state of the application process remains unchanged:
- If the application requester was not connected to an application server then the application process remains unconnected.
- If the application requester was already connected to an application server, the application process remains connected to that application server. Any further statements are executed at that application server.
- If the CONNECT statement is unsuccessful for any other reason, the application process is placed into the unconnected state.
- IN SHARE MODE
- Allows other concurrent connections to the database and prevents other users from connecting to the database in exclusive mode.
- IN EXCLUSIVE MODE
- Prevents concurrent application
processes from executing any operations at the application server, unless they have the same
authorization ID as the user holding the exclusive lock.
- ON SINGLE MEMBER
- Specifies that the coordinator database member is connected in exclusive mode and all other members are connected in share mode.
- Disconnects the application process from the current server. A commit operation is performed. If implicit connect is available, the application process remains unconnected until an SQL statement is issued.
- USER authorization-name/host-variable
- Identifies the user ID trying to connect to the application server. If a host-variable is specified, it must be a character string variable that does not include an indicator variable. The user ID that is contained within the host-variable must be left-aligned and must not be delimited by quotation marks.
- USING password/host-variable
- Identifies the password of the user ID trying to connect to the application server. The maximum length of the password is determined by the data server you are connecting to. If a host variable is specified, it must be a character string variable and it must not include an indicator variable.
- NEW password/host-variable CONFIRM password
- Identifies the new password that should be assigned to the user ID identified by the USER option. The maximum length of the password is determined by the data server you are connecting to. If a host variable is specified, it must be a character string variable and it must not include an indicator variable. The system on which the password will be changed depends on how the user authentication has been set up. To support the changing passwords on Linux®, the database instance must be configured to use the security plug-ins IBMOSchgpwdclient and IBMOSchgpwdserver.
- It is good practice for the first SQL statement executed by an application process to be the CONNECT statement.
- If a CONNECT statement is issued to the current application server with a different user ID and password then the conversation is deallocated and reallocated. All cursors are closed by the database manager (with the loss of the cursor position if the WITH HOLD option was used).
- If a CONNECT statement is issued to the current application server with the same user ID and password then the conversation is not deallocated and reallocated. Cursors, in this case, are not closed.
- To use a multiple-partition partitioned database environment, the user or application must connect to one of the database partitions listed in the db2nodes.cfg file. You should try to ensure that not all users use the same database partition as the coordinator partition.
- The authorization-name SYSTEM cannot be explicitly specified in the CONNECT statement.
- The database can be inaccessible if the database was not explicitly activated, a client application performs frequent reconnections, or the time interval between issuing the DEACTIVATE DATABASE and ACTIVATE DATABASE commands is very short. Activate the database by issuing the ACTIVATE DATABASE command and then attempt to connect to the database.
- Example 1: In a C program, connect to the application server TOROLAB, using database
alias TOROLAB, user ID FERMAT, and password THEOREM.
EXEC SQL CONNECT TO TOROLAB USER FERMAT USING THEOREM;
- Example 2: In a C program, connect to an application server whose database alias is
stored in the host variable APP_SERVER (varchar(8)). Following a successful connection, copy the
3-character product identifier of the application server to the variable PRODUCT (char(3)).
EXEC SQL CONNECT TO :APP_SERVER; if (strncmp(SQLSTATE,'00000',5)) strncpy(PRODUCT,sqlca.sqlerrp,3);
- Example 3: Connect to the SAMPLE database using a JWT access
connect to sample accesstoken <access_token> accesstokentype jwt Database Connection Information Database server = DB2/LINUXX8664 18.104.22.168 SQL authorization ID = NEWTON Local database alias = SAMPLE