Brian, This is absolutely horrendous (my personal opinion). See the email below dated a couple of years back from Charles Stevenson that describes the problem and the fix. This is so nasty that I kept the mail!
Martin Phillips Ladybridge Systems 17b Coldstream Lane, Hardingstone, Northampton NN4 6DB +44-(0)1604-709200 =========== Credit: Primarily Glenn Herbert. Thank-you. Cause: LOCK.WAIT param set to 0 instead of default 3600 (seconds) during UV.LOGIN Workaround: Set LOCK.WAIT to very large number. E.g.: ASSIGN 86400 TO SYSTEM(1999) (basic statement) SET.SQL LOCK.WAIT 86400 (tcl command) Summary: In Rev 8.3.3 UV introduced the LOCK.WAIT "feature" and set the default to 3600 (seconds). This parameter says that if a LOCKED clause is missing, after [LOCK.WAIT] seconds, then ignore the lock and proceed with the ELSE clause ! ? ! ? ! Until now, the workaround for this "feature" has been to turn off by setting the parameter to 0, typically via ASSIGN 0 TO SYSTEM(1999) typically from a program executed from UV.LOGIN, the routine in uv/VOC that everyone hits when they enter the universe shell. See GTAR 22465, 22755: http://u2techconnect.informix.com/u2kbprinter.asp?kbid=G22465 http://u2techconnect.informix.com/u2kbprinter.asp?kbid=G22755 This method has worked since rev 8.3.3 ( I DO HOPE MOST OF YOU DO THIS ), except...as we just discovered...not for type 25 files (Btree) tables. Alternate keys are implemented as type-25 files. If a process tries to update an index while another holds the relevant lock, error 010014 "WRITE failure" is triggered. Long-term fix: I think both Datastage & IBM will correct the bug in their next releases. The bug exists in both products. !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !! C A U T I O N !! C A U T I O N !! C A U T I O N !! C A U T I O N !! I am surprised this latent bug has not surfaced until now. It probably means many sites leave the LOCK.WAIT parameter at 3600. I am open to suggestions, by I cannot imagine a single case where that is a good idea: If someone else holds a lock, ignore it and pretend the record does not exist? ! No, no, no. If you have already set LOCK.WAIT to 0, then * your heavily used indexes may be corrupt. The more heavily used, the more likely a lock collision. * Change the "0" to a very large number so a lock will be waited on until the next uv shutdown. Else * Change the 3600 to a very large number so a lock will be honored. !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Personal opinion, impressions, & comments: I first reported this problem at the beginning of April. It prevented me from implementing much needed indices. At that point the problem only occurred in my own testing. Our existing production indices were fine. When performance became unbearable I introduced the needed indices mid-May since no one else was having trouble, and mine was theoretical. At that point I still had no official response from IBM. I should have been pushing harder via Strategy 7, through whom we get support. After production problems occurred, response from IBM was still very slow. Only after no visible progress did I report the error to this list. I had held back, hoping to give IBM the chance to solve it so I could post a solution rather than a complaint. Once I posted here, Glenn Herbert emailed me off-line with a way to create logs of events surrounding my errors. Ascential digested it (We are Ascential customers, too, by the way.) and Glenn thought the problem was in the way locks were being handled. He & IBM were talking together, too. Yesterday Glenn came back with the comment that "LOCK.WAIT 0" seemed unusual. That triggered my memory about gtar 22465 so I changed my UV.LOGIN and bingo: no more problems! And for the first time someone else was able to reproduce my own errors. If Glenn had not stepped in, IBM's plan was to give me a version of uvsh that had some debug/logging hooks. That would probably have happened yesterday. I would have generated my errors, sent them the log files and waited for their analysis. I hope to re-introduce one critical index over the weekend. If I have misstated anything, the fault is mine and I humbly leave it to the others to correct. Others on this list have chimed in and run my sample tests, too. I am grateful to you all. Sincerely, Charles Stevenson, DBA BP/IS Getronics 836 North Street Tewksbury, MA 01876 Tel: 978-858-6872 Fax: 978-858-8631 [EMAIL PROTECTED] www.getronics.com/us ------- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/