ALLBASE/SQL Reference Manual (36216-90216)

Chapter 12 539
SQL Statements S - Z
SET SESSION
statements used to set transaction attributes.
• When using RC or RU, you should verify the existence of a row before you issue an
UPDATE statement. In application programs that employ cursors, you can use the
REFETCH statement prior to updating. REFETCH is not available in ISQL. Therefore, you
should use caution in employing RC and RU in ISQL if you are doing updates.
• If the FILL or PARALLEL FILL option has already been set for the session with a SET
SESSION statement, and you do not want either of these options in effect for a given
transaction, specify NO FILL in the transaction's BEGIN WORK statement.
• As with the SET CONSTRAINTS statement, the SET SESSION statement allows you to
set the UNIQUE, REFERENTIAL or CHECK constraint error checking mode. If the
constraint checking mode is deferred, checking of constraints is deferred until the end
of a transaction or until the constraint mode is set back to immediate. If the constraint
mode is immediate, integrity constraints are checked following processing of each SQL
statement (if statement level atomicity is in effect) or each row (if row level atomicity is
in effect). Refer to the SET DML ATOMICITY statement in this chapter for further
information on statement and row level error checking. The following paragraph
assumes that statement level atomicity is in effect.
When constraint checking is deferred, a COMMIT WORK,orSET CONSTRAINTS IMMEDIATE
statement executes if zero constraint violations exist at that time, otherwise a
constraint error is reported. When constraint checking is immediate (the default), zero
constraint violations must exist when an SQL statement executes, otherwise a
constraint error is reported and the statement is rolled back. The SET CONSTRAINTS
statement in this chapter gives further detail about constraint checking.
• As with the SET DML ATOMICITY statement, the SET SESSION statement allows you
to set the general error checking level in data manipulation statements. General error
checking refers to any errors, for example, arithmetic overflows or constraint violation
errors.
Setting ROW LEVEL atomicity guarantees that internal savepoints are not generated.
For example, if an error occurs on the
n th row of a bulk statement such as LOAD, BULK
INSERT, or Type2 INSERT, the row is not processed, statement execution terminates,
and any previously processed rows are
not rolled back. In contrast, STATEMENT
LEVEL atomicity guarantees that the entire statement is rolled back if it does not
execute without error. STATEMENT LEVEL atomicity is the default. Refer to the SET
DML ATOMICITY statement in this chapter for further information on statement and row
level error checking.
• In contrast to the SET TRANSACTION statement, transaction attributes set within a
transaction by a SET SESSION statement are
not sensitive to savepoints. That is, if
you establish a savepoint, then issue the SET SESSION statement to change attribute(s
)for the session, and then roll back to the savepoint, the transaction attribute(s) set
after the savepoint are
not undone. In this case, the attribute(s) would go into effect for
the next and subsequent transactions, just as if no rollback to savepoint had occurred.
See Chapter 2 , “Using ALLBASE/SQL,” "Scoping of Transaction and Session
Attributes" section for information about statements used to set transaction attributes.
• The SET SESSION statement is not allowed within a stored procedure.
• When ON TIMEOUT ROLLBACK or DEADLOCK ROLLBACK is set to