DB2 - L

  • 1.  we have a NSP with long wait in "SPAS SERVER TCB" ????

    Posted Oct 07, 2021 08:59 AM

    we have a well behaving native stored procedure.

    Occasionally, there is a long wait time with no explanation.
    Trace via Mainview shows bufferpool hit ratio 98-100%
    But occasionally there is many seconds wait time under SPAS Server TCB.

    We are having MVS examine the dispatch priorities for WLM, but MVS says
    'now there are no standards for the dispatching priorities as they are dynamically managed by SRM..'

    We are coming into busy season and this is causing a major problem.

    Does anyone know what SPAS Server TCB delays are and how to correct?

    Any advise would be appreciated
    Thanks
    Bill



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


  • 2.  RE: we have a NSP with long wait in "SPAS SERVER TCB" ????

    Posted Oct 07, 2021 09:11 AM
      |   view attached
    uploaded trace output.....

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

    Attachment(s)

    docx
    Document1.docx   34 KB 1 version


  • 3.  RE: we have a NSP with long wait in "SPAS SERVER TCB" ????

    Posted Oct 07, 2021 02:42 PM
    Native stored procedures does not run in a WLM SPAS - they run within DBM1 address space.
    This must be a traditional Cobol, PL/I, C, or pre-historic SQL routine.

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



  • 4.  RE: we have a NSP with long wait in "SPAS SERVER TCB" ????

    Posted Oct 07, 2021 02:46 PM

    Make sure the NSP is defined with debugging turned off.  This can cause something that would otherwise be "native" to inadvertently run in a WLM address space.

     






  • 5.  RE: we have a NSP with long wait in "SPAS SERVER TCB" ????

    Posted Oct 07, 2021 02:48 PM
    A WLM SPAS can take a while to spin up, especially if the load in your environment is high. I think your MVS folks should have some control over that?! 

    You can consider coding MNSPAS=n in the JCL proc for the WLM SPAS. This keeps n address spaces running all the time, so there should be less waiting for new ones to spin up. 

    Check the manual for a example of using MNSPAS https://www.ibm.com/docs/en/db2-for-zos/12?topic=ms2cdrspudfo-setting-up-wlm-application-environment-stored-procedures-during-migration

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