Professional Web Applications Themes

Monitoring Stored Procedure Usage - IBM DB2

Hi I want to see how often individual stored procedures are being called. Does anybody have any ideas how I could achieve this? (Running 7.2 on Solaris) Thanks Tim...

  1. #1

    Default Monitoring Stored Procedure Usage

    Hi
    I want to see how often individual stored procedures are being called.
    Does anybody have any ideas how I could achieve this? (Running 7.2 on
    Solaris)
    Thanks
    Tim
    Tim Jacobs Guest

  2. #2

    Default Re: Monitoring Stored Procedure Usage

    The first thing I can think of would be a Statement Event Monitor.
    --
    Larry Menard
    IBM Workstation Database (DB2) Performance Team
    Defender of Geese and of All Things Natural


    "Tim Jacobs" <tim.jacobst-systems.co.uk> wrote in message
    news:62f6e8bf.0307010548.36b7d4b4posting.google.c om...
    > Hi
    > I want to see how often individual stored procedures are being called.
    > Does anybody have any ideas how I could achieve this? (Running 7.2 on
    > Solaris)
    > Thanks
    > Tim

    Larry Menard Guest

  3. #3

    Default Re: Monitoring Stored Procedure Usage

    Having done (or at least valiantly attempted to do) this several
    times, there are a couple things you need to know about. The statement
    monitor will provide you access to all of the SQL calls going into the
    database. If you're using SQL stored procedures you will not see the
    stored procedure name, but rather the package associated with that
    name in the event monitor output. You'll have to massage the output
    and correlate the pkgnames with the actual SP name to get an idea of
    what's being called when.

    If you're using Java stored procs as we are, you may be out of luck.
    (If someone knows any different, please speak up!) Since there is no
    package associated with a Java stored proc, the only thing that you'll
    see in the event monitor output is the raw SQL statements executed by
    the Java SPs. You'll manually have to correlate those SQL statements
    back to the Java source code to get an idea of executions of the
    stored procs. So far this has proved to be pretty tedious. If your SQL
    is unique and readily identifiable and only called from one SP, then
    this might not be so bad.

    Hope this helps!

    Evan


    "Larry Menard" <lmenardca.ibm.com> wrote in message news:<bds6jt$99c$1hanover.torolab.ibm.com>...
    > The first thing I can think of would be a Statement Event Monitor.
    > --
    > Larry Menard
    > IBM Workstation Database (DB2) Performance Team
    > Defender of Geese and of All Things Natural
    >
    >
    > "Tim Jacobs" <tim.jacobst-systems.co.uk> wrote in message
    > news:62f6e8bf.0307010548.36b7d4b4posting.google.c om...
    > > Hi
    > > I want to see how often individual stored procedures are being called.
    > > Does anybody have any ideas how I could achieve this? (Running 7.2 on
    > > Solaris)
    > > Thanks
    > > Tim
    Evan Smith Guest

  4. #4

    Default Re: Monitoring Stored Procedure Usage

    Thanks guys, that's useful.
    Tim
    Tim Jacobs Guest

  5. #5

    Default Re: Monitoring Stored Procedure Usage

    Not helpful on v7...but for anyone interested in v8, call is a compiled
    statement, so with the statement evmon you'll actually get the call
    statement (complete with proc name) in the evmon output.

    Tim Jacobs wrote:
    > Hi
    > I want to see how often individual stored procedures are being called.
    > Does anybody have any ideas how I could achieve this? (Running 7.2 on
    > Solaris)
    > Thanks
    > Tim
    Sean McKeough Guest

  6. #6

    Default Re: Monitoring Stored Procedure Usage

    Sean McKeough <mckeoughnospam.ca.ibm.com> wrote in message news:<bdump5$5uk$2hanover.torolab.ibm.com>...
    > Not helpful on v7...but for anyone interested in v8, call is a compiled
    > statement, so with the statement evmon you'll actually get the call
    > statement (complete with proc name) in the evmon output.
    Even on Java SPs?
    Evan Smith Guest

  7. #7

    Default Re: Monitoring Stored Procedure Usage

    Yep...in v7 call wasn't compled, so the call sql was never recorded by
    the compiler, but now in the dynamic sql case it should be there...

    Evan Smith wrote:
    > Sean McKeough <mckeoughnospam.ca.ibm.com> wrote in message news:<bdump5$5uk$2hanover.torolab.ibm.com>...
    >
    >>Not helpful on v7...but for anyone interested in v8, call is a compiled
    >>statement, so with the statement evmon you'll actually get the call
    >>statement (complete with proc name) in the evmon output.
    >
    >
    > Even on Java SPs?
    Sean McKeough Guest

Similar Threads

  1. Stored Procedure
    By Aaron Bertrand - MVP in forum ASP.NET
    Replies: 13
    Last Post: July 5th, 05:43 AM
  2. MS SQL stored procedure
    By lfsxdth in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 23rd, 02:14 PM
  3. Share Usage Monitoring
    By msnews.microsoft.com in forum Windows Server
    Replies: 0
    Last Post: June 16th, 02:54 PM
  4. Stored procedure from stored procedure
    By Red Valsen in forum Informix
    Replies: 3
    Last Post: October 2nd, 02:22 PM
  5. need help on a stored procedure
    By Helixpoint in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 01:26 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139