Options that govern unit of work semantics

The semantics of type 2 connection management are determined by a set of precompiler options. These options are summarized in the following list, with default values indicated by bold and underlined text.

  • CONNECT (1 | 2). Specifies whether CONNECT statements are to be processed as type 1 or type 2.
  • SQLRULES (DB2 | STD). Specifies whether type 2 CONNECTs are to be processed according to the Db2® rules, which allow CONNECT to switch to a dormant connection, or the SQL92 Standard rules, which do not allow this.
  • DISCONNECT (EXPLICIT | CONDITIONAL | AUTOMATIC). Specifies what database connections are to be disconnected when a commit operation occurs:
    • Those that have been explicitly marked for release by the SQL RELEASE statement (EXPLICIT)
    • Those that have no open WITH HOLD cursors, and those that are marked for release (CONDITIONAL)
    • All connections (AUTOMATIC).
  • SYNCPOINT (ONEPHASE | TWOPHASE | NONE). Specifies how COMMITs or ROLLBACKs are to be coordinated among multiple database connections. This option is ignored, and is included for backwards compatibility only.
    • Updates can only occur against one database in the unit of work, and all other databases are read-only (ONEPHASE). Any update attempts to other databases raise an error (SQLSTATE 25000).
    • A transaction manager (TM) is used at run time to coordinate two-phase COMMITs among those databases that support this protocol (TWOPHASE).
    • Does not use a TM to perform two-phase COMMITs, and does not enforce single updater, multiple reader (NONE). When a COMMIT or a ROLLBACK statement is executed, individual COMMITs or ROLLBACKs are posted to all databases. If one or more ROLLBACKs fail, an error (SQLSTATE 58005) is raised. If one or more COMMITs fail, another error (SQLSTATE 40003) is raised.

To override any of the previously listed options at run time, use the SET CLIENT command or the sqlesetc application programming interface (API). Their current settings can be obtained using the QUERY CLIENT command or the sqleqryc API. Note that these are not SQL statements; they are APIs defined in the various host languages and in the command line processor (CLP).