Understanding Implicit Updatability

The FIPS 127.1 standard does not allow a FOR UPDATE clause in a DECLARE CURSOR statement. In this standard, updatability of a cursor rests solely on the cursor definition.

In contrast, ALLBASE/SQL default processing (i.e., flagging is not in effect) of a DECLARE CURSOR statement having no FOR UPDATE clause is to allow neither update nor delete on any column in the cursor definition.

When the FIPS flagger is set and a FOR UPDATE clause is not specified, standard updatability processing takes effect. That is, any column in the cursor's select list can be updated or deleted. This is known as implicit updatability. When implicit updatability is in effect, a performance impact may be realized as compared to ALLBASE/SQL default processing, as follows:

An index scan can only be applied to an EQUAL predicate containing an index column. An index scan is suppressed for a predicate containing a range factor.
More severe locks (intent update) are required. Therefore, if the cursor is to be used only for reading data, unnecessary locking overhead my be incurred. To alleviate this type of overhead, a FOR READ ONLY clause can be used when declaring the cursor.

Note that when the FIPS flagger is set and a FOR UPDATE clause is used in the cursor definition, a warning message is generated, and ALLBASE/SQL default processing overrides the semantics of the standard.

Understanding Implicit Updatability

Technical documentation

» Table of Contents

» Index