pajama

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.

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.

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.

You do not have exclusive access to the table. This means there is a lock on the table or there is a select running on the table. An ALTER TABLE operation has a condition that only the session running the ALTER TABLE is accessing the table or it will fail immediately. Having LOCK MODE set to WAIT or having an exclusive lock on the table will not change the behavior.

1. Determine the partnum or tblsnum of the table you are trying to alter. Run this select in the database where your table resides:

select hex(partnum) from systables where tabname = "your_table_name"

For this example the value returned is 0x00100045

2. Look for a lock on the table. You will search for table's partnum in the onstat -k output. The onstat -k output does not display the 0x or leading 0. This means 0x00100045 in the onstat -k output would look like 100045 Run this command:

onstat -k | grep 100045

If nothing is returned, then there are no locks on the table.

3. Check to see if the table is open ( most likely has one or more selects running on it ). Run this command:

onstat -g opn | grep 0x00100045

If nothing is returned, then the table is not being accessed and you can run your ALTER TABLE command. If you do find that the table is open, then proceed to Resolving the problem.

• If you are running ALTER FRAGMENT on TABLE then starting in v11.50 and above you can set FORCE_DDL_EXEC to on in your environment then rerun the ALTER FRAGMENT. Here are a couple links that provide detailed description of FORCE_DDL_EXEC:
  
  FORCE_DDL_EXEC Environment Option 
  Forcing out transactions when altering table fragments 
  
  
• Wait for users to leave the table then rerun your ALTER TABLE. You can run LOCK TABLE tab1 IN EXCLUSIVE MODE so new users cannot lock the table. Users can access the exclusively locked table if their isolation level is set to dirty read. A dirty read select counts as a user accessing the table and will cause the ALTER TABLE to fail.
  
• Run onstat -g sql 0. This will give you a list of all the sql running on the database server. You can find all sqls running on the table. The output will also give you the session id. You can then run onmode -z session_id on each session accessing the table. onmode -z will destroy an active session so use it with caution. Run the ALTER TABLE.
  
• Run this select statement in sysmaster database to get a list of all sessions with an sql referencing your table:
  
  select scs_sessionid from syssqlcurses where scs_sqlstatement like "%your_table%"
  
  The first row returned is the session id for the session running this select. You can then run onmode -z on the sessions accessing the table. onmode -z will destroy an active session so use it with caution.
  
• Set IFX_DIRTY_WAIT. For details click this link:
  http://www.ibm.com/support/docview.wss?rs=630&uid=swg21174239
  
• Restart the database server and run the ALTER TABLE immediately.