|
IDS Forum
AW: Sysprocplan being locked from time to time
Posted By: Schuran, Sven Date: Thursday, 30 September 2004, at 11:23 a.m.
In Response To: Re: Sysprocplan being locked from time to time (Rajib Sarkar )
We have such a Proble, too.
I fixed it with an update statistice for procedure. Every night. make sure it runs otherwise you might get further problems.
-----Ursprüngliche Nachricht----- Von: Rajib Sarkar [mailto:rsarkar@us.ibm.com] Gesendet: Dienstag, 28. September 2004 17:54 An: ids@iiug.org Betreff: 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+zFjVyQ8FWkOlg4NQiZMB5QS8QO3mpOaKnL0Z2EKvNMSILEThKhCg zMKPVxYJh23qm9KNW7pArPynMqZDiErsTMqI+LRi3QAgkFUbXpuFKhSYZALd0O5RKa2z9EYKBbpb qxIKsjUPRgD7I2XYV6wyrOw92ykExP8NW4URhknC5dKGE4v4NENQj2jXjmfNgOZDaXb5glRmXQ33 YEWQYNcZFnrYcIQLNzyTFDQNkXIff0ExVlY4srziQk43inZgL4rwxxINMvpFFAz1KOODHiu+4aEw NEjFl5B3JIKWKF3k6I9bfUGp5ZZcdunll5IA4cuHvQQJ5gcsoCWOOUwgltIwAKRxJgbIkJAQZEq0 2YliZnpZZ4BH3CnYOXldOUOfQoYDqF1LFHbXCrO8xmRsfoXDXJ6ChjCAH3QlhJcT6VWE6FCkfCco CgrMFsROrIEX3o2whVjWDjoJccN3LdggSGXLCdLEgHr1lyU3O3QxhgohNKXJCWv8JQr/PDdaqd6w 2rj1inLiGeiCJoDspAoQlYE6QWLSECehcWIYxIQES6zhbn1iImTHEQyqJ4eIxJJoUBc+3CbBuwZE V5cJPPkIjFDdeEabQbd6WgICTxiiz0f5dBKquXF6k4senwEhYGnKEFJeGrxUZy8dB8gmAXI/sPvH ESfCwVt5hTgYiqQqtdRNHQIU1PJ33ZqmzgE90OwLaoJcnMop1WiMmgkPHQRIrwgFuNV90A3doNKT mrKIN07AnGcI9BQjhCBN4RfA1qIZnMqorJCogKfGQnxSCDilTVIA0yl5ciTovgLuBDKFUDE9aQcw 9SA+rjSNf9/M1gxrj6VwDTS0IUSElMzBfsj0NFXR2kwsV1A5IF1grLgLL/r1R40BZEnuBWgmQEyb jqRwSAt6bqMCOFkvKFN2GPPkUzIm/SCF8z8pVzpbjVnMsy0vOr1hw3SaSRUhpY09v0z0J1FnwzPl fmh+xl4WtR0zGu24I4KbMQm3lnVu2oNWxI9W/lcyzA+mCKF4DBikxb/+UWtOGRiFP8qEwAayIgIA 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.
|
|