SQL Reference

CONNECT (Type 1)

The CONNECT (Type 1) statement connects an application process to the identified application server according to the rules for remote unit of work.

An application process can only be connected to one application server at a time. This is called the current server. A default application server may be established when the application requester is initialized. If implicit connect is available and an application process is started, it is implicitly connected to the default application server. The application process can explicitly connect to a different application server by issuing a CONNECT TO statement. A connection lasts until a CONNECT RESET statement or a DISCONNECT statement is issued or until another CONNECT TO statement changes the application server.

See Remote Unit of Work Connection Management for concepts and additional details on connection states. See Options that Govern Distributed Unit of Work Semantics for the precompiler options that determine the framework for CONNECT behavior.

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.

Authorization

The authorization ID of the statement must be authorized to connect to the identified application server. Depending on the authentication setting for the database, the authorization check may be performed by either the client or the server. For a partitioned database, the user and group definitions must be identical across partitions or nodes. Refer to the AUTHENTICATION database manager configuration parameter in the Administration Guide for information about the authentication setting.

Syntax 

>>-CONNECT------------------------------------------------------>
 
>-----+----------------------------------------------------------------------+>
      +-TO--+-server-name---+---+-----------------+---+--------------------+-+
      |     '-host-variable-'   '-| lock-block |--'   '-| authorization |--' |
      +-RESET----------------------------------------------------------------+
      |                   (1)                                                |
      '-| authorization |----------------------------------------------------'
 
>--------------------------------------------------------------><
 
authorization
 
|---USER----+-authorization-name-+--USING----+-password------+-->
            '-host-variable------'           '-host-variable-'
 
>-----+---------------------------------------------+-----------|
      '-NEW--+-password------+---CONFIRM--password--'
             '-host-variable-'
 
lock-block
 
    .-IN SHARE MODE--------------------------.
|---+----------------------------------------+------------------|
    '-IN EXCLUSIVE MODE--+----------------+--'
                         '-ON SINGLE NODE-'

Notes:

  1. This form is only valid if implicit connect is enabled.

Description

CONNECT  (with no operand) 
Returns information about the current server. The information is returned in the SQLERRP field of the SQLCA as described in "Successful Connection".

If a connection state exists, the authorization ID and database alias are placed in the SQLERRMC field of the SQLCA. If the authorization ID is longer than 8 bytes, it will be truncated to 8 bytes, and the truncation will be flagged in the SQLWARN0 and SQLWARN1 fields of the SQLCA, with 'W' and 'A', respectively. If the database configuration parameter DYN_QUERY_MGMT is enabled, then the SQLWARN0 and SQLWARN7 fields of the SQLCA will be flagged with 'W' and 'E', respectively.

If no connection exists and implicit connect is possible, then an attempt to make an implicit connection is made. If implicit connect is not available, this attempt results in an error (no existing connection). If no connection, then the SQLERRMC field is blank.

The country code and code page of the application server are placed in the SQLERRMC field (as they are with a successful CONNECT TO statement).

This form of CONNECT:

TO server-name  or  host-variable
Identifies the application server by the specified server-name or a host-variable which contains the server-name.

If a host-variable is specified, it must be a character string variable with a length attribute that is not greater than 8, and it must not include an indicator variable. The server-name that is contained within the host-variable must be left-justified and must not be delimited by quotation marks.

Note that the server-name is a database alias identifying the application server. It must be listed in the application requester's local directory.
Note:DB2 for MVS supports a 16 byte location-name and both SQL/DS and DB2/400 support a 18 byte target database name. DB2 Version 7 only supports the use of 8 byte database-alias name on the SQL CONNECT statement. However, the database-alias name can be mapped to an 18 byte database name through the Database Connection Service Directory.

When the CONNECT TO statement is executed, the application process must be in the connectable state (see Remote Unit of Work Connection Management for information about connection states with Type 1 CONNECT).

Successful Connection:

If the CONNECT TO statement is successful:

Unsuccessful Connection:

If the CONNECT TO statement is unsuccessful:

IN SHARE MODE
Allows other concurrent connections to the database and prevents other users from connecting to the database in exclusive mode.

IN EXCLUSIVE MODE 68
Prevents concurrent application processes from executing any operations at the application server, unless they have the same authorization ID as the user holding the exclusive lock.

ON SINGLE NODE
Specifies that the coordinator partition is connected in exclusive mode and all other partitions are connected in share mode. This option is only effective in a partitioned database.

RESET
Disconnects the application process from the current server. A commit operation is performed. If implicit connect is available, the application process remains unconnected until an SQL statement is issued.

USER authorization-name/host-variable
Identifies the userid trying to connect to the application server. If a host-variable is specified, it must be a character string variable with a length attribute that is not greater than 8, and it must not include an indicator variable. The userid that is contained within the host-variable must be left justified and must not be delimited by quotation marks.

USING password/host-variable
Identifies the password of the userid trying to connect to the application server. Password or host-variable may be up to 18 characters. If a host variable is specified, it must be a character string variable with a length attribute not greater than 18 and it must not include an indicator variable.

NEW password/host-variable CONFIRM password
Identifies the new password that should be assigned to the userid identified by the USER option. Password or host-variable may be up to 18 characters. If a host variable is specified, it must be a character string variable with a length attribute not greater than 18 and it must not include an indicator variable. The system on which the password will be changed depends on how user authentication is set up.

Notes

Examples

Example 1:  In a C program, connect to the application server TOROLAB3, where TOROLAB3 is a database alias of the same name, with the userid FERMAT and the password THEOREM. 

  EXEC SQL  CONNECT TO TOROLAB3 USER FERMAT USING THEOREM;

Example 2:  In a C program, connect to an application server whose database alias is stored in the host variable APP_SERVER (varchar(8)). Following a successful connection, copy the 3 character product identifier of the application server to the variable PRODUCT (char(3)). 

  EXEC SQL  CONNECT TO :APP_SERVER;
  if (strncmp(SQLSTATE,'00000',5))
    strncpy(PRODUCT,sqlca.sqlerrp,3);

Footnotes:

66
This release of DB2 Universal Database Version 7 is 'SQL07010'.

67
See the "Character Conversion Expansion Factor" section of the "Programming in Complex Environments" chapter in the Application Development Guide for details.

68
This option is not supported by DDCS.

[ Top of Page | Previous Page | Next Page ]