SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
F-31
Considerations—FREE RESOURCES
Considerations—FREE RESOURCES

If CONTROL TABLE SEQUENTIAL INSERT/UPDATE is set to ENABLE or ON,
FREE RESOURCES also flushes the insert/update buffer for audited and
nonaudited tables.

On audited tables, FREE RESOURCES releases locks acquired only within the
current TMF transaction using STABLE access. FREE RESOURCES releases
locks that apply only to data being read. Locks on data that has been inserted,
updated, or deleted by the current transaction are released automatically when the
transaction completes.
FREE RESOURCES does not affect a table lock on an audited table. The lock is
released when the transaction ends unless SQL acquired the lock under STABLE
access and no updates were performed.

On nonaudited tables, unless you specify AUDITONLY, FREE RESOURCES
releases all locks acquired by DML statements with REPEATABLE access,
whether or not a TMF transaction is in progress.
You must use either FREE RESOURCES or UNLOCK TABLE to release locks on
nonaudited tables under these conditions:

A DML statement with REPEATABLE access operates on nonaudited tables or
on views with underlying nonaudited tables.

LOCK TABLE statements are in effect.

A host program is returning control to a requester but holds locks on
nonaudited files because it used the AUDITONLY option on COMMIT WORK,
ROLLBACK WORK, or another FREE RESOURCES statement.
You must use FREE RESOURCES or COMMIT WORK to ensure the
insert/update buffer is flushed for nonaudited tables before you exit SQLCI or a
host program. (In programs, you must then check SQLCA for flush errors.)

FREE RESOURCES closes all cursors on nonaudited tables or views unless you
specify the AUDITONLY option.
FREE RESOURCES closes all cursors with BROWSE access to audited tables or
views. FREE RESOURCES closes a cursor with STABLE or REPEATABLE access
to an audited table only if the FREE RESOURCES statement executes in the same
transaction as the most recent OPEN of the cursor.
Examples—FREE RESOURCES

This example shows FREE RESOURCES used to release locks acquired by
SQLCI on a nonaudited file.
Suppose that JOB is a nonaudited table and AUTOWORK AUDITONLY is ON.
Because the INSERT uses REPEATABLE access, locks are not released when the