DB2 - L

 View Only

  • 1.  Native SQL PL calls another Native SQL PL - ASUTIME exceeded

    Posted Oct 11, 2022 01:45 PM
    We have a Native SQL PL procedure (we'll call it SP4) that calls another Native SQL PL procedure (SP5).  SP5 can perform CONNECT to Remote DB2s as needed to access data there.  Occasionally the call to SP4 results in -905 with a message like:

    UNSUCCESSFUL EXECUTION DUE TO RESOURCE LIMIT BEING EXCEEDED, RESOURCE NAME = ASUTIME LIMIT = 000000000192 CPU SECONDS (000018000000 SERVICE UNITS) DERIVED FROM SYSIBM.DSNRLST01 Error in subsystem: DBnn.ppp *Error Calling SP5 DB2_USER_ID=user_id DB_APPL_ID=db2jcc_application< (certain values masked for anonymity)

    When this happens the procedure (SP4) has only been executing for a second or two.  So it seems highly unlikely it used up 192 CPU seconds.

    Also when we get -905 the DBnn in the error message is always to the local DB2 where the call originated.

    Any suggestions other than setting these to ASUTIME NO LIMIT?

    ------------------------------
    StefanKolevSS&C
    ------------------------------


  • 2.  RE: Native SQL PL calls another Native SQL PL - ASUTIME exceeded

    Posted Oct 12, 2022 02:39 AM
    The ASU limit (   ASUTIME LIMIT = 000000000192 )  you have is a very small number and certainly not cpu seconds

    Please start by looking at the db2 reference manual


    Thanks,
    Peter Schwarcz
    9 Ridge Road, Fairhaven, Vic 3231
    pH 0418363938

    Prediction is very difficult, especially if it's about the future! 



    Original Message


  • 3.  RE: Native SQL PL calls another Native SQL PL - ASUTIME exceeded

    Posted Oct 12, 2022 08:55 AM
    Peter, if you look at the error message you will notice the ASUTIME LIMIT is set to 18000000 service units.

    ------------------------------
    StefanKolevSS&C
    ------------------------------

    Original Message


  • 4.  RE: Native SQL PL calls another Native SQL PL - ASUTIME exceeded

    Posted Oct 12, 2022 09:03 AM
    Silly question, but could it be that the Native SQL Procedure has a "hard coded" ASUTIME limit on the remote site? Perhaps that is then returned to the local Db2 incorrectly...Just a stab in the dark...

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-



    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich


    Original Message


  • 5.  RE: Native SQL PL calls another Native SQL PL - ASUTIME exceeded

    Posted Oct 12, 2022 09:15 AM
    Yes, Roy, the value on the remote sites is indeed ASUTIME LIMIT = 000000000017 CPU SECONDS (000001666667 SERVICE UNITS), the same as it used to be on the local site before I altered it.  I will explore that possibility, thanks for the suggestion!

    ------------------------------
    StefanKolevSS&C
    ------------------------------

    Original Message


  • 6.  RE: Native SQL PL calls another Native SQL PL - ASUTIME exceeded

    Posted Oct 12, 2022 11:15 AM
    Look at the SRUs .. 18 million units - not that big of a number it seems to me  - if it happens consistently within each member of the data sharing group, then it's related to the query and/or missing stats/unsupported index etc - if it occurs sporadically in certain members only then possibly those Lpar(s) hv less CPU capacity than the rest (where you may not hv issues)..

    always start with looking at tuning potentials with the involved query - these must be zIIP eligible I believe!

    ------------------------------
    VASUDEVANNATARAJANwalmart
    ------------------------------

    Original Message


  • 7.  RE: Native SQL PL calls another Native SQL PL - ASUTIME exceeded

    Posted Nov 21, 2022 01:13 PM
    Stefan,

    Any update on this? (I'm just curious.)  Do you have an explanation?
    Was Roy's "stab in the dark" indeed the explanation?

    ------------------------------
    Peter Vanroose
    ABIS Training & Consulting
    https://www.abis.be/html/enDB2Calendar.html
    ------------------------------

    Original Message