NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
U-5
Considerations—UPDATE
higher than the actual number of rows changed. The data in the table is correct and
the message correctly reports the number of times rows were updated. This behavior
(a variation of the “Halloween problem” described in computer science literature)
occurs when all of the following are true:
°
The optimizer chooses the alternate index as the access path.
°
The index columns in the equal-predicate are not changed by the update.
°
Another index column of the same index is updated to a higher value (if the
column is stored in ASCENDING order, or a lower value if the column is stored
in DESCENDING order).
•
CHARACTER and VARCHAR padding
For a fixed-length character column, an update value shorter than the column length
is padded with single-byte ASCII blanks (HEX20) to fill the column.
For a varying-length character column, an update value is not padded; its length is
the length of the value specified. In an entry-sequenced table, a value that updates a
varying-length character column must be the same length as the value it replaces.
•
Buffering UPDATE operations
See CONTROL TABLE Directive
on page C-72 for information on buffering
UPDATE operations.
•
Use in host programs
The following guidelines apply specifically to using the UPDATE statement in host
programs:
°
A TMF transaction must be in progress if you use the WHERE CURRENT OF
clause to update an audited table or view. The same TMF transaction must
include the OPEN cursor, FETCH, and UPDATE operations.
°
When using an SQL cursor in a host language program, an UPDATE WHERE
CURRENT operation provides a performance benefit over a stand-alone
UPDATE operation. Updating through a cursor uses virtual sequential block
buffering (VSBB) unless another cursor or a stand-alone UPDATE or DELETE
operation for the same table is used within the same process.
Use an UPDATE WHERE CURRENT operation instead of a stand-alone
operation to access a table (directly or through a view) within the same process
whenever possible. Using a stand-alone operation or another cursor to access a
table (directly or through a view) within the same process invalidates VSBB.
Invalidating VSBB can degrade performance substantially.
°
SQL returns the following values to the SQLCODE variable after a DELETE:
0 The UPDATE succeeded