IDS Forum

RE: Sysprocplan being locked from time to time

Posted By: Schmitz, Rob B [Contractor for Sprint]
Date: Monday, 27 September 2004, at 1:41 p.m.

In Response To: Re: Sysprocplan being locked from time to time (jpierrot@chubb.com)

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--

Messages In This Thread

• Sysprocplan being locked from time to time
  jpierrot@chubb.com -- Friday, 24 September 2004, at 11:39 a.m.
  • Re: Sysprocplan being locked from time to time
    Rajib Sarkar -- Monday, 27 September 2004, at 11:17 a.m.
  • Re: Sysprocplan being locked from time to time
    jpierrot@chubb.com -- Monday, 27 September 2004, at 11:50 a.m.
    • RE: Sysprocplan being locked from time to time
      Schmitz, Rob B [Contractor for Sprint] -- Monday, 27 September 2004, at 1:41 p.m.
      • RE: Sysprocplan being locked from time to time
        John Adamski -- Monday, 27 September 2004, at 3:53 p.m.
  • Re: Sysprocplan being locked from time to time
    jpierrot@chubb.com -- Tuesday, 28 September 2004, at 11:02 a.m.
  • Re: Sysprocplan being locked from time to time
    Rajib Sarkar -- Tuesday, 28 September 2004, at 11:54 a.m.
    • RE: Sysprocplan being locked from time to time
      Vivek CHAUDHARY -- Tuesday, 28 September 2004, at 2:24 p.m.
    • AW: Sysprocplan being locked from time to time
      Schuran, Sven -- Thursday, 30 September 2004, at 11:23 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.