|
IDS Forum
RE: Sysprocplan being locked from time to time
Posted By: Vivek CHAUDHARY Date: Tuesday, 28 September 2004, at 2:24 p.m.
In Response To: Re: Sysprocplan being locked from time to time (Rajib Sarkar )
Hi Rajib,
We have a similar problem where we find "sysdistrib" table locked up during "update statistics" causing it to fail. It doesn't happen always. I have not been able to pinpoint the cause. Earlier Informix developed a patch for us to overcome this bug. IDS7.31FD6W4 was the release. When we had this error again, we were told that this patch was not meant to fix this bug.
Any help would be greatly appreciated.
thanks,
Vivek
-----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Rajib Sarkar Sent: Tuesday, September 28, 2004 8:54 AM To: ids@iiug.org Subject: Re: Sysprocplan being locked from time to time [3496]
--0__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: multipart/related; Boundary="1__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939"
--1__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: multipart/alternative; Boundary="2__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939"
--2__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable
There's a bug 161680 which occurs when after a UPDATE STATS on a table = (say table1) and there's another table which has got a trigger which execute= s a stored proc to update table1 can hold locks on sysprocplan long enough = to cause locking issues. This has been fixed in 7.31.UD8.
165202 -- Deadlock can occur (if SET LOCK MODE TO WAIT is set otherwise=
211/144 error) when UPDATE STATS for procedure and EXECUTE procedure is=
done simulataneously. fixed in 9.40.UC5
I could find these 2 in our KB ..but there maybe more .. u need to open= up a case with Tech support for more investigation into the issue.
Thanx much,
Rajib Sarkar Advisory Software Engineer DB2/UDB Regional Advanced Support IBM Data Management Group
If we all did the things we are capable of doing, we would literally astound ourselves. -- T. Edison
= jpierrot@chubb.co = m = = To
09/28/2004 07:58 Rajib Sarkar/Phoenix/IBM@IBMUS = AM = cc forum.subscriber@iiug.org, = ids@iiug.org = Subj= ect
Re: Sysprocplan being locked fro= m time to time [3476] = = = = = = =
The version is IDS 7.31.UD7 and I can't really say what triggered it . Other than it is happening on select statement in stored procedure. Doe= s that help?
Thanks!
JP
Rajib Sarkar <rsarkar@us.ibm.c om> = To jpierrot@chubb.com 09/27/2004 11:13 = cc AM forum.subscriber@iiug.org, ids@iiug.org Subj= ect Re: Sysprocplan being locked fro= m time to time [3476]
U didn't mention the release u r on ..and under what conditions you hit=
this problem.
Anyway, there r a number of bugs entered for this scenario ..so if you mention your exact scenario then probably it can be matched to an exist= ing bug.
Thanx much,
Rajib Sarkar Advisory Software Engineer DB2/UDB Regional Advanced Support IBM Data Management Group
If we all did the things we are capable of doing, we would literally astound ourselves. -- T. Edison
(Embedded image moved to file: pic09765.gif)jpierrot@chubb.com
jpierrot@chubb. com Sent by: forum.subscribe (Embedded image moved to file:=
r@iiug.org pic05356.gif) = To (Embedded image moved to=
09/24/2004 file: pic26833.gif) 08:39 AM ids@iiug.org (Embedded image moved to file:=
pic31786.gif) = cc (Embedded image moved to=
file: pic01528.gif) (Embedded image moved to file:=
pic02609.gif) Subj= ect (Embedded image moved to=
file: pic04363.gif) Sysprocplan being locked=
from time to time [3476]=
(Embedded image moved to file:=
pic06300.gif) (Embedded image moved to=
file: pic27005.gif)
To all, From time to time , the sysprocplan table is being locked even though t= hat "update statistics for procedure " were run or are run daily through cr= on. Note - No one is altering or modifying any objects in the database or h= ad done those two either prior to its reoccurrence and it is also recurrin= g sporadically. Each time it happens, I have to kill the session which ow= ns the lock and then re-run update stats for procedure to clear out thing= s. Any suggestions or thoughts relatively on known issues with stored procedure will be greatly appreciated.
Thanks!
Josue'
(See attached file: pic09765.gif)(See attached file: pic05356.gif)(See attached file: pic26833.gif)(See attached file: pic31786.gif)(See attac= hed file: pic01528.gif)(See attached file: pic02609.gif)(See attached file:=
pic04363.gif)(See attached file: pic06300.gif)(See attached file: pic27005.gif) =
--2__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable
<html><body> <p>There's a bug 161680 which occurs when after a UPDATE STATS on a tab= le (say table1) and there's another table which has got a trigger which= executes a stored proc to update table1 can hold locks on sysprocplan = long enough to cause locking issues. This has been fixed in 7.31.UD8.<b= r> <br> 165202 -- Deadlock can occur (if SET LOCK MODE TO WAIT is set otherwise= 211/144 error) when UPDATE STATS for procedure and EXECUTE procedure i= s done simulataneously. fixed in 9.40.UC5<br> <br> I could find these 2 in our KB ..but there maybe more .. u need to open= up a case with Tech support for more investigation into the issue.<br>=
<br> Thanx much,<br> <br> Rajib Sarkar<br> Advisory Software Engineer<br> DB2/UDB Regional Advanced Support<br> IBM Data Management Group<br> <br> <br> If we all did the things we are capable of doing, we would literally as= tound ourselves. -- T. Edison<br> <br> <img src=3D"cid:100__=3D88BBE58EDFC689398f9e8a93df938@us.ibm.com" width= =3D"16" height=3D"16" alt=3D"Inactive hide details for jpierrot@chubb.c= om">jpierrot@chubb.com<br> <br> <br>
<table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">=
<tr valign=3D"top"><td style=3D"background-image:url(cid:110__=3D88BBE5= 8EDFC689398f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wi= dth=3D"40%"> <ul> <ul> <ul> <ul><b><font size=3D"2">jpierrot@chubb.com</font></b><font size=3D"2"> = </font> <p><font size=3D"2">09/28/2004 07:58 AM</font></ul> </ul> </ul> </ul> </td><td width=3D"60%"> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">=
<tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:1= 20__=3D88BBE58EDFC689398f9e8a93df938@us.ibm.com" border=3D"0" height=3D= "1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">To</font></div></td><td width=3D"= 100%"><img src=3D"cid:120__=3D88BBE58EDFC689398f9e8a93df938@us.ibm.com"= border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">Rajib Sarkar/Phoenix/IBM@IBMUS</font></td></tr>
<tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:1= 20__=3D88BBE58EDFC689398f9e8a93df938@us.ibm.com" border=3D"0" height=3D= "1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">cc</font></div></td><td width=3D"= 100%"><img src=3D"cid:120__=3D88BBE58EDFC689398f9e8a93df938@us.ibm.com"= border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">forum.subscriber@iiug.org, ids@iiug.org</font></td></t= r>
<tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:1= 20__=3D88BBE58EDFC689398f9e8a93df938@us.ibm.com" border=3D"0" height=3D= "1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">Subject</font></div></td><td widt= h=3D"100%"><img src=3D"cid:120__=3D88BBE58EDFC689398f9e8a93df938@us.ibm= .com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">Re: Sysprocplan being locked from time to time [3476]<= /font></td></tr> </table>
<table border=3D"0" cellspacing=3D"0" cellpadding=3D"0"> <tr valign=3D"top"><td width=3D"58"><img src=3D"cid:120__=3D88BBE58EDFC= 689398f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" al= t=3D""></td><td width=3D"336"><img src=3D"cid:120__=3D88BBE58EDFC689398= f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""= ></td></tr> </table> </td></tr> </table> <br> <tt><br> The version is IDS 7.31.UD7 and I can't really say what triggered it .<= br> Other than it is happening on select statement in stored procedure. Doe= s<br> that help?<br> <br> Thanks!<br> <br> JP<br> <br> <br> = &= nbsp; &n= bsp; <br> Rajib Sarkar &= nbsp; &n= bsp; &nb= sp; <br> <rsarkar@us.ibm.c = &= nbsp; &n= bsp; <br> om> = &= nbsp; &n= bsp; To <br> = jpierrot= @chubb.com &nbs= p;<br> 09/27/2004 11:13 &nbs= p;  = ; cc <br= > AM &nbs= p; forum.subscri= ber@iiug.org, <br> = ids@iiug= .org &nb= sp; <br> = &= nbsp; &n= bsp; Subject <br> = Re: Sysp= rocplan being locked from <br> = time to = time [3476] <br= > = &= nbsp; &n= bsp; <br> = &= nbsp; &n= bsp; <br> = &= nbsp; &n= bsp; <br> = &= nbsp; &n= bsp; <br> = &= nbsp; &n= bsp; <br> = &= nbsp; &n= bsp; <br> <br> <br> <br> <br> U didn't mention the release u r on ..and under what conditions you hit= <br> this problem.<br> <br> Anyway, there r a number of bugs entered for this scenario ..so if you<= br> mention your exact scenario then probably it can be matched to an exist= ing<br> bug.<br> <br> Thanx much,<br> <br> Rajib Sarkar<br> Advisory Software Engineer<br> DB2/UDB Regional Advanced Support<br> IBM Data Management Group<br> <br> <br> If we all did the things we are capable of doing, we would literally<br= > astound ourselves. -- T. Edison<br> <br> (Embedded image moved to file: pic09765.gif)jpierrot@chubb.com<br> <br> = &= nbsp; &n= bsp; <br> = jpierrot@chubb. = = <br> = com &nbs= p;  = ; <br> = Sent by: = = <br> = forum.subscribe (Embedded image moved to file: &nb= sp;<br> = r@iiug.org pic05356.gif)  = ; <br> = &= nbsp; &n= bsp; To <br> = &= nbsp; (Embedded image moved to <br> = 09/24/2004 file:= pic26833.gif) <br> = 08:39 AM = ids@iiug.org <br= > = (= Embedded image moved to file: <br> = p= ic31786.gif) &n= bsp; <br> = &= nbsp; &n= bsp; cc <br> = &= nbsp; (Embedded image moved to <br> = &= nbsp; file: pic01528.gif) <br= > = (= Embedded image moved to file: <br> = p= ic02609.gif) &n= bsp; <br> = &= nbsp; &n= bsp; Subject <br> = &= nbsp; (Embedded image moved to <br> = &= nbsp; file: pic04363.gif) <br= > = &= nbsp; Sysprocplan being locked <br> = &= nbsp; from time to time [3476] <br> = &= nbsp; &n= bsp; <br> = &= nbsp; &n= bsp; <br> = (= Embedded image moved to file: <br> = p= ic06300.gif) &n= bsp; <br> = &= nbsp; (Embedded image moved to <br> = &= nbsp; file: pic27005.gif) <br= > = &= nbsp; &n= bsp; <br> = &= nbsp; &n= bsp; <br> <br> <br> <br> To all,<br> From time to time , the sysprocplan table is being locked even though t= hat<br> "update statistics for procedure " were run or are run daily = through cron.<br> Note - No one is altering or modifying any objects in the database or h= ad<br> done those two either prior to its reoccurrence and it is also recurrin= g<br> sporadically. Each time it happens, I have to kill the session which ow= ns<br> the lock and then re-run update stats for procedure to clear out = things.<br> Any suggestions or thoughts relatively on known issues with store= d<br> procedure will be greatly appreciated.<br> <br> Thanks!<br> <br> Josue'<br> <br> <br> <br> </tt><i>(See attached file: pic09765.gif)</i><i>(See attached file: pic= 05356.gif)</i><i>(See attached file: pic26833.gif)</i><i>(See attached = file: pic31786.gif)</i><i>(See attached file: pic01528.gif)</i><i>(See = attached file: pic02609.gif)</i><i>(See attached file: pic04363.gif)</i= ><i>(See attached file: pic06300.gif)</i><i>(See attached file: pic2700= 5.gif)</i><br> </body></html>=
--2__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939--
--1__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: image/gif; name="graycol.gif" Content-Disposition: inline; filename="graycol.gif" Content-ID: <100__=88BBE58EDFC689398f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64
R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7
--1__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: image/gif; name="pic19190.gif" Content-Disposition: inline; filename="pic19190.gif" Content-ID: <110__=88BBE58EDFC689398f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64
R0lGODlhWABDALP/AAAAAK04Qf79/o+Gm7WuwlNObwoJFCsoSMDAwGFsmIuezf///wAAAAAAAAAA AAAAACH5BAEAAAgALAAAAABYAEMAQAT/EMlJq704682770RiFMRinqggEUNSHIchG0BCfHhOjAuh EDeUqTASLCbBhQrhG7xis2j0lssNDopE4jfIJhDaggI8YB1sZeZgLVA9YVCpnGagVjV171aRVrYR RghXcAGFhoUETwYxcXNyADJ3GlcSKGAwLwllVC1vjIUHBWsFilKQdI8GA5IcpApeJQt8L09lmgkH LZikoU5wjqcyAMMFrJIDPAKvCFletKSev1HBw8KrxtjZ2tvc3d5VyKtCKW3jfz4uMKmq3xu4N0nK BVoJQmx2LGVOmrqNjjJf2hHAQo/eDwJGTKhQMcgQEEAnEjFS98+RnW3smGkZU6ncCWav/4wYOnAI TihRL/4FEwbp28BXMMcoscQCVxlepL4IGDSCyJyVQOu0o7CjmLN50OZlqWmyFy5/6yBBuji0AxFR M00oQAqNIstqI6qKHUsWRAEAvagsmfUEAImyxgbmUpJk3IklNUtJOUAVLoUr1+wqDGTE4zk+T6FG uQb3SizBCwatiiUgCBN8vrz+zFjVyQ8FWkOlg4NQiZMB5QS8QO3mpOaKnL0Z2EKvNMSILETh uQb3SizBCwatiiUgCBN8vrz+KhCg zMKPVxYJh23qm9KNW7pArPynMqZDiErsTMqI+LRi3QAgkFUbXpuFKhSYZALd0O5RKa2z9EYK zMKPVxYJh23qm9KNW7pArPynMqZDiErsTMqI+Bbpb qxIKsjUPRgD7I2XYV6wyrOw92ykExP8NW4URhknC5dKGE4v4NENQj2jXjmfNgOZDaXb5glRmXQ33 YEWQYNcZFnrYcIQLNzyTFDQNkXIff0ExVlY4srziQk43inZgL4rwxxINMvpFFAz1KOODHiu+4aEw NEjFl5B3JIKWKF3k6I9bfUGp5ZZcdunll5IA4cuHvQQJ5gcsoCWOOUwgltIwAKRxJgbIkJAQZEq0 2YliZnpZZ4BH3CnYOXldOUOfQoYDqF1LFHbXCrO8xmRsfoXDXJ6ChjCAH3QlhJcT6VWE6FCkfCco CgrMFsROrIEX3o2whVjWDjoJccN3LdggSGXLCdLEgHr1lyU3O3QxhgohNKXJCWv8JQr/PDdaqd6w 2rj1inLiGeiCJoDspAoQlYE6QWLSECehcWIYxIQES6zhbn1iImTHEQyqJ4eIxJJoUBc+3CbB 2rj1inLiGeiCJoDspAoQlYE6QWLSECehcWIYxIQES6zhbn1iImTHEQyqJ4eIxJJoUBc+uwZE V5cJPPkIjFDdeEabQbd6WgICTxiiz0f5dBKquXF6k4senwEhYGnKEFJeGrxUZy8dB8gmAXI/sPvH ESfCwVt5hTgYiqQqtdRNHQIU1PJ33ZqmzgE90OwLaoJcnMop1WiMmgkPHQRIrwgFuNV90A3doNKT mrKIN07AnGcI9BQjhCBN4RfA1qIZnMqorJCogKfGQnxSCDilTVIA0yl5ciTovgLuBDKFUDE9aQcw 9SA+rjSNf9/M1gxrj6VwDTS0IUSElMzBfsj0NFXR2kwsV1A5IF1grLgLL/r1R40BZEnuBWgm 9SA+QEyb jqRwSAt6bqMCOFkvKFN2GPPkUzIm/SCF8z8pVzpbjVnMsy0vOr1hw3SaSRUhpY09v0z0J1FnwzPl fmh+xl4WtR0zGu24I4KbMQm3lnVu2oNWxI9W/lcyzA+mCKF4DBikxb/+UWtOGRiFP8qEwAay fmh+IgIA Ow==
--1__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: image/gif; name="ecblank.gif" Content-Disposition: inline; filename="ecblank.gif" Content-ID: <120__=88BBE58EDFC689398f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64
R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7
--1__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939--
--0__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: image/gif; name="pic09765.gif" Content-Disposition: attachment; filename="pic09765.gif" Content-ID: <10__=88BBE58EDFC689398f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64
R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7
--0__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: image/gif; name="pic05356.gif" Content-Disposition: attachment; filename="pic05356.gif" Content-ID: <20__=88BBE58EDFC689398f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64
R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7
--0__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: image/gif; name="pic26833.gif" Content-Disposition: attachment; filename="pic26833.gif" Content-ID: <30__=88BBE58EDFC689398f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64
R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7
--0__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: image/gif; name="pic31786.gif" Content-Disposition: attachment; filename="pic31786.gif" Content-ID: <40__=88BBE58EDFC689398f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64
R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7
--0__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: image/gif; name="pic01528.gif" Content-Disposition: attachment; filename="pic01528.gif" Content-ID: <50__=88BBE58EDFC689398f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64
R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7
--0__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: image/gif; name="pic02609.gif" Content-Disposition: attachment; filename="pic02609.gif" Content-ID: <60__=88BBE58EDFC689398f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64
R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7
--0__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: image/gif; name="pic04363.gif" Content-Disposition: attachment; filename="pic04363.gif" Content-ID: <70__=88BBE58EDFC689398f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64
R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7
--0__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: image/gif; name="pic06300.gif" Content-Disposition: attachment; filename="pic06300.gif" Content-ID: <80__=88BBE58EDFC689398f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64
R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7
--0__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939 Content-type: image/gif; name="pic27005.gif" Content-Disposition: attachment; filename="pic27005.gif" Content-ID: <90__=88BBE58EDFC689398f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64
R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7
--0__=88BBE58EDFC689398f9e8a93df938690918c88BBE58EDFC68939--
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|