any recommendation on NONEXCLTRIG parameter?

Hi Informix community, We are facing an issue to create trigger on a table , always get error saying not able to obtain exclusive lock. I did try to set lock mode to wait, it didn't help. ( db runs on 14.10.fc10w2 ,linux) When I checked onstat -k and onstat -g ses 0 output, there is no lock to that table, no session accessing it at all. This table does replicate data via cdr to another informix server though. So I think cdr puts locks and prevents schema change to this table. Raised a case to ibm and got advice to use NONEXCLTRIG parameter. However it requires db restart to take effective. It's an undocumented parameter that I didn't use before. I don't know if any potential impact if I set it to 1 in $ONCONFIG (then bounce db). May I ask for advice on whether it's safe to use this parameter? Is there any better option to create trigger in my case? Thank you! ------------------------------ ke chen ------------------------------

Hello, I was recently advised to use onstat -g opt to get list of threads and open tables by these threads as some DDL operations requires that tables cannot be even open without any locks by other sessions. Milan Rafaj --

Hi, the error you're getting (106 - non-exclusive access) does not mean " not able to obtain exclusive lock", in fact it has nothing to do with locks. Instead it means this statement is not the only one currently having this table open, and there seem to be other queries or cursors currently having this table open. In such situation, try running " onstat -g opn" and search its output for the respective table. This onstat option gives you the open partitions per thread (identified by rstcb and tid at the start of each section), so for every match you'd take the respective rstcb address, minus the leading 0x, and search onstat -ufor it to obtain the thread's session ID (sessid). This way you'd learn which session(s) is/are in your way. onstat -g stm [<sessid>]would even tell you the sessions' statements which you could use to verify which ones exactly still have the table open. onstat -g ses <sessid>will tell you who (UID, process, host) owns the session in case you want to locate them. You now can either shut those applications down or, a little more brute-force, terminate those sessions using onmode -z <sessid> .As for NONEXCLTRIG, this is an old, yet hardly used (since undocumented and off by default) feature, so it's also a little hard to know its outcome, for instance whether those concurrent queries, when continuing to run, will notice and execute your new trigger. On the other hand, after a restart, you might not even have this problem unless those concurrent applications immediately start accessing your table again. HTH, Andreas ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

thank you so much! ------------------------------ ke chen ------------------------------

We have this set at several sites and it works just fine: https://www.ibm.com/support/pages/dropping-or-disabling-trigger-fails-error-242-106 See also: https://www.oninitgroup.com/faq-items/informix-stored-procedure-to-list-open-tables ------------------------------ Doug Lawry Oninit Consulting ------------------------------