IDS Forum

RE: Sysprocplan being locked from time to time

Posted By: John Adamski
Date: Monday, 27 September 2004, at 3:53 p.m.

In Response To: RE: Sysprocplan being locked from time to time (Schmitz, Rob B [Contractor for Sprint])

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