The CONNECT (Type 2) statement connects an activation group within an application process to the identified application server using the rules for application directed distributed unit of work. This server is then the current server for the activation group. This type of CONNECT statement is used if RDBCNNMTH(*DUW) 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 in 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 profile ID 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 a host-variable is specified,
If password is specified as a literal, it must be a character string. The maximum length is 10 characters. It must be left justified.
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.
Let S denote the specified server name or the server name contained in the host variable. S must not identify an existing connection of the application process.
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 application server S allows commitable updates to be performed. Following is a list of values and their meanings for the SQLERRD(4) field of the SQLCA on the CONNECT:
If the CONNECT statement is unsuccessful, the connection state of the activation group and the states of its connections are unchanged.
In addition, the SQLERRD(3) field of the SQLCA will indicate the status of connection for this unit of work. It will have one of the following values:
Implicit connect will always send the authorization-name of the application requester job and will 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.
Execute SQL statements at TOROLAB1 and TOROLAB2. The first CONNECT statement creates the TOROLAB1 connection and the second CONNECT statement places it in the dormant state.
EXEC SQL CONNECT TO TOROLAB1; (execute statements referencing objects at TOROLAB1) EXEC SQL CONNECT TO TOROLAB2; (execute statements referencing objects at TOROLAB2)
User JOE wants to connect to TOROLAB3 and execute SQL statements under the user ID ANONYMOUS which has a password of SHIBBOLETH. The RDB directory entry for TOROLAB3 specifies *IP for the connection type.
Before running the application, some setup must be done.
This command will be required to allow server security information to be retained in OS/400, if it has not been previously run:
CHGSYSVAL SYSVAL(QRETSVRSEC) VALUE('1')
This command adds the required server authorization entry:
ADDSVRAUTE USRPRF(JOE) SERVER(TOROLAB3) USRID(ANONYMOUS) +
PASSWORD(SHIBBOLETH)
This statement, run under JOE's user profile, will now make the desired connection:
EXEC SQL CONNECT TO TOROLAB3; (execute statements referencing objects at TOROLAB3)