CONNECT

The CONNECT statement connects an application process to a database server. This server becomes the current server for the process. The CONNECT statement of DB2® for z/OS® is equivalent to CONNECT (Type 2) in IBM DB2 SQL Reference for Cross-Platform Development.

Refer to Distributed data for complete information about connections, the current server, commit processing, and distributed and remote units of work.

Invocation

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

Authorization

The primary authorization ID of the process or the authorization ID that is specified in this statement must be authorized to connect to the specified server. The server performs the authorization check when the statement is executed, and determines the specific authorization that is required. See DB2 Administration Guide for further information.

Syntax


>>-CONNECT--+------------------------------------------+-------><
            +-TO--+-location-name-+--+---------------+-+   
            |     '-host-variable-'  '-authorization-' |   
            +-RESET------------------------------------+   
            '-authorization----------------------------'

authorization:


>>-USER--host-variable--USING--host-variable-------------------><

Description

TO location-name or host-variable

Identifies the server by the specified location name or by the location name that is contained in the host variable. If a host variable is specified:

It must be a CHAR or VARCHAR variable with a length attribute that is not greater than 16. (A C NUL-terminated character string can be up to 17 bytes long.)
It must not be followed by an indicator variable.
The location name must be left-justified within the host variable and must conform to the rules for forming an ordinary identifier.
If the length of the location name is less than the length of the host variable, it must be padded on the right with blanks.
It must not contain lowercase characters.
If used with an SQL procedure language application, host variable must be a qualified SQL-variable name or a qualified SQL-parameter name.

When the CONNECT statement is executed:

The location name must identify a server known to the local DB2 subsystem. Hence, the location name must be the location name of the local DB2 subsystem or it must appear in the LOCATION column of the SYSIBM.LOCATIONS table.
The application process must not have an existing connection to the specified server, if the SQLRULES(STD) bind option is in effect.
The application process must be in a connectable state, if the transaction is participating in a remote unit of work.

RESET

CONNECT RESET is equivalent to CONNECT TO x where x is the location name of the local DB2 subsystem.

If the SQLRULES(DB2) bind option is in effect, CONNECT RESET establishes the local DB2 subsystem as the current SQL connection.
If the SQLRULES(STD) bind option is in effect, CONNECT RESET establishes the local DB2 subsystem as the current SQL connection only if the connection does not exist.

authorization

Specifies an authorization ID and a password that is used to verify that the authorization ID is authorized to connect to the server. Authorization cannot be specified when the connection type is IMS™ or CICS® for a connection to the local DB2 subsystem. An attempt to do so causes an SQL error.

USER host-variable

Identifies the authorization name to use when connecting to the server. The value of host-variable must satisfy the following rules:

The value must be a CHAR or VARCHAR variable with a length attribute that is not greater than 128.
The value must be left-justified within the host variable and must conform to the rules for forming an authorization name.
The value must not be followed by an indicator variable.
The value must be padded on the right with blanks if the length of the authorization name is less than the length of the host variable.

For a connection to the local DB2 subsystem, a user ID that is longer than 8 characters causes an SQL error.

USING host-variable

Identifies the password of the authorization name to use when connecting to the server. The value of host-variable must satisfy the following rules:

The value must be a CHAR or VARCHAR variable with a length attribute that is not greater than 128.
The value must be left-justified.
The value must not include an indicator variable.
The value must be padded on the right with blanks if the length of the password is less than the length of the host variable.
The value must not contain lowercase characters.

For a connection to a DB2 subsystem, a password that is longer than 8 characters causes an SQL error.

CONNECT USER/USING is equivalent to CONNECT TO x USER/USING where x is the location name of the local DB2 subsystem (which has the semantic of CONNECT RESET).

CONNECT with no operand

This form of the CONNECT statement returns information about the current server in the SQLERRP field of the SQLCA. SQLERRP returns blanks if the application process is in the unconnected state.

Executing a CONNECT with no operand has no effect on connection states.

In a remote unit of work, this form of CONNECT does not require the application process to be in a connectable state.

Notes

Successful connection: With the exception of a CONNECT with no operand statement, if execution of the CONNECT statement is successful:

One of the following scenarios takes place in a distributed unit of work:
- If the location name does not identify a server to which the application process is already connected, an SQL connection to the server is created and placed in the current and held state. The previously current SQL connection, if any, is placed in the dormant state.
- If the location name identifies a server to which the application process is already connected, the associated SQL connection is dormant, and the SQLRULES(DB2) option is in effect, the SQL connection is placed in the current state. The previously current SQL connection, if any, is placed in the dormant state.
- If the location name identifies a server to which the application process is already connected, the associated SQL connection is current, and the SQLRULES(DB2) option is in effect, the states of all SQL connections of the application process are unchanged.
The following actions occur in a remote unit of work:
- The application process is connected to the specified server.
- An existing SQL connection of the application process is ended. As a result, all cursors of that SQL connection are closed, all prepared statements of that connection are destroyed, and so on.
The location name is placed in the CURRENT SERVER special register.
When CONNECT is used to connect back to the local DB2 subsystem, the CURRENT SQLID special register is reinitialized if the USER/USING clause is specified.
Information about the server is placed in the SQLERRP field of the SQLCA. If the server is a DB2 product, the information has the form pppvvrrm.
The format of product identifier values is pppvvrrm, where ppp is a 3-letter product code (such as DSN for DB2), vv is the version, rr is the release, and m is the modification level. For example, DSN10015 identifies DB2 10 in new-function mode, the value is ‘DSN10015'. The product code (ppp) is one of the following values:
- AQT for IBM® DB2 Analytics Accelerator for z/OS
- ARI for DB2 Server for VSE & VM
- DSN for DB2 for z/OS
- JCC for IBM Data Server Driver for JDBC and SQLJ
- QSQ for DB2 for i
- SQL for DB2 for Linux, UNIX, and Windows
Modification (m) values have the following meanings:
0 - 1

Modification levels in conversion and enabling-new-function mode from DB2 Version 8 (CM8, CM8*, ENFM8, and ENFM8*)

2 - 3

Modification levels in conversion and enabling-new-function mode from DB2 9 (CM9, CM9*, ENFM9, and ENFM9*)

4

Not used.

5 - 9

Modification levels in new-function mode.
Additional information about the connection is placed in the SQLERRMC field of the SQLCA. The contents are product-specific.
Tip: Use the GET DIAGNOSTICS statement to get detailed diagnostic information about the last SQL statement that was executed.

Unsuccessful connection: With the exception of a CONNECT with no operand statement, if execution of the CONNECT statement is unsuccessful:

In a distributed unit of work, the connection state of the application process and the states of its SQL connections are unchanged unless the failure was because an authorization check failed. If this is the case, the connection is placed in the connectable and unconnected state.
In a remote unit of work, the SQLERRP field of the SQLCA is set to the name of the DB2 requester module that detected the error.
If execution of the CONNECT statement is unsuccessful because the application process is not in the connectable state, the connection state of the application process is unchanged. If execution of the CONNECT statement is unsuccessful for any other reason, CURRENT SERVER is set to blanks and the application process is placed in the connectable and unconnected state.

Authorization: If the server is a DB2 subsystem, a user is authenticated in the following way:

DB2 invokes RACF® via the RACROUTE macro with REQUEST=VERIFY to verify the password.
If the password is verified, DB2 then invokes RACF again via the RACROUTE macro with REQUEST=AUTH, to check whether the authorization ID is allowed to use DB2 resources defined to RACF.
DB2 then invokes the connection exit routine if one has been defined.
The connection then has a primary authorization ID, possibly one or more secondary IDs, and an SQL ID.

If the server is a remote DB2 subsystem, the requester generates authentication tokens and sends them to the remote site in the following way:

The SECURITY_OUT column in SYSIBM.LUNAMES for SNA or the SECURITY_OUT column in SYSIBM.IPNAMES for TCP/IP must have one of the following values:
- 'A' (already verified)
- 'D' (userid and security-sensitive data encryption; TCP/IP only)
- 'E' (userid, password, and security-sensitive data encryption; TCP/IP only)
- 'P' (password)
When the value is 'A', the user ID and password specified on the CONNECT is still sent.
When the value is 'D', 'E', 'or 'P', the requester encrypts the user ID and password specified on the CONNECT for TCP/IP. However, if the Integrated Cryptographic Service Facility (ICSF) is not configured at the requester or if the server does not support encryption, one of the following actions occurs:
- If the value of SECURITY_OUT in SYSIBM.IPNAMES is 'D' or 'E', SQLCODE -904 is returned if ICSF is not configured at the requester, and SQLCODE -30082 is returned if the server does not support encryption.
- If the value of SECURITY_OUT in SYSIBM.IPNAMES is 'P', the requester does not encrypt the user ID and password and flows the tokens in clear text.
For SNA, the ENCRYPTPSWDS column in SYSIBM.SYSLUNAMES must be not contain 'Y'.
The authorization ID and password are verified at the server.
In all cases, outbound translation—as specified in SYSIBM.USERNAMES—is not done.

Distributed unit of work: In general, the following are true:

A CONNECT statement with the TO clause and the USER/USING clause can be executed only if no current or dormant connection to the named server exists. However, if the named server is the local DB2 subsystem and the CONNECT statement is the first SQL statement that is executed after the DB2 thread is created, the CONNECT statement executes successfully.
A CONNECT statement without the TO clause but with the USER/USING clause can be executed only if no current or dormant connection to the local DB2 subsystem exists. However, if the CONNECT statement is the first SQL statement that is executed after the DB2 thread is created, the CONNECT statement executes successfully.

Remote unit of work: If the authorization check fails, the connection is placed in the connectable and unconnected state.

Precompiler options: Regardless of whether a program is precompiled with the CONNECT(1) or CONNECT(2) option, DB2 for z/OS negotiates with the remote server during the connection process to determine how to perform commits. If the remote server does not support the two-phase commit protocol, DB2 downgrades to perform one-phase commits.

Programs containing CONNECT statements that are precompiled with different CONNECT precompiler options cannot execute as part of the same application process. An error occurs when an attempt is made to execute the invalid CONNECT statement.

Host variables: If a CONNECT statement contains host variables, the contents of the host variables are assumed to be in the encoding scheme that was specified in the ENCODING parameter when the package or plan that contains the statement was bound.

Error processing: A CONNECT statement can return and indicate a successful execution even when no physical connection yet exists. DB2 delays the physical connection process, when possible, to economize on the number of messages it sends to a server. Therefore, errors in CONNECT statement processing can be reported following the next executable SQL statement, not immediately following the CONNECT statement.

Examples

Example 1: Connect an application to a DBMS. The location name is in the character-string variable LOCNAME, the authorization identifier is in the character-string variable AUTHID, and the password is in the character-string variable PASSWORD.

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

Example 2: Obtain information about the current server.

  EXEC SQL CONNECT;

Example 3: Execute SQL statements in a distributed unit of work. The first CONNECT statement creates a connection to the EASTDB server. The second CONNECT statement creates a connection to the WESTDB server, and places the SQL connection to EASTDB in the dormant state.

  EXEC SQL CONNECT TO EASTDB;
    -- execute statements referencing objects at EASTDB
  EXEC SQL CONNECT TO WESTDB;
    -- execute statements referencing objects at WESTDB

Example 4: Connect the application to a DBMS whose location identifier is in the character-string variable LOC using the authorization identifier in the character-string variable AUTHID and the password in the character-string variable PASSWORD. Perform work for the user, and then release the connection and connect again using a different user ID and password.

  EXEC SQL CONNECT TO :LOC USER :AUTHID USING :PASSWORD;
    -- execute SQL statements accessing data on the server
  RELEASE :LOC;    
  EXEC SQL COMMIT;
    -- set AUTHID and PASSWORD to new values
  EXEC SQL CONNECT TO :LOC USER :AUTHID USING :PASSWORD;
    -- execute SQL statements accessing data on the server

Example 5: Change servers in a remote unit of work. Assume that the application connected to a remote DB2 server, opened a cursor, and fetched rows from the cursor's result table. Subsequently, to connect to the local DB2 subsystem, the application executes the following statements:

  EXEC SQL COMMIT WORK;
  EXEC SQL CONNECT RESET;

Start of change The COMMIT is required because opening the cursor caused the application to enter the unconnectable and connected state. The unconnectable state means that the connection has started a transaction and cannot connect to another server until the transaction completes. Issuing a COMMIT statement ends the transaction. Issuing a CONNECT statement with the RESET option reconnects the application to the local server and returns the application to the unconnectable and connected state. End of change

If the cursor was declared with the WITH HOLD clause and was not closed with a CLOSE statement, it would still be open even after execution of the COMMIT statement. However, it would be closed with the execution of the CONNECT statement.