트리거 삭제시 242 오류가 발생하는 원인

안녕하세요. 인포믹스에서 트리거를 drop 또는 disable 하려고 할 때 242 오류가 발생하는 경우가 있습니다.

 

트리거가 설정된 테이블에 INSERT, UPDATE, DELETE와 같은 LOCK을 유발하는 트랜잭션이 아닌 커서가 오픈된 경우라도 drop이나 disable은 허용이 되지 않는 것 같습니다.

 

아래는 이와 같은 상황에 대한 조치방법을 설명한 문서입니다.

 

 

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.

 

출처 : https://www.ibm.com/support/pages/dropping-or-disabling-trigger-fails-error-242-106

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