SET CLIENT command

Authorization

None

Required connection

None

Command syntax

>>-SET CLIENT--+----------------+------------------------------->
               '-CONNECT--+-1-+-'   
                          '-2-'     

>--+-----------------------------+--+-------------------+------->
   '-DISCONNECT--+-EXPLICIT----+-'  '-SQLRULES--+-DB2-+-'   
                 +-CONDITIONAL-+                '-STD-'     
                 '-AUTOMATIC---'                            

>--+-------------------------+---------------------------------->
   '-SYNCPOINT--+-ONEPHASE-+-'   
                +-TWOPHASE-+     
                '-NONE-----'     

>--+--------------------------------------------+--------------->
   '-CONNECT_MEMBER--+-member-number----------+-'   
                     '-CATALOG_DBPARTITIONNUM-'     

>--+------------------------------+----------------------------><
   '-ATTACH_MEMBER--member-number-'   

Command parameters

CONNECT

1

Specifies that a CONNECT statement is to be processed as a type 1 CONNECT.

2

Specifies that a CONNECT statement is to be processed as a type 2 CONNECT.

DISCONNECT

EXPLICIT

Specifies that only database connections that have been explicitly marked for release by the RELEASE statement are to be disconnected at commit.

CONDITIONAL

Specifies that the database connections that have been marked RELEASE or have no open WITH HOLD cursors are to be disconnected at commit.

AUTOMATIC

Specifies that all database connections are to be disconnected at commit.

SQLRULES

DB2

Specifies that a type 2 CONNECT is to be processed according to the DB2® rules.

STD

Specifies that a type 2 CONNECT is to be processed according to the Standard (STD) rules based on ISO/ANS SQL92.

SYNCPOINT

Specifies how commits or rollbacks are to be coordinated among multiple database connections. This command parameter is ignored and is only included here for backward compatibility.

ONEPHASE

Specifies that no transaction manager (TM) is to be used to perform a two-phase commit. A one-phase commit is to be used to commit the work done by each database in multiple database transactions.

TWOPHASE

Specifies that the TM is required to coordinate two-phase commits among those databases that support this protocol.

NONE

Specifies that no TM is to be used to perform a two-phase commit, and does not enforce single updater, multiple reader. A COMMIT is sent to each participating database. The application is responsible for recovery if any of the commits fail.

CONNECT_MEMBER (partitioned database or DB2 pureScale® environments)

member-number

Specifies the member to which a connect is to be made. For a partitioned database environment, valid values are between zero and 999, inclusive; for a DB2 pureScale environment, valid values are between 0 and 127, inclusive. Overrides the value of the DB2NODE environment variable.

CATALOG_DBPARTITIONNUM

In a partitioned database environment, specifying this value permits the client to connect to the catalog database partition of the database without knowing the identity of that database partition in advance. In a DB2 pureScale environment, specifying this option is not permitted (SQLSTATE 56038).

ATTACH_MEMBER member-number (partitioned database or DB2 pureScale environments)

Specifies the member to which an attach is to be made. For a change to ATTACH_MEMBER to take effect, you must first detach from the instance using the DETACH command, then attach to the instance using the ATTACH command. For a partitioned database environment, valid values are between zero and 999, inclusive; for a DB2 pureScale environment, valid values are between 0 and 127, inclusive. Overrides the value of the DB2NODE environment variable .

Usage notes

SET CLIENT cannot be issued if one or more connections are active.

If SET CLIENT is successful, the connections in the subsequent units of work will use the connection settings specified. If SET CLIENT is unsuccessful, the connection settings of the back-end process are unchanged.

In partitioned databases or DB2 pureScale environments, the connection settings could have an impact on acquiring trusted connections. For example, if the CONNECT_MEMBER option is set to a node such that the establishment of a connection on that node requires going through an intermediate node (a hop node), it is the IP address of that intermediate node and the communication protocol used to communicate between the hop node and the connection node that are considered when evaluating this connection in order to determine whether or not it can be marked as a trusted connection. In other words, it is not the original node from which the connection was initiated that is considered. Rather, it is the hop node that is considered.

In partitioned databases or DB2 pureScale environments, the connection settings could have an impact on how the connection is assigned to a member subset. For example, if the CONNECT_MEMBER option is set to a node such that the establishment of the connection on that node requires going through an intermediate node (a hop node), it is the actual database name that is used by the hop node to establish a connection at the connection node. The actual database name provided by the hop node is considered when assigning the connection to a member subset. It is not the database alias that the original connection targeted that is considered for member subset assignment. It is the database name that is considered for member subset assignment.

Compatibilities