Parameters That Apply to MODIFY Only

In this section:

The following commands are available only within FOCUS MODIFY procedures, SET LOADONLY, SET AUTOCOMMIT ON CRTFORM, and SET ERRORRUN. The SET ISOLATION command, explained in ISOLATION (DB2), may also be issued from MODIFY. See Maintaining Tables With FOCUS, for a discussion of MODIFY.

When issuing commands from within MODIFY procedures, omit the sqlengine or the environmental prefix.


Top of page

x
LOADONLY

The Fastload facility increases the speed of loading data into tables. Use the LOADONLY command exclusively in those MODIFY procedures that insert rows. The syntax is:

SQL SET LOADONLY ON

You can use the Fastload feature only with ON NOMATCH INCLUDE operations. Other MODIFY operations generate an error message if SQL SET LOADONLY is invoked.

In MODIFY processing without Fastload, the adapter uses an SQL SELECT statement to test the existence of a single row. By examining the SQL return code, the adapter determines whether the row exists and directs MODIFY processing to the appropriate ON MATCH or ON NOMATCH logic.

The Fastload option eliminates this SELECT operation. It loads rows into the table without first evaluating their existence. The RDBMS ensures the uniqueness of stored rows with unique indexes.

Note:

Note: The INSERTSIZE parameter is only functional for consecutive executions of INSERT statements that are identical to each other (except for the values to be inserted). No other intervening SQL statements are allowed, including COMMIT WORK. If a statement is issued that in any way (other than the inserted values) differs from the current blocked INSERT statement in effect, the block is immediately transmitted to the RDBMS, even if the buffer is not full. This restriction has several ramifications:


Top of page

x
AUTOCOMMIT ON CRTFORM

How to:

The AUTOCOMMIT ON CRTFORM facility releases locks on database objects every time a CRTFORM is displayed. The integrity of data is protected by an automated Change Verify Protocol (CVP) as an alternative to the RDBMS standard method of locking for concurrency and integrity. The Change Verify Protocol consists of a series of steps that manage the integrity and concurrent update/retrieval activity of the database by committing open transactions prior to displaying each CRTFORM.

Note: This setting is not needed for Teradata because it implicitly commits every SQL statement individually.

AUTOCOMMIT ON CRTFORM does not apply to Maintain.

CVP lacks the unit-of-work capabilities of the RDBMS protocol, but CVP never retains locks on displayed records. The elimination of locks increases rates of transaction operations and improves terminal response times for interactive applications. All open transactions are automatically committed prior to using CVP for SELECT and UPDATE.

The Change Verify Protocol initiates these steps for each CRTFORM display:

  1. The adapter releases all locks held on the record. Before the record displays on the terminal, the adapter issues an SQL COMMIT WORK statement to release any existing RDBMS locks.
  2. The adapter retrieves the record and displays it on the terminal. The user enters a database update that results in an UPDATE, DELETE, or INCLUDE action.
  3. The adapter retrieves the record again. It compares the original and current values of the record to determine if another transaction changed the record in the interim. If the original (displayed) record has been modified in the time between the original retrieval and the update request, the update is rejected. If the original (displayed) record remains unchanged, the update is processed.

With the RDBMS standard method, SQL COMMIT WORK statements specified in applications define transactions or logical units of work (LUW). The RDBMS uses a locking mechanism to protect the LUW from interference by other concurrent transactions. The locking mechanism allocates and isolates database resources for the LUW.

The disadvantage to the RDBMS method is that terminal I/O in the LUW locks data for the indeterminate amount of time it takes the user to react to the terminal display. The locks are retained until the user completes the transactions successfully or until the RDBMS issues a ROLLBACK WORK command.

You can issue the AUTOCOMMIT ON CRTFORM command only from within a MODIFY CRTFORM procedure. Include it in CASE AT START, not in the TOP case. You may not switch AUTOCOMMIT modes within the MODIFY procedure.

The AUTOCOMMIT ON CRTFORM facility only works on single-record transactions. (For example, MODIFY MATCH and NEXT commands retrieve single records.) It is not designed for set processing with FOCUS multiple-record operations (REPEAT and HOLD, for example). For multiple-record processing, CVP applies only to the last record.



x
Syntax: How to Invoke the Change Verify Protocol

The syntax is

SQL SET AUTOCOMMIT {OFF|ON CRTFORM}

where:

OFF

Is the default. It retains the native RDBMS locking protocol.

ON CRTFORM

Invokes the Change Verify Protocol.

Note:


Top of page

x
ERRORRUN

With SET ERRORRUN ON, MODIFY processing continues even when a serious error occurs, allowing an application to handle its own errors in the event that an RDBMS error is part of the normal application flow. Code this command explicitly within the MODIFY procedure, preferably in CASE AT START, where it is executed once.

The syntax is

CASE AT START
SQL SET ERRORRUN {OFF|ON}
.
.
.
ENDCASE

where:

OFF

Causes MODIFY processing to stop when a fatal error is detected (for example, if the table name is not found). OFF is the default value.

ON

Allows MODIFY processing to continue despite fatal errors. Test the value of FOCERROR to determine appropriate action after an RDBMS call.

When SET ERRORRUN is ON, the MODIFY procedure reports the error but continues execution. The MODIFY code can then test the value of FOCERROR to determine the cause of the error and take appropriate action. Be careful in evaluating the contents of FOCERROR, as failure to respond to a negative SQLCODE, non-zero return code, or RDBMS error message can cause unpredictable errors in subsequent RDBMS or MODIFY processing.

SET ERRORRUN returns to its default setting at the end of the MODIFY procedure.


Information Builders