CONNECT (type 2) 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

The selection between Type 1 and Type 2 is determined by precompiler options. For an overview of these options, see Connecting to distributed relational databases.

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

TO server-name/host-variable

The rules for coding the name of the server are the same as for Type 1.

If the SQLRULES(STD) option is in effect, the server-name must not identify an existing connection of the application process, otherwise an error (SQLSTATE 08002) is raised.

If the SQLRULES(DB2) option is in effect and the server-name identifies an existing connection of the application process, that connection is made current and the old connection is placed into the dormant state. That is, the effect of the CONNECT statement in this situation is the same as that of a SET CONNECTION statement.

For information about the specification of SQLRULES, see Options that Govern Distributed Unit of Work Semantics.

Successful Connection

If the CONNECT statement is successful:

• A connection to the application server is either created (or made non-dormant) and placed into the current and held states.
• If the CONNECT TO is directed to a different server than the current server, then the current connection is placed into the dormant state.
• The CURRENT SERVER special register and the SQLCA are updated in the same way as for CONNECT (Type 1).

Unsuccessful Connection

If the CONNECT statement is unsuccessful:

• No matter what the reason for failure, the connection state of the application process and the states of its connections are unchanged.
• As with an unsuccessful Type 1 CONNECT, the SQLERRP field of the SQLCA is set to the name of the module at the application requester or server that detected the error.

CONNECT (with no operand), IN SHARE/EXCLUSIVE MODE, USER, and USING

If a connection exists, Type 2 behaves like a Type 1. The authorization ID and database alias are placed in the SQLERRMC field of the SQLCA. If a connection does not exist, no attempt to make an implicit connection is made and the SQLERRP and SQLERRMC fields return a blank. (Applications can check if a current connection exists by checking these fields.)

A CONNECT with no operand that includes USER and USING can still connect an application process to a database using the DB2DBDFT environment variable. This method is equivalent to a Type 2 CONNECT RESET, but permits the use of a user ID and password.

RESET

Equivalent to an explicit connect to the default database if it is available. If a default database is not available, the connection state of the application process and the states of its connections are unchanged.

Availability of a default database is determined by installation options, environment variables, and authentication settings.

Rules

• As outlined in Options that Govern Distributed Unit of Work Semantics, a set of connection options governs the semantics of connection management. Default values are assigned to every preprocessed source file. An application can consist of multiple source files precompiled with different connection options.

  Unless a SET CLIENT command or API has been executed first, the connection options used when preprocessing the source file containing the first SQL statement executed at run time become the effective connection options.

  If a CONNECT statement from a source file preprocessed with different connection options is subsequently executed without the execution of any intervening SET CLIENT command or API, an error (SQLSTATE 08001) is returned. Note that once a SET CLIENT command or API has been executed, the connection options used when preprocessing all source files in the application are ignored.

  Example 1 in the Examples section of this statement illustrates these rules.

• Although the CONNECT statement can be used to establish or switch connections, CONNECT with the USER/USING clause will only be accepted when there is no current or dormant connection to the named server. The connection must be released before issuing a connection to the same server with the USER/USING clause, otherwise it will be rejected (SQLSTATE 51022). Release the connection by issuing a DISCONNECT statement or a RELEASE statement followed by a COMMIT statement.

Comparing Type 1 and Type 2 CONNECT Statements

The semantics of the CONNECT statement are determined by the CONNECT precompiler option or the SET CLIENT API (see Options that Govern Distributed Unit of Work Semantics). CONNECT Type 1 or CONNECT Type 2 can be specified and the CONNECT statements in those programs are known as Type 1 and Type 2 CONNECT statements, respectively. Their semantics are described in the following tables:

Use of CONNECT:

Use of CONNECT...USER...USING:

Use of Implicit CONNECT, CONNECT RESET, and Disconnecting:

CONNECT Failures:

Notes

• Implicit connect is supported for the first SQL statement in an application with Type 2 connections. In order to execute SQL statements on the default database, first the CONNECT RESET or the CONNECT USER/USING statement must be used to establish the connection. The CONNECT statement with no operands will display information about the current connection if there is one, but will not connect to the default database if there is no current connection.
• 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.
• Termination of a connection: When a connection is terminated and a transaction has not yet been committed or rolled back, see "Use of Implicit CONNECT, CONNECT RESET, and Disconnecting" section for details on what happens to such transactions. To ensure consistent behavior, code an explicit COMMIT statement or ROLLBACK statement instead of depending on the behavior of the CONNECT statement.
• 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: This example illustrates the use of multiple source programs (shown in the boxes), some preprocessed with different connection options (shown in the statement preceding the code), and one of which contains a SET CLIENT API call.
  PGM1: CONNECT(2) SQLRULES(DB2) DISCONNECT(CONDITIONAL)

     ...
     exec sql CONNECT TO OTTAWA;
     exec sql SELECT col1 INTO :hv1
     FROM tbl1;
     ...

  PGM2: CONNECT(2) SQLRULES(STD) DISCONNECT(AUTOMATIC)

     ...
     exec sql CONNECT TO QUEBEC;
     exec sql SELECT col1 INTO :hv1
     FROM tbl2;
     ...

  PGM3: CONNECT(2) SQLRULES(STD) DISCONNECT(EXPLICIT)

     ...
     SET CLIENT CONNECT 2  SQLRULES DB2  DISCONNECT EXPLICIT 1
     exec sql CONNECT TO LONDON;
     exec sql SELECT col1 INTO :hv1 
     FROM tbl3;
     ...

  Note:

  1. Not the actual syntax of the SET CLIENT API
  PGM4: CONNECT(2) SQLRULES(DB2) DISCONNECT(CONDITIONAL)

     ...
     exec sql CONNECT TO REGINA;
     exec sql SELECT col1 INTO :hv1 
     FROM tbl4;
     ...

  If the application executes PGM1 then PGM2:

  • connect to OTTAWA runs: connect=2, sqlrules=DB2, disconnect=CONDITIONAL
  • connect to QUEBEC fails with SQLSTATE 08001 because both SQLRULES and DISCONNECT are different.
  If the application executes PGM1 then PGM3:

  • connect to OTTAWA runs: connect=2, sqlrules=DB2, disconnect=CONDITIONAL
  • connect to LONDON runs: connect=2, sqlrules=DB2, disconnect=EXPLICIT

  This is OK because the SET CLIENT API is run before the second CONNECT statement.
  If the application executes PGM1 then PGM4:

  • connect to OTTAWA runs: connect=2, sqlrules=DB2, disconnect=CONDITIONAL
  • connect to REGINA runs: connect=2, sqlrules=DB2, disconnect=CONDITIONAL

  This is OK because the preprocessor options for PGM1 are the same as those for PGM4.
• Example 2: This example shows the interrelationships of the CONNECT (Type 2), SET CONNECTION, RELEASE, and DISCONNECT statements. S0, S1, S2, and S3 represent four servers.

  Sequence	Statement	Current Server	Dormant Connections	Release Pending
  0	No statement	None	None	None
  1	SELECT * FROM TBLA	S0 (default)	None	None
  2	CONNECT TO S1 SELECT * FROM TBLB	S1 S1	S0 S0	None None
  3	CONNECT TO S2 UPDATE TBLC SET ...	S2 S2	S0, S1 S0, S1	None None
  4	CONNECT TO S3 SELECT * FROM TBLD	S3 S3	S0, S1, S2 S0, S1, S2	None None
  5	SET CONNECTION S2	S2	S0, S1, S3	None
  6	RELEASE S3	S2	S0, S1	S3
  7	COMMIT	S2	S0, S1	None
  8	SELECT * FROM TBLE	S2	S0, S1	None
  9	DISCONNECT S1 SELECT * FROM TBLF	S2 S2	S0 S0	None None

• 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