x

SET AUTOCOMMIT issues an SQL COMMIT WORK each time the specified event occurs. Until a COMMIT WORK, changes to the target data source are conditional and locks are held on the affected data. Other users may have their work delayed waiting for locks to be released.

COMMIT WORK completes the changes to the data source and releases locks, improving concurrency. On the other hand, delaying the COMMIT preserves the integrity of processed data through several FOCUS commands or an entire FOCUS session.

Teradata implicitly commits each SQL statement individually, so the COMMIT action is redundant.

x

SET AUTOCLOSE initiates the DB2 Call Attachment Facility (CAF) CLOSE operation or sponsors a Teradata LOGOFF. It is ignored for IDMS/SQL or Oracle.

For DB2, it determines how long a thread (the connection between the application program in the user's address space and the DB2 application plan) is open. The thread is not the same as the address space connection to DB2. The AUTODISCONNECT setting, discussed in a subsequent section, controls that connection. In FOCUS, a DB2 application program is one of the following:

• The dynamic FOCUS Adapter for DB2.
• A MODIFY procedure using static SQL.
• A CALLDB2 subroutine using embedded SQL.

Generally speaking, each program has a corresponding plan. (Static SQL (DB2), includes a discussion of plan management.)

A site that installs a DB2 subsystem determines the maximum number of concurrent users (threads) the subsystem will support. Since each user requires enough virtual storage for his application plan, this setting controls the amount of storage the site wants to allocate to active DB2 users at any one time.

The CAF CLOSE command de-allocates the DB2 thread, releasing the virtual storage for the application plan. DB2 requires that an existing thread to a plan be closed before a thread to another plan is opened. If a thread is closed without a subsequent OPEN operation, the closed thread becomes inactive. The user is still connected to DB2, but not to a particular application plan. The user (task) still owns the thread. It is not available to other users. To release the thread, the user must disconnect completely from DB2.

For Teradata, the SET AUTOCLOSE command enables users to control logon and logoff interaction with the Teradata RDBMS. After the Teradata RDBMS processes a FOCUS request or native DBC/SQL command, the connection (or communications path) may be deactivated or retained depending on the AUTOCLOSE setting.

• If the AUTOCLOSE setting is ON FIN, the initial logon connection is retained and remains active for the duration of the FOCUS session. The adapter initiates a Teradata logon request at the first native DBC/SQL command or FOCUS request requiring Teradata services, provided a valid SET CONNECTION_ATTRIBUTES command has been issued. The connection is released at the FIN command.
• If the AUTOCLOSE setting is ON COMMAND, the initial logon connection is released after Teradata processing and, for each subsequent request, is re-established. The adapter logs on prior to the DBC/SQL call and generates a Teradata logoff request after completing each FOCUS command (TABLE request, MODIFY procedure, or Direct SQL Passthru request). Teradata resources are freed prior to report generation and display. Although the logoff request is issued and the connection to Teradata is disconnected, the FOCUS session remains active.

Any other setting defaults to AUTOCLOSE ON FIN. AUTOCLOSE ON FIN minimizes overhead required for repetitive interaction with the Teradata RDBMS by retaining the connection to Teradata. By contrast, AUTOCLOSE ON COMMAND frees resources that are no longer (or infrequently) required.

Note For Teradata:

• The SET CONNECTION_ATTRIBUTES command described in Adapter Commands functions independently from the SET AUTOCLOSE command.
• Regardless of the AUTOCLOSE setting, the FIN command to exit the session generates an explicit logoff request. This ensures a successful user exit from the Teradata environment. Teradata frees the resources required for the session.

x

For DB2, DISCONNECT completely detaches the user's address space (or task) from DB2. This differs from CLOSE because, after a CLOSE, the FOCUS task is still connected to the DB2 subsystem and can open a thread to another plan. After a DISCONNECT, the FOCUS task must reestablish its connection to DB2 before doing any database work. FOCUS tasks that frequently issue the DISCONNECT command are connected to DB2 for shorter periods of time, allowing other tasks to connect and acquire threads as needed. However, there is significant system overhead associated with frequently connecting and disconnecting, and the possibility exists that no thread will be immediately available when the task attempts to reconnect.

For Teradata and IDMS/SQL, DISCONNECT is ignored. For Teradata, use AUTOCLOSE instead.

For Oracle, DISCONNECT sponsors a LOGOFF from the Oracle RDBMS. AUTODISCONNECT ON COMMIT is not supported. It will be converted to AUTODISCONNECT ON FIN.