The CONNECT (TYPE 1) statement connects an activation group within an application process to the identified application server using the rules for remote unit of work. This server is then the current server for the activation group. This type of CONNECT statement is used if RDBCNNMTH(*RUW) was specified on the CRTSQLxxx command. 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.
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 REXX.
CONNECT is not allowed in a trigger program. CONNECT is not allowed in an external procedure if the external procedure is called on a remote application server.
The privileges held by the authorization ID of the statement must include communications-level security. (See the section about security in the Distributed Database Programming book.)
If the application server is DB2 UDB for AS/400, the user profile of the person issuing the statement must also be a valid user profile on the application server system, UNLESS:
>>-CONNECT----+---------------+--------------------------------><
+-RESET---------+
'-| to-clause |-'
to-clause
|---TO----+-server-name---+------------------------------------->
'-host-variable-'
>-----+-----------------------------------------------------------+>
'-USER--+-authorization-name-+--USING----+-password------+--'
'-host-variable------' '-host variable-'
>---------------------------------------------------------------|
If the server-name is the local relational database and an authorization-name is specified, it must be the authorization-name of the job. If the specified authorization-name is different than the authorization-name of the job, an error occurs and the application is left in the unconnected state.
If a host-variable is specified,
If a host-variable is specified,
When the CONNECT statement is executed, the specified server name or the server name contained in the host variable must identify an application server described in the local directory and the activation group must be in the connectable state.
If the CONNECT statement is successful:
For example, if the application server is Version 4 of DB2 UDB for OS/390, the value of SQLERRP is 'DSN04010'.
The SQLERRD(4) field of the SQLCA contains values indicating whether the application server allows commitable updates to be performed. For a CONNECT (Type 1) statement SQLERRD(4) will always contain the value 1. The value 1 indicates that commitable updates can be performed, and the 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. Note that the first three characters of the module name identify the product. For example, if the application requester is DB2 UDB for NT 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:
An application in a connectable but unconnected state can only execute the CONNECT or SET CONNECTION statements.
For a description of connection states, see "Remote Unit of Work Connection Management".
| Note: | It is a good practice for the first SQL statement executed by an activation group to be the CONNECT statement. |
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 on implicit connect, refer to the SQL Programming Concepts book. 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 book for more details.
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:
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 the SQL Programming Concepts book and the Distributed Database Programming book.
For an example of the additional flexibility available in doing connects using the TCP/IP protocol with DRDA, see Example 2 under the discussion of CONNECT Type 2. This example applies also to CONNECT Type 1.
In a PL/I program, user JOE will connect to the application server TOROLAB3. JOE's password is XYZ1.
USERNAME='JOE';
USERPASS='XYZ1';
EXEC SQL CONNECT TO TOROLAB3
USER :USERNAME USING :USERPASS;
In a PL/I program, connect to an application server whose name is stored in the host variable APP_SERVER (varchar(18)). Following a successful connection, copy the 3 character product identifier of the application server to the host variable PRODUCT (char(3)).
EXEC SQL CONNECT TO :APP_SERVER;
IF SQLSTATE = '00000' THEN
PRODUCT = SUBSTR(SQLERRP,1,3);