CONNECT statement

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 SQL Reference for Cross-Platform Development - Version 6.

For more information about connections, the current server, commit processing, and distributed and remote units of work, see Distributed relational databases.

Invocation for CONNECT

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 for CONNECT

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 checks the authorization when the statement is executed, and determines the specific authorization that is required. For more information, see Authorization IDs.

Syntax for CONNECT

authorization:

Description for CONNECT

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-aligned 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 that is 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 for 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-aligned 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 for 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-aligned.
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.

Start of change For a connection to the local Db2 subsystem, a user ID that is longer than 100 characters causes an SQL error. End of change

For more information, see PASSWORD or PHRASE (Specify user password or password phrase).

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 for CONNECT

Successful connections

Except for a CONNECT with no operand statement, if execution of the CONNECT statement is successful the actions depend on the unit of work type:

Distributed units of work

One of the following scenarios takes place in a distributed unit of work:

If the location name does not identify a server that the application process is already connected to, 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 that application process is already connected to, 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 that the application process is already connected to, 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.

Remote units of work

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 product identifier (PRDID) value is an 8-byte character value in pppvvrrm format, where: ppp is a 3-letter product code; vv is the version;rr is the release; and m is the modification level. In Db2 12 for z/OS, the modification level indicates a range of function levels:
- DSN12015 for V12R1M500 or higher.
- DSN12010 for V12R1M100.
For more information, see Product identifier (PRDID) values in Db2 for z/OS.
Additional information about the connection is placed in the SQLERRMC field of the SQLCA. The contents are product-specific.
If the versions of a Db2 for z/OS requester and data server are Db2 12 or later, the SQLERRMC field contains the fully qualified function level of the Db2 for z/OS data server to which the requester is connected, in the format VnnRnMnnn.

Tip: Use the GET DIAGNOSTICS statement to get detailed diagnostic information about the last SQL statement that was executed.

Unsuccessful connections

Except for a CONNECT with no operand statement, if execution of the CONNECT statement is unsuccessful:

Distributed units of work: 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.
Remote units of work: 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 that are defined to RACF.
Db2 then invokes the connection exit routine if one is 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' (user ID and security-sensitive data encryption; TCP/IP only)
- 'E' (user ID, password, and security-sensitive data encryption; TCP/IP only)
- 'P' (password)
When the value is 'A', the user ID and password that is specified on the CONNECT is still sent.
When the value is 'D', 'E', 'or 'P', the requester encrypts the user ID and password that is 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.

Restrictions on array types and array variables

In any SQL statement other than a CALL statement, array types and array variables must not be referenced after a connection at a remote server is established. This restriction includes an SQL statement that executes at a remote server as a result of a three-part name or alias that resolves to an object at a remote server. An exception is that an array element can be the target of a FETCH, SELECT INTO, SET assignment-statement, or VALUES INTO statement in an SQL routine even when the statement is executed at a remote server.

Examples for CONNECT

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. Later, 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.