Sysprocplan being locked from time to time

Posted in 2004

Summary

A DBA on IDS 7.31.UD7 saw intermittent locking of the sysprocplan table despite nightly "update statistics for procedure" runs, having to kill the lock-holding session and rerun update stats each time. Replies explained that a procedure reoptimizes (locking sysprocplan) whenever a referenced table's 'created' timestamp in systables is newer than the procedure's last update statistics, so update statistics on a table should be followed by update statistics for every procedure using it; heavy procedure use then causes lock queues. IBM support cited bugs 161680 (trigger-driven procedure holding sysprocplan locks, fixed in 7.31.UD8) and 165202 (deadlock when update statistics for procedure runs concurrently with executing it, fixed in 9.40.UC5), and advised opening a support case. Another user reported the same unresolved problem with Cognos Impromptu. No confirmed fix for the original poster is recorded.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

jpierrot@chubb.com — Fri, 24 Sep 2004 15:39:19 — source: IIUG Forums & Mailing Lists

To all, From time to time , the sysprocplan table is being locked even though that "update statistics for procedure " were run or are run daily through cron. Note - No one is altering or modifying any objects in the database or had done those two either prior to its reoccurrence and it is also recurring sporadically. Each time it happens, I have to kill the session which owns the lock and then re-run update stats for procedure to clear out things. Any suggestions or thoughts relatively on known issues with stored procedure will be greatly appreciated. Thanks! Josue'

↪ replying to jpierrot@chubb.com

Rajib Sarkar — Mon, 27 Sep 2004 15:17:57 — source: IIUG Forums & Mailing Lists

--0__=88BBE58FDFC1F56F8f9e8a93df938690918c88BBE58FDFC1F56F Content-type: multipart/alternative; Boundary="1__=88BBE58FDFC1F56F8f9e8a93df938690918c88BBE58FDFC1F56F" --1__=88BBE58FDFC1F56F8f9e8a93df938690918c88BBE58FDFC1F56F Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable 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 = jpierrot@chubb.co = m = Sent by: = To forum.subscriber@ ids@iiug.org = iiug.org = cc = Subj= ect 09/24/2004 08:39 Sysprocplan being locked from ti= me AM to time [3476] = = = = = = = 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' = --1__=88BBE58FDFC1F56F8f9e8a93df938690918c88BBE58FDFC1F56F Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> 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 as= tound ourselves. -- T. Edison <img src=3D"cid:10__=3D88BBE58FDFC1F56F8f9e8a93df938@us.ibm.com" width=3D= "16" height=3D"16" alt=3D"Inactive hide details for jpierrot@chubb.com"= >jpierrot@chubb.com <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D88BBE58= FDFC1F56F8f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wid= th=3D"40%"> <ul> <ul> <ul> <ul>jpierrot@chubb.com = Sent by: forum.subscriber@iiug.org 09/24/2004 08:39 AM</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:3= 0__=3D88BBE58FDFC1F56F8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""> <div align=3D"right">To</div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D88BBE58FDFC1F56F8f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""> ids@iiug.org</td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D88BBE58FDFC1F56F8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""> <div align=3D"right">cc</div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D88BBE58FDFC1F56F8f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""> </td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D88BBE58FDFC1F56F8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""> <div align=3D"right">Subject</div></td><td widt= h=3D"100%"><img src=3D"cid:30__=3D88BBE58FDFC1F56F8f9e8a93df938@us.ibm.= com" border=3D"0" height=3D"1" width=3D"1" alt=3D""> Sysprocplan being locked from time to time [3476]</fon= t></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:30__=3D88BBE58FDFC1= F56F8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt= =3D""></td><td width=3D"336"><img src=3D"cid:30__=3D88BBE58FDFC1F56F8f9= e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><= /td></tr> </table> </td></tr> </table> <tt>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 cron. 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 = things. Any suggestions or  thoughts relatively on known issues with store= d procedure will be greatly appreciated. Thanks! Josue' </tt> </body></html>= --1__=88BBE58FDFC1F56F8f9e8a93df938690918c88BBE58FDFC1F56F-- --0__=88BBE58FDFC1F56F8f9e8a93df938690918c88BBE58FDFC1F56F Content-type: image/gif; name="graycol.gif" Content-Disposition: inline; filename="graycol.gif" Content-ID: <10__=88BBE58FDFC1F56F8f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7 --0__=88BBE58FDFC1F56F8f9e8a93df938690918c88BBE58FDFC1F56F Content-type: image/gif; name="pic17972.gif" Content-Disp

↪ replying to jpierrot@chubb.com

jpierrot@chubb.com — Mon, 27 Sep 2004 15:50:53 — source: IIUG Forums & Mailing Lists

--0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: text/plain; charset=us-ascii IDS 7.31.UD7 and I can't really say what triggered it . I wish I knew so I can better address this issue. Also, if this turns out to be a bug or one of them. Is it resolved in 9.4. Please advise! 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 Subject Re: Sysprocplan being locked from 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 existing 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: pic24232.gif)jpierrot@chubb.com jpierrot@chubb. com Sent by: forum.subscribe (Embedded image moved to file: r@iiug.org pic05765.gif) To (Embedded image moved to 09/24/2004 file: pic18618.gif) 08:39 AM ids@iiug.org (Embedded image moved to file: pic31720.gif) cc (Embedded image moved to file: pic16237.gif) (Embedded image moved to file: pic28264.gif) Subject (Embedded image moved to file: pic00709.gif) Sysprocplan being locked from time to time [3476] (Embedded image moved to file: pic30703.gif) (Embedded image moved to file: pic32544.gif) To all, From time to time , the sysprocplan table is being locked even though that "update statistics for procedure " were run or are run daily through cron. Note - No one is altering or modifying any objects in the database or had done those two either prior to its reoccurrence and it is also recurring sporadically. Each time it happens, I have to kill the session which owns the lock and then re-run update stats for procedure to clear out things. Any suggestions or thoughts relatively on known issues with stored procedure will be greatly appreciated. Thanks! Josue' --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic24232.gif" Content-Disposition: attachment; filename="pic24232.gif" Content-transfer-encoding: base64 R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic05765.gif" Content-Disposition: attachment; filename="pic05765.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic18618.gif" Content-Disposition: attachment; filename="pic18618.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic31720.gif" Content-Disposition: attachment; filename="pic31720.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic16237.gif" Content-Disposition: attachment; filename="pic16237.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic28264.gif" Content-Disposition: attachment; filename="pic28264.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic00709.gif" Content-Disposition: attachment; filename="pic00709.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic30703.gif" Content-Disposition: attachment; filename="pic30703.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic32544.gif" Content-Disposition: attachment; filename="pic32544.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613--

↪ replying to jpierrot@chubb.com

Schmitz, Rob B — Mon, 27 Sep 2004 17:41:17 — source: IIUG Forums & Mailing Lists

Josue' We came across a similar issue several years ago. If the "created" timestamp for a table involved in a stored procedure is later than the last time "update statistics" was run for that procedure, the stored procedure will reoptimize. Regardless of what is written, it will reoptimize every time it is run until "update statistics" is run for the procedure. When it reoptimizes it locks the sysprocplan table. My thoughts are: if it is going to lock sysprocplan, why not reoptimize the first time and be done with it. If it is not going to update the sysprocplan table with the reoptimizing results, then don't lock it. (Just my own view on the matter.) In our experience, the stored procedure was heavily used. The reoptimization did not take very long but it took long enough for a queue to begin waiting to get a lock on the sysprocplan table. One important note: Running "update statistics" on a table will change the created column in systables. Therefore, whenever you run "update statistics" on a table, be sure to run "update statistics" on every stored procedure that involves that table. Rob Schmitz -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of jpierrot@chubb.com Sent: Monday, September 27, 2004 10:51 AM To: ids@iiug.org Subject: Re: Sysprocplan being locked from time to time [3488] --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: text/plain; charset=us-ascii IDS 7.31.UD7 and I can't really say what triggered it . I wish I knew so I can better address this issue. Also, if this turns out to be a bug or one of them. Is it resolved in 9.4. Please advise! 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 Subject Re: Sysprocplan being locked from 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 existing 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: pic24232.gif)jpierrot@chubb.com jpierrot@chubb. com Sent by: forum.subscribe (Embedded image moved to file: r@iiug.org pic05765.gif) To (Embedded image moved to 09/24/2004 file: pic18618.gif) 08:39 AM ids@iiug.org (Embedded image moved to file: pic31720.gif) cc (Embedded image moved to file: pic16237.gif) (Embedded image moved to file: pic28264.gif) Subject (Embedded image moved to file: pic00709.gif) Sysprocplan being locked from time to time [3476] (Embedded image moved to file: pic30703.gif) (Embedded image moved to file: pic32544.gif) To all, From time to time , the sysprocplan table is being locked even though that "update statistics for procedure " were run or are run daily through cron. Note - No one is altering or modifying any objects in the database or had done those two either prior to its reoccurrence and it is also recurring sporadically. Each time it happens, I have to kill the session which owns the lock and then re-run update stats for procedure to clear out things. Any suggestions or thoughts relatively on known issues with stored procedure will be greatly appreciated. Thanks! Josue' --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic24232.gif" Content-Disposition: attachment; filename="pic24232.gif" Content-transfer-encoding: base64 R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic05765.gif" Content-Disposition: attachment; filename="pic05765.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic18618.gif" Content-Disposition: attachment; filename="pic18618.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic31720.gif" Content-Disposition: attachment; filename="pic31720.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic16237.gif" Content-Disposition: attachment; filename="pic16237.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic28264.gif" Content-Disposition: attachment; filename="pic28264.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic00709.gif" Content-Disposition: attachment; filename="pic00709.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic30703.gif" Content-Disposition: attachment; filename="pic30703.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic32544.gif" Content-Disposition: attachment; filename="pic32544.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613--

↪ replying to Schmitz, Rob B

John Adamski — Mon, 27 Sep 2004 19:53:06 — source: IIUG Forums & Mailing Lists

We have been having this issue of the sysprocplan being locked for over 2 years. We run IDS 7.31.UD6W6 on HPUX. We tend to get this when running Cognos Impromptu report using a view that has a proc in it. We have turned this issue into Support and even had the developers look at this and could not find the cause. We have switched to SDK 2.81 TC2 for the PCs that runs Impromptu and that seems to have lessen how often we get these locks but have not eliminated. If anyone know the cause and how to avoid I would like to know. John -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Schmitz, Ro.... Sent: Monday, September 27, 2004 12:41 PM To: ids@iiug.org Subject: RE: Sysprocplan being locked from time to time [3489] Josue' We came across a similar issue several years ago. If the "created" timestamp for a table involved in a stored procedure is later than the last time "update statistics" was run for that procedure, the stored procedure will reoptimize. Regardless of what is written, it will reoptimize every time it is run until "update statistics" is run for the procedure. When it reoptimizes it locks the sysprocplan table. My thoughts are: if it is going to lock sysprocplan, why not reoptimize the first time and be done with it. If it is not going to update the sysprocplan table with the reoptimizing results, then don't lock it. (Just my own view on the matter.) In our experience, the stored procedure was heavily used. The reoptimization did not take very long but it took long enough for a queue to begin waiting to get a lock on the sysprocplan table. One important note: Running "update statistics" on a table will change the created column in systables. Therefore, whenever you run "update statistics" on a table, be sure to run "update statistics" on every stored procedure that involves that table. Rob Schmitz -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of jpierrot@chubb.com Sent: Monday, September 27, 2004 10:51 AM To: ids@iiug.org Subject: Re: Sysprocplan being locked from time to time [3488] --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: text/plain; charset=us-ascii IDS 7.31.UD7 and I can't really say what triggered it . I wish I knew so I can better address this issue. Also, if this turns out to be a bug or one of them. Is it resolved in 9.4. Please advise! 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 Subject Re: Sysprocplan being locked from 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 existing 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: pic24232.gif)jpierrot@chubb.com jpierrot@chubb. com Sent by: forum.subscribe (Embedded image moved to file: r@iiug.org pic05765.gif) To (Embedded image moved to 09/24/2004 file: pic18618.gif) 08:39 AM ids@iiug.org (Embedded image moved to file: pic31720.gif) cc (Embedded image moved to file: pic16237.gif) (Embedded image moved to file: pic28264.gif) Subject (Embedded image moved to file: pic00709.gif) Sysprocplan being locked from time to time [3476] (Embedded image moved to file: pic30703.gif) (Embedded image moved to file: pic32544.gif) To all, From time to time , the sysprocplan table is being locked even though that "update statistics for procedure " were run or are run daily through cron. Note - No one is altering or modifying any objects in the database or had done those two either prior to its reoccurrence and it is also recurring sporadically. Each time it happens, I have to kill the session which owns the lock and then re-run update stats for procedure to clear out things. Any suggestions or thoughts relatively on known issues with stored procedure will be greatly appreciated. Thanks! Josue' --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic24232.gif" Content-Disposition: attachment; filename="pic24232.gif" Content-transfer-encoding: base64 R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0Popwx Ubpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic05765.gif" Content-Disposition: attachment; filename="pic05765.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic18618.gif" Content-Disposition: attachment; filename="pic18618.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic31720.gif" Content-Disposition: attachment; filename="pic31720.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic16237.gif" Content-Disposition: attachment; filename="pic16237.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic28264.gif" Content-Disposition: attachment; filename="pic28264.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic00709.gif" Content-Disposition: attachment; filename="pic00709.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: image/gif; name="pic30703.gif" Content-Disposition: attachment; filename="pic30703.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58FDFC736138f9e8a93df938690918c0ABBE58FDFC73613 Content-type: ima

↪ replying to jpierrot@chubb.com

jpierrot@chubb.com — Tue, 28 Sep 2004 15:02:27 — source: IIUG Forums & Mailing Lists

--0__=0ABBE58EDFC27E0F8f9e8a93df938690918c0ABBE58EDFC27E0F Content-type: text/plain; charset=us-ascii 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. Does 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 Subject Re: Sysprocplan being locked from 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 existing 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) Subject (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 that "update statistics for procedure " were run or are run daily through cron. Note - No one is altering or modifying any objects in the database or had done those two either prior to its reoccurrence and it is also recurring sporadically. Each time it happens, I have to kill the session which owns the lock and then re-run update stats for procedure to clear out things. Any suggestions or thoughts relatively on known issues with stored procedure will be greatly appreciated. Thanks! Josue' --0__=0ABBE58EDFC27E0F8f9e8a93df938690918c0ABBE58EDFC27E0F Content-type: image/gif; name="pic09765.gif" Content-Disposition: attachment; filename="pic09765.gif" Content-transfer-encoding: base64 R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7 --0__=0ABBE58EDFC27E0F8f9e8a93df938690918c0ABBE58EDFC27E0F Content-type: image/gif; name="pic05356.gif" Content-Disposition: attachment; filename="pic05356.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58EDFC27E0F8f9e8a93df938690918c0ABBE58EDFC27E0F Content-type: image/gif; name="pic26833.gif" Content-Disposition: attachment; filename="pic26833.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58EDFC27E0F8f9e8a93df938690918c0ABBE58EDFC27E0F Content-type: image/gif; name="pic31786.gif" Content-Disposition: attachment; filename="pic31786.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58EDFC27E0F8f9e8a93df938690918c0ABBE58EDFC27E0F Content-type: image/gif; name="pic01528.gif" Content-Disposition: attachment; filename="pic01528.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58EDFC27E0F8f9e8a93df938690918c0ABBE58EDFC27E0F Content-type: image/gif; name="pic02609.gif" Content-Disposition: attachment; filename="pic02609.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58EDFC27E0F8f9e8a93df938690918c0ABBE58EDFC27E0F Content-type: image/gif; name="pic04363.gif" Content-Disposition: attachment; filename="pic04363.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58EDFC27E0F8f9e8a93df938690918c0ABBE58EDFC27E0F Content-type: image/gif; name="pic06300.gif" Content-Disposition: attachment; filename="pic06300.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58EDFC27E0F8f9e8a93df938690918c0ABBE58EDFC27E0F Content-type: image/gif; name="pic27005.gif" Content-Disposition: attachment; filename="pic27005.gif" Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=0ABBE58EDFC27E0F8f9e8a93df938690918c0ABBE58EDFC27E0F--

↪ replying to jpierrot@chubb.com

Rajib Sarkar — Tue, 28 Sep 2004 15:54:22 — source: IIUG Forums & Mailing Lists

--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> 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> 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 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 as= tound ourselves. -- T. Edison <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 <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>jpierrot@chubb.com = 09/28/2004 07:58 AM</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""> <div align=3D"right">To</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""> Rajib Sarkar/Phoenix/IBM@IBMUS</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

↪ replying to Rajib Sarkar

Vivek CHAUDHARY — Tue, 28 Sep 2004 18:24:36 — source: IIUG Forums & Mailing Lists

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> 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> 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 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 as= tound ourselves. -- T. Edison <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 <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>jpierrot@chubb.com = 09/28/2004 07:58 AM</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"

↪ replying to Rajib Sarkar

Schuran, Sven — Thu, 30 Sep 2004 15:23:58 — source: IIUG Forums & Mailing Lists

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> 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> 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 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 as= tound ourselves. -- T. Edison <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 <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>jpierrot@chubb.com = 09/28/2004 07:58 AM</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""> <div align=3D"right">To</div></td><td width=3D"= 100%"><img src=3D"cid:120__=3D88BBE58EDFC689

Glossary

UPDATE STATISTICS