数据库－db2－存储过程－746问题

Avoiding SQLCODE -746 in CALL in Trigger

Technote (FAQ)

Problem  

When issuing a CALL statement in a trigger, an SQLCODE SQL0746 is issued at runtime.  

Cause

The SQL0746 error prevents procedures called by a trigger to access tables which have previously been modified within the context of the invoking statement. These so called "mutating table conflicts" can cause non deterministic results and must be blocked.  

Solution

In DB2 V8.1 FP 11 (V8.2.4) a new registry variable DB2_RESOLVE_CALL_CONFLICT has been introduced which, when set, eliminates SQLCODE -746 errors in the context of triggers. With this variable set DB2 enforces that all modifications to tables are completed in compliance with the SQL Standard rules for triggers before executing the CALL statement.

Limitations:

Once DB2_RESOLVE_CALL_CONFLICT is set, procedures called directly or indirectly by a trigger must not perform any DDL.

This limitation includes:

* DECLARE GLOBAL TEMPORARY TABLE,

* CREATE/DROP TABLE,

* CREATE/DROP INDEX.

It is recommended for performance reasons to declare temporary tables outside procedures which are executed frequently. Instead of DROP/DECLARE DDL, DELETE statements or the ON COMMIT DELETE ROWS property of temporary tables should be used to re-initialize temporary tables.Following this recommendation will both improve performance and allow procedures using temporary tables to be called from triggers.

Usage:

1. Stop the DB2 instance
2. Execute "db2set DB2_RESOLVE_CALL_CONFLICT=YES" from a shell
3. Start the DB2 instance
4. Rebind any packages which cause invocation of triggers.

To rebind SQL Procedures use:
CALL SYSPROC.REBIND_ROUTINE_PACKAGE
('P','.','CONSERVATIVE');

Performance impact:

DB2_RESOLVE_CALL_CONFLICT causes DB2 to resolve all potential read/write conflicts through the injection of temporary tables as needed. Typically at most one temporary tables is injected which is very small in an OLTP environment because only one or a small number of rows are being modified by the triggering statement.Typically, when following the general recommendation to use SMS (system managed storage) for temporary tablespaces, the performance impact from setting DB2_RESOLVE_CALL_CONFLICT is expected to be low.