CONNECT (Type 2)

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.

Invocation

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 Java™ or REXX.

CONNECT is not allowed in a trigger, a function, or a procedure if the procedure is called on a remote application server.

Authorization

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 topic collection.)

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

Start of change 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:End of change

Start of changeEnd of change

Syntax

Read syntax diagramSkip visual syntax diagram
>>-CONNECT--+----------------------------------------+---------><
            +-TO--+-server-name-+--+---------------+-+   
            |     '-variable----'  '-authorization-' |   
            '-RESET----------------------------------'   

authorization

|--USER--+-authorization-name-+--USING--+-password-+------------|
         '-variable-----------'         '-variable-'   

Description

TO server-name or variable
Identifies the application server by the specified server name or the server name contained in the variable. Start of changeIt can be a global variable if it is qualified with schema name. End of changeIf 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.
  • The value of the server name must not contain lowercase characters.

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.

Let S denote the specified server name or the server name contained in the variable. S must not identify an existing connection of the application process.

USER authorization-name or variable
Identifies the authorization name that will be used to connect to the application server. Start of changeIt can be a global variable if it is qualified with schema name.End of change

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.
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:

  • Start of changeIt cannot be a global variable.End of change
  • 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).

In addition, the DB2_CONNECTION_STATUS connection information item in the SQL Diagnostics Area (or 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:

  • 1 - Commitable updates can be performed on the connection for this unit of work.
  • 2 - No commitable updates can be performed on the connection for this unit of work.

Notes

Successful connection: If the CONNECT statement is successful:

Unsuccessful connection: If the CONNECT statement is unsuccessful, the connection state of the activation group and the states of its connections are unchanged.

Implicit connect: 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 about implicit connect, refer to SQL Programming. 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 Distributed Database Programming for more details.

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).

Examples

Example 1: Execute SQL statements at TOROLAB and SVLLAB. The first CONNECT statement creates the TOROLAB connection and the second CONNECT statement places it in the dormant state.

  EXEC SQL  CONNECT TO TOROLAB;

    (execute statements referencing objects at TOROLAB)

  EXEC SQL  CONNECT TO SVLLAB;

    (execute statements referencing objects at SVLLAB)

Example 2: Connect to a remote server specifying a userid and password, perform work for the user and then connect as another user to perform further work.

  EXEC SQL  CONNECT TO SVLLAB USER :AUTHID USING :PASSWORD;

    (execute SQL statements accessing data on the server)

  EXEC SQL COMMIT;

    (set AUTHID and PASSWORD to new values)

  EXEC SQL  CONNECT TO SVLLAB USER :AUTHID USING :PASSWORD;

    (execute SQL statements accessing data on the server)

Example 3: 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 the IBM i operating system, 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 wanted connection:

   EXEC SQL CONNECT TO TOROLAB3;
   (execute statements referencing objects at TOROLAB3)