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/
