DB2 - L

  • 1.  SYSSTAT having many lock waits

    Posted Oct 06, 2021 12:41 PM
    we have a program calling NULLID.SYSSTAT ( DB2 Connect package used by DB2 Connect's Call Level Interface (CLI)) which appears to be taking much time and lock waits.
    What might cause that?
    thanks
    Bill


    ------------------------------
    williamgiannelliMe
    ------------------------------


  • 2.  RE: SYSSTAT having many lock waits

    Posted Oct 06, 2021 04:02 PM
    Hi Bill,

    SYSSTAT is used by distributed apps to call a stored procedure.
    So you likely have a stored procedure taking too much time and waiting for locks.

    ------------------------------
    Jørn Thyssen
    Rocket Software
    2021 IBM Champion
    ------------------------------



  • 3.  RE: SYSSTAT having many lock waits

    Posted Oct 06, 2021 04:09 PM
    so it wouldnt be sysstat it's self?

    ------------------------------
    williamgiannelliMe
    ------------------------------



  • 4.  RE: SYSSTAT having many lock waits

    Posted Oct 07, 2021 03:17 AM
    No, that's very unlikely. SYSSTAT only contains statements like CALL :H.
    You'll have to find out which stored procedure is being called, and why it is taking a long time.

    In Db2 Query Monitor you can activate the "stored procedure" optional key for your distributed workload, which allows QM to summarize by stored procedure. You can also enable a static threshold for elapsed time, to capture any SQL statements that are long-running.

    ------------------------------
    Jørn Thyssen
    Rocket Software
    2021 IBM Champion
    ------------------------------



  • 5.  RE: SYSSTAT having many lock waits

    Posted Oct 11, 2021 04:38 PM
    Hi Jorn,
    Is the Db2 Query Monitor stored procedure optional key a licensed add on? Or does it come with QM?
    thanks
    Bill

    ------------------------------
    williamgiannelliMe
    ------------------------------



  • 6.  RE: SYSSTAT having many lock waits

    Posted Oct 12, 2021 02:30 AM
      |   view attached
    Hi Bill,

    It's part of QM (no additional licensing required).

    Go to QM option 8 (work with profiles); use 'u' to update the profile; and 'u' to update the relevant workload monitoring profile.
    Scroll to the bottom and enable OPTKEYS(SP) (see attached screenshot).
    Note 1: that only stored procedures issuing SQL will be captured (a pure REXX or Cobol procedure will not be captured)
    Note 2: more data will now be collected. If you only need OPTKEYS(SP) for a portion of your workload you can create multiple workload lines, each with different OPTKEYS.

    After changing the profile, go to QM option 6 and refresh the SSID(s) to pick up the new profile.

    ------------------------------
    Jørn Thyssen
    Rocket Software
    2021 IBM Champion
    ------------------------------



  • 7.  RE: SYSSTAT having many lock waits

    Posted Oct 12, 2021 09:26 AM
    turning on this option, are there extra overhead costs?
    thanks
    Bill

    ------------------------------
    williamgiannelliMe
    ------------------------------