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

accesstoken

lock-block

Notes:

• ¹ This form is only valid if implicit connect is enabled.
• ² This feature is available starting from Db2 Version 11.5 Mod Pack 4.
• ³ This feature is available starting from Db2 Version 11.5 Mod Pack 4.
• ⁴ This feature is available starting from Db2 Version 11.5 Mod Pack 4.

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, where:
  • ppp represents one of the following product identifiers:
    • DSN for Db2® for z/OS®
    • ARI for Db2 Server for VSE & VM
    • QSQ for Db2 for IBM i
    • SQL for Db2
  • vv is a two-digit version identifier, such as '08'
  • rr is a two-digit release identifier, such as '01'
  • m is a one-character modification level identifier, such as '0'.

  For example, Version 9.5 of Db2 is identified as 'SQL09050'.

• 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 (or blanks if using Db2 Connect),
  2. The code page of the application server (or CCSID if using Db2 Connect),
  3. The authorization ID (up to first 8 bytes only),
  4. The database alias,
  5. The platform type of the application server. Currently identified values are:

     Token

     Server

     QAS

     Db2 for IBM i

     QDB2

     Db2 for z/OS

     QDB2/6000

     Db2 Database for AIX®

     QDB2/LINUX

     Db2 Database for Linux®

     QDB2/NT

     Db2 Database for Windows

     QSQLDS/VM

     Db2 Server for VM

     QSQLDS/VSE

     Db2 Server for VSE

  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 the server instance operates in a Db2 pureScale® environment, as indicated by SQLWARN0 and SQLWARN4 being set to 'W' and 'S' respectively, this value represents the member number. 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 non-partitioned environment and outside of a Db2 pureScale environment, this value is not applicable and is always 0.
  9. The code page of the application client.
  10. If this value is zero, the server instance operates in a non-partitioned environment and outside of a Db2 pureScale environment. Otherwise, this non-zero value represents the number of members in a Db2 pureScale instance, if SQLWARN0 and SQLWARN4 are set to 'W' and 'S' respectively. 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.

  2

  Authenticated using Db2 Connect.

  4

  Authenticated on the server with encryption.

  5

  Authenticated using Db2 Connect 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. This option is not supported by Db2 Connect.

ON SINGLE MEMBER

Specifies that the coordinator database member is connected in exclusive mode and all other members are connected in share mode.

If the database is neither in a partitioned environment nor a Db2 pureScale environment, this option can be specified, but it has no effect.

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.

Quoting Passwords

A-Z, a-z (ALPHABETIC CHARACTERS)
# $ @ (DIAMOND "LETTERS")
0-9 (NUMERIC CHARACTERS)
_ (UNDERSCORE CHARACTER)
. (PERIOD CHARACTER)
/ (FORWARD SLASH CHARACTER)
\ (BACKWARD SLASH CHARACTER)
: (COLON CHARACTER)
& * % - ~ + ; ? < = > [ ] ` | ^ { }

db2 => CONNECT TO sample USER admin USING '!xyz'

db2 connect to sample USER admin USING "'!xyz'"

db2 "connect to sample USER admin USING '!xyz'"

Alternatively, use the password prompt feature by executing a CONNECT statement without providing a password. In the password prompt, you do not need to add quotation marks to passwords containing special characters:

db2 => CONNECT TO sample USER admin

db2 CONNECT TO sample USER admin

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. However, on Windows operating systems, local applications running under the Local System Account can implicitly connect to the database, such that the user ID is SYSTEM.
• When connecting to Windows Server explicitly, the authorization-name or user host-variable can be specified using the Microsoft Windows Security Account Manager (SAM)-compatible name.
• 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.
• Syntax alternatives: The following are supported for compatibility with previous versions of Db2 and with other database products. These alternatives are non-standard and should not be used.
  • DBPARTITIONNUM or NODE can be specified in place of MEMBER, except when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.

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