Establishing Different Types of FOCUS Sessions

You can establish the following three types of FOCUS sessions by varying the combinations of SET AUTOaction ON event commands. Each is illustrated in a subsequent section: The default data adapter session uses the settings AUTOCOMMIT ON COMMAND, AUTOCLOSE ON FIN, and AUTODISCONNECT ON FIN. The user-controlled session uses the settings AUTOCOMMIT ON FIN, AUTOCLOSE ON FIN, and AUTODISCONNECT ON FIN.

In this section: The Default Data Adapter Session The User-Controlled Session The Pseudo-Conversational Session Example: Explicit Control of a Logical Unit of Work (LUW)

Note:

Teradata implicitly commits each SQL statement individually unless they are enclosed in BEGIN TRANSACTION and END TRANSACTION statements.

• The pseudo-conversational session uses the settings AUTOCOMMIT ON CRTFORM, AUTOCLOSE ON COMMIT, and AUTODISCONNECT ON COMMIT.

The Default Data Adapter Session

The following illustration shows the duration of connections, threads, and Logical Units of Work (LUWs) using the default data adapter settings:

connection |--------------------------------------------------------->| |------LUW----->|--- LUW ------>| |--- LUW ----->| F | . . . . . . F O C U S . . . s e s s i o n . . . . . . . . . . I N | | | | | cmd user cmd cmd cmd beg COMMIT end beg end |--------- MODIFY ------------->| |----TABLE --->| (a) (b) (c) (d) (e) (f) (g) AUTOCOMMIT ON COMMAND AUTOCLOSE ON FIN AUTODISCONNECT ON FIN

The FOCUS session begins at point (a) and ends at point (g), with the FIN command. The connection is established at point (b), the first MODIFY call to the RDBMS, and is retained for the entire FOCUS session.

The LUW initiated at point (b) by the MODIFY is terminated by the explicit COMMIT issued at point (c) within the MODIFY. Point (c) also marks the start of the next LUW which is retained until the end of the MODIFY command at point (d), where the data adapter automatically generates a COMMIT.

A third LUW begins for the TABLE request. It is terminated by the COMMIT automatically generated at the end of the TABLE command. At FIN, the data adapter terminates the connection to the RDBMS. No COMMIT is generated at FIN.

Note: For DB2, the thread is opened when the connection is established and closed when the connection is severed.

The User-Controlled Session

The following illustration shows a session in which the user controls the duration of each Logical Unit of Work (LUW). The data adapter does not automatically issue any COMMIT, CLOSE, or DISCONNECT command until after the FIN at the end of the FOCUS session:

Note: The Teradata Data Adapter does not support this type of session.

connection |------------------------------------------------------------->| |----------LUW------------------------>| |--LUW-->| F | . . . . . . F O C U S . . . s e s s i o n . . . . . . . . . . . . I N | | | | | | | cmd cmd cmd cmd user cmd cmd beg end beg end COMMIT beg end |----TABLE--->| |-PASSTHRU->| |MODIFY >| (a) (b) (c) (d) (e) (f) (g) (h) (i) AUTOCOMMIT ON FIN AUTOCLOSE ON FIN AUTODISCONNECT ON FIN

The FOCUS session begins at point (a) and ends at point (i) with the FIN command.

The connection is established by the TABLE command at point (b) and retained until FIN. The LUW also starts at point (b). It is completed at point (f) when the user issues COMMIT WORK as an SQL Passthru request.

The final LUW, triggered by the MODIFY procedure, is not terminated until FIN since there is no other user-issued COMMIT.

Note: For DB2, the thread is opened when the connection is established and closed when the connection is severed.

The Pseudo-Conversational Session

The next illustration shows how connections can be dropped and re-established within a MODIFY procedure. The AUTOCOMMIT ON CRTFORM, issued within the MODIFY, automatically generates a COMMIT whenever a CRTFORM is displayed.

Note: The Teradata Data Adapter does not support this type of session.

|--connection--->|--connection->|-conn-->|--connection-->| |--LUW---------->|--LUW-------->|-LUW--->|--LUW--------->| F | . . . . . . F O C U S . . . s e s s i o n . . . . . . . . . . . . I N | | | | | cmd CRTFORM user CRTFORM cmd beg COMMIT end |---------------------MODIFY---------------------------->| (a) (b) (c) (d) (e) (f) (g) AUTOCOMMIT ON CRTFORM AUTOCLOSE ON COMMIT AUTODISCONNECT ON COMMIT

The FOCUS session begins at point (a) and ends at point (g) with the FIN command.

The connection and Logical Unit of Work (LUW) are established at point (b), the beginning of the MODIFY procedure. The first CRTFORM triggers a COMMIT, terminating the LUW and connection. The data adapter automatically re-establishes them at the next call to the RDBMS, and they are terminated by the user-issued COMMIT at point (d).

The process is repeated two more times. At the end of the MODIFY procedure, point (f), AUTOCOMMIT reverts to its previous (default) COMMAND setting, and the data adapter generates a COMMIT.

Note: For DB2, a thread is opened when the connection is established and closed when the connection is severed.

Example: Explicit Control of a Logical Unit of Work (LUW)

In prior releases, the data adapter automatically followed each native SQL request with a COMMIT; therefore, you could not have more than one native SQL command in a single LUW.

The following example demonstrates how to explicitly control the scope of an LUW using the expanded AUTO command settings. Numbers to the left refer to explanatory notes that follow the example.

Dialogue Manager control statements govern COMMIT or ROLLBACK processing based on the &RETCODE value. The example treats any value other than 0 as a failure:

    -TOP
    SQL DB2 SET AUTODISCONNECT ON FIN
    SQL DB2 SET AUTOCLOSE ON FIN
1.  SQL DB2 SET AUTOCOMMIT ON FIN
 
2.  SQL DB2 LOCK TABLE XYZ IN EXCLUSIVE MODE
    END
3.  -IF &RETCODE NE 0 GOTO ROLLBACK ;
 
4.  SQL DB2 INSERT INTO XYZ VALUES ('A','B','C','D') ;
    END
    -IF &RETCODE NE 0 GOTO ROLLBACK ;
 
    SQL DB2 INSERT INTO XYZ VALUES ('E','F','G','H') ;
    END
    -IF &RETCODE NE 0 GOTO ROLLBACK ;
 
    SQL DB2 INSERT INTO XYZ VALUES ('I','J','K','L') ;
    END
    -IF &RETCODE NE 0 GOTO ROLLBACK ;
 
5.  SQL DB2 COMMIT WORK;
    END
    -IF &RETCODE NE 0 GOTO ROLLBACK ;
 
    -GOTO OUT
 
    -ROLLBACK
    SQL DB2 ROLLBACK WORK;
    END
 
    -OUT
6.  SQL DB2 SET AUTOCOMMIT ON COMMAND

Note:

1. AUTOCOMMIT is set to FIN. No COMMIT is issued unless specifically coded. As is required for this AUTOCOMMIT setting, AUTODISCONNECT and AUTOCLOSE are also set to FIN.

2. Table XYZ is locked for this program's exclusive use. This lock will be terminated at a COMMIT or ROLLBACK point.

3. The procedure checks &RETCODE. If it is not zero, the SQLCODE is displayed to show that the lock was not completed; the procedure issues a ROLLBACK and terminates. This &RETCODE test is executed after every SQL statement passed to DB2.

4. Three rows are inserted into XYZ.

5. The program issues an explicit COMMIT making the inserts to XYZ permanent and releasing the exclusive lock.

6. SET AUTOCOMMIT is reissued to restore automatic command-level COMMITs.