CONNECT (type 1) statement

Invocation

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.

Authorization

CONNECT processing goes through two levels of access control. Both levels must be satisfied for the connection to be successful.

Syntax

authorization

passwords

lock-block

Notes:

• ¹ This form is only valid if implicit connect is enabled.

Description

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')
  1. The country or region code of the application server.
  2. The code page of the application server,
  3. The authorization ID (up to first 8 bytes only),
  4. The database alias,
  5. The platform type of the application server.
  6. 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.
  7. The agent index. It identifies the index of the agent and is used for service.
  8. 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.
  9. The code page of the application client.
  10. 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:

  0

  N/A (only possible if running from a client which is not at the latest level, is one-phase commit, and is an updater).

  1

  one-phase commit.

  2

  one-phase commit; read-only (only applicable to connections to DRDA1 databases in a TP Monitor environment).

  3

  two-phase commit.

• The SQLERRD(5) field of the SQLCA returns the authentication type for the connection. The value is one of:

  0

  Authenticated on the server.

  1

  Authenticated on the client.

  4

  Authenticated on the server with encryption.

  7

  Authenticated using an external Kerberos security mechanism.

  9

  Authenticated using an external GSS API plug-in security mechanism.

  11

  Authenticated on the server, which accepts encrypted data.

  255

  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.

RESET

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.

Notes

• 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.

Examples

• 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 token.

  connect to sample accesstoken <access_token> accesstokentype jwt
  
     Database Connection Information
  
   Database server        = DB2/LINUXX8664 11.5.4.0
   SQL authorization ID   = NEWTON
   Local database alias   = SAMPLE