Dropping or disabling a trigger fails with error -242 / -106

Technote (troubleshooting)

Problem(Abstract)

You are trying to drop or disable a trigger but such operation fails with following errors:

242: Could not open database table <table_name>

106: ISAM error: non-exclusive access

Symptom

You have trapped error -106 using 'onmode -I 106' but in the gathered output of 'onstat -k' you do not see any locks on the table for which the error was returned.

Cause

Certain DDL operations require exclusive lock placed on a table. The corresponding session also checks if the table's partition is not used by any other sessions in DIRTY READ mode.

Resolving the problem

There are two possible workarounds for this problem:

1) Use LOCK MODE WAIT for the session which runs the DDL statement.

2) Use undocumented $ONCONFIG parameter & environment variable NONEXCLTRIG.

You can set NONEXCLTRIG to the following values in $ONCONFIG file (engine restart is required so change could take effect):

NONEXCLTRIG values can be:

0 - Normal behaviour (exclusive lock is required)
1 - the trigger DDL can be done without exclusive access to table, only if NONEXCLTRIG environment variable is defined in the session environment.
2 - the trigger DDL can be done without exclusive access to table.

http://www-01.ibm.com/support/docview.wss?uid=swg21648816