CONNECT (Type 1)

Differences between the two types of statements are described in CONNECT (Type 1) and CONNECT (Type 2) differences. Refer to Application-directed distributed unit of work for more information about connection states.

Invocation

This statement can only be embedded within an application program or issued interactively. It is an executable statement that cannot be dynamically prepared. It must not be specified in Java™ or REXX.

CONNECT is not allowed in a trigger or function.

Authorization

The privileges held by the authorization ID of the statement must include communications-level security. (See the section about security in Distributed Database Programming).

If the application server is DB2® for i, the user profile of the person issuing the statement must also be a valid user profile on the application server system, UNLESS:

• User is specified. In this case, the USER clause must specify a valid user profile on the application server system.
• TCP/IP is used with a server authorization entry for the application server. In this case, the server authorization entry must specify a valid user profile on the application server system.

If a global variable is referenced in a statement, the privileges held by the authorization ID of the statement must include at least one of the following:

• For the global variable identified in the statement,
  • The READ privilege on the global variable, and
  • The system authority *EXECUTE on the library containing the global variable
• Administrative authority

Description

TO server-name or variable

Identifies the application server by the specified server name or the server name contained in the variable. It can be a global variable if it is qualified with schema name. If a variable is specified:

• It must be a CHAR or VARCHAR host variable.
• It must not be followed by an indicator variable.
• The server name must be left-justified within the variable and must conform to the rules for forming an ordinary identifier.
• If the length of the server name is less than the length of the variable, it must be padded on the right with blanks.

When the CONNECT statement is executed, the specified server name or the server name contained in the variable must identify an application server described in the local directory and the activation group must be in the connectable state.

If the server-name is a local relational database and an authorization-name is specified, it must be the user of the job. If the specified authorization-name is different than the user of the job, an error occurs and the application is left in the unconnected state.

USER authorization-name or variable

Identifies the authorization name that will be used to connect to the application server. It can be a global variable if it is qualified with schema name.

If a variable is specified,

• It must be a CHAR or VARCHAR host variable.
• It must not be followed by an indicator variable.
• The authorization name must be left-justified within the variable and must conform to the rules of forming an authorization name.
• If the length of the authorization name is less than the length of the variable, it must be padded on the right with blanks.
• The value of the server name must not contain lowercase characters.

USING password or variable

Identifies the password that will be used to connect to the application server.

If password is specified as a literal, it must be a character string. The maximum length is 128 characters. It must be left justified. The literal form of the password is not allowed in static SQL or REXX.

If a variable is specified,

• It cannot be a global variable.
• It must be a CHAR or VARCHAR host variable.
• It must not be followed by an indicator variable.
• The password must be left-justified within the variable.
• If the length of the password is less than that of the variable, it must be padded on the right with blanks.

RESET

CONNECT RESET is equivalent to CONNECT TO x where x is the local server name.

CONNECT with no operand

This form of the CONNECT statement returns information about the current server and has no effect on connection states, open cursors, prepared statements, or locks. The connection information is returned in the connection information items in the SQL Diagnostics Area (or the SQLCA).

Notes

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 current connection.
• The activation group is disconnected from all current and dormant connections, if any, and connected to the identified application server.
• The name of the application server is placed in the CURRENT SERVER special register.
• Information about the application server is placed in the connection-information-items in the SQL Diagnostics Area.
• Information about the application server is also placed in the SQLERRP and SQLERRD(4) fields of the SQLCA. If the application server is an IBM® relational database product, the information in the SQLERRP field has the form pppvvrrm, where:
  • ppp identifies the product as follows:
    • ARI for DB2 for VM and VSE
    • DSN for DB2 for z/OS®
    • QSQ for DB2 for i
    • SQL for all other DB2 products
  • vv is a two-digit version identifier such as '09'
  • rr is a two-digit release identifier such as '01'
  • m is a one-character modification level such as '0'

  For example, if the application server is Version 9 of DB2 for z/OS, the value of SQLERRP is 'DSN09010'.

  The SQLERRD(4) field of the SQLCA contains values indicating whether the application server allows committable updates to be performed. For a CONNECT (Type 1) statement SQLERRD(4) will always contain the value 1. The value 1 indicates that committable updates can be performed, and the connection:

  • Uses an unprotected conversation¹, or
  • Is a connection to an application requester driver program using the *RUW connection method, or
  • Is a local connection using the *RUW connection method.
• Additional information about the connection is placed in the SQLERRMC field of the SQLCA. Refer to SQLCA (SQL communication area)

Unsuccessful connection: If the CONNECT statement is unsuccessful, the DB2_MODULE_DETECTING_ERROR condition information item in the SQL Diagnostics Area (or the SQLERRP field of the SQLCA) is set to the name of the module at the application requester that detected the error. Note that the first three characters of the module name identify the product. For example, if the application requester is DB2 LUW for Windows the first three characters are 'SQL'.

If the CONNECT statement is unsuccessful because the activation group is not in the connectable state, the connection state of the activation group is unchanged.

If the CONNECT statement is unsuccessful for any other reason:

• The activation group remains in a connectable, but unconnected state
• All open cursors are closed, all prepared statements are destroyed, and all locks are released from all current and dormant connections.

An application in a connectable but unconnected state can only execute the CONNECT or SET CONNECTION statements.

Implicit connect:

• When running in the default activation group, the SQL program implicitly connects to a remote relational database when:
  • The activation group is in a connectable state.
  • The first SQL statement in the first SQL program on the program stack is executed.
• When running in a non-default activation group, the SQL program implicitly connects to a remote relational database when the first SQL statement in the first SQL program for that activation group is executed.

When APPC is used for connecting to an RDB, implicit connect always sends the authorization-name of the application requester job and does not send passwords. If the authorization-name of the application server job is different, or if a password must be sent, an explicit connect statement must be used.

When TCP/IP is used for connecting to an RDB, an implicit connect is not bound by the above restrictions. Use of the ADDSVRAUTE and other -SVRAUTE commands allows one to specify, for a given user under which the implicit (or explicit) CONNECT is done, the remote authorization-name and password to be used in connecting to a given RDB.

In order for the password to be stored with the ADDSVRAUTE or CHGSVRAUTE command, the QRETSVRSEC system value must be set to '1' rather than the default of '0'. When using these commands for DRDA connection, it is very important to realize that the value of the RDB name entered into the SERVER parameter must be in UPPER CASE. For more information, see Example 2 under Type 2 CONNECT.

For more information about implicit connect, refer to the SQL Programming topic collection. Once a connection to a relational database for a user profile is established, the password, if specified, may not be validated again on subsequent connections to the same relational database with the same user profile. Revalidation of the password depends on if the conversation is still active. See the Distributed Database Programming topic collection for more details.

Connection states: For a description of connection states, see Remote unit of work connection management. Consecutive CONNECT statements can be executed successfully because CONNECT does not remove the activation group from the connectable state.

A CONNECT to either a current or dormant connection in the application group is executed as follows:

• If the connection identified by the server-name was established using a CONNECT (Type 1) statement, then no action is taken. Cursors are not closed, prepared statements are not destroyed, and locks are not released.
• If the connection identified by the server-name was established using a CONNECT (Type 2) statement, then the CONNECT statement is executed like any other CONNECT statement.

CONNECT cannot execute successfully when it is preceded by any SQL statement other than CONNECT, COMMIT, DISCONNECT, SET CONNECTION, RELEASE, or ROLLBACK. To avoid an error, execute a commit or rollback operation before a CONNECT statement is executed.

If any previous current or dormant connections were established using protected conversations, then the CONNECT (Type 1) statement will fail. Either, a CONNECT (Type 2) statement must be used, or the connections using protected conversations must be ended by releasing the connections and successfully committing.

For more information about connecting to a remote relational database and the local directory, see SQL Programming and the Distributed Database Programming.

SET SESSION AUTHORIZATION: If a SET SESSION AUTHORIZATION statement has been executed in the thread, a CONNECT to the local server will fail unless prior to the connect statement, the SYSTEM_USER value is the same as SESSION_USER.

This incudes an implicit connect due to invoking a program that specifies ACTGRP(*NEW).

Example 2: In a C program, connect to an application server whose name is stored in the variable APP_SERVER (VARCHAR(18)). Following a successful connection, copy the product identifier of the application server to the variable PRODUCT.