Db2 for z/OS & Db2ZAI

Db2 for z/OS and its ecosystem

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  DSN_PROFILE_TABLE behavior

    Posted yesterday

    Hello,

    I have a question about the expected behavior of one the exceptions within the DSN_PROFILE_TABLE.  I am testing the MONITOR_THREADS activity for a particular ID.  I set the MONITOR_THREADS on for this ID, in the attributes, I set EXCEPTION_DIAGLEVEL3 and a threshold of one thread.  

    According to the Profile Table, it was accepted (I did issue a refresh of the table).  The first ID went through without issue, the second attempt did issue the message, but did not terminate the threads, instead, it cycled until a slot became available.  I would like it to terminate the attempt immediately.  

    Is there a way this can happen?



    ------------------------------
    George Meadows
    ------------------------------


  • 2.  RE: DSN_PROFILE_TABLE behavior

    Posted 13 hours ago
    Edited by Paul Bartak 13 hours ago

    George,

    Sounds like it is working. I created this picture (it's better when presenting it with some background) to help understand the MONITOR KEYWORDS. The filter used makes a difference but, in any case, this causes threads to suspend (the yellow area in the picture). The graphic isn't perfect, but feedback over the years has been that it was helpful.

    Regards, Paul



    ------------------------------
    Paul Bartak
    Distinguished Engineer
    Rocket Software
    St. Louis
    ------------------------------

    Original Message


  • 3.  RE: DSN_PROFILE_TABLE behavior

    Posted 4 hours ago

    That's really useful info Paul, as they say a picture paints a 1,000 words ...

     

    Cheers

    Bruce

    --

     




    Original Message


  • 4.  RE: DSN_PROFILE_TABLE behavior

    Posted 12 hours ago

    Hello George,

    Yes, you can do what you want.  The design is for thread requests in excess of the value you place in Attribute 2 to queue (or suspend) until a thread becomes available, as you observed.  The number of requests that will queue in this way is equal to the value of Attribute 2.  (If you had started a third, it should have terminated immediately.)

    To prevent requests from queuing (suspending) you specify a value in Attribute 3. This indicates the number of requests that will queue if they exceed Attribute 2.  In your desired case, set Attribute 3 to zero, and no requests will queue.



    ------------------------------
    Mark Rader
    ------------------------------

    Original Message


  • 5.  RE: DSN_PROFILE_TABLE behavior

    Posted 12 hours ago

    Hi George,

    It depends on your setup, but you should be able to use ATTRIBUTE3 in the DSN_PROFILE_ATTRIBUTES table to control how many threads are queued.  If you set it to NULL, then none should queue.

    I like to reference the examples in the manual from this page: https://www.ibm.com/docs/en/db2-for-zos/12.0.0?topic=dbats-monitoring-threads-by-using-profile-tables

    Quoted:   

    In this case, you might complete the following steps:

    1. Insert the following DSN_PROFILE_TABLE rows.
      PROFILEID
      LOCATION
      ROLE
      AUTHID
      PRDID
      COLLID
      PKGNAME
      1 ::FFFF:9.30.137.28 null null null null null
      2 ::0 null null null null null
    2. Insert the following DSN_PROFILE_ATTRIBUTES rows.
      PROFILEID
      KEYWORDS
      ATTRIBUTE1
      ATTRIBUTE2
      ATTRIBUTE3
      1 MONITOR THREADS EXCEPTION 50 null
      2 MONITOR ALL THREADS EXCEPTION 200 null
      2 MONITOR ALL CONNECTIONS EXCEPTION 1000 null
    3. Issue a -START PROFILE command.

    Creating these example profiles has the following results:

    • PROFILEID=1 limits applications running from IP address ::FFFFF:9.30.137.28 to a total of 50 threads. Since ATTRIBUTE3 for this profile is the value NULL, additional connections beyond the exception threshold of 50 are suspended. When more than 50 connections become suspended, an exception is triggered.


    ------------------------------
    Michelle Hougrand
    ------------------------------

    Original Message


  • 6.  RE: DSN_PROFILE_TABLE behavior

    Posted 7 hours ago
    Hi, when using MONITOR THREADS, the profile exception would not be hit until a third thread attempts to execute a transaction while one thread is in use and "by default" another thread is queued waiting to start.

              


                                                                      

    Jim Pickel, STSM

    Db2 for z/OS Development

    Silicon Valley Laboratory





    Original Message


Related Content