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 Mon February 09, 2026 09:26 AM

    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 Tue February 10, 2026 09:06 AM
    Edited by Paul Bartak Tue February 10, 2026 09:06 AM

    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 Tue February 10, 2026 06:08 PM

    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 Tue February 10, 2026 09:48 AM

    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 Tue February 10, 2026 09:48 AM

    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 Wed February 11, 2026 07:44 AM
      |   view attached

     DB2 Thread Profile Manager

    1. Profile Builder UI - Complete form for DSN_PROFILE_TABLE with all required fields (PROFILEID, LOCATION, ROLE, AUTHID, PRDID, COLLID, PKGNAME)

    2. ATTRIBUTE3 Thread Limiter - Core feature with:

      • Number input with min/max validation (0-9999)
      • NULL toggle button that switches between queue limit and "No Queueing" mode
      • Visual feedback: red border and badge when NULL is enabled
      • Tooltip explaining the behavior in each state
      • Auto-filled and locked ATTRIBUTE1 = "EXCEPTION"

    3. SQL Snippet Generator - Real-time generation of:

      • DSN_PROFILE_TABLE INSERT statements
      • DSN_PROFILE_ATTRIBUTES INSERT statements
      • Copy-to-clipboard buttons with success feedback
      • Validation errors displayed in red warning banners

    Design Highlights:

    • Dark mode with slate-900 background
    • Color-coded sections (blue for profile, emerald for attributes, purple for SQL)
    • Smooth transitions and hover effects
    • Responsive layout (stacks on mobile, side-by-side on desktop)
    • Sticky SQL output panel on desktop for easy reference

    The app pre-fills with the IBM example (PROFILEID=1, IPv6 location, ATTRIBUTE3=50) and users can toggle between values and NULL to see the SQL update in real-time.



    ------------------------------
    Suman Suhag
    Dev Bhoomi Uttarakhand University
    Quantum Computing & ML Explorer | Mentor at Groundbreaker | Community Builder
    ------------------------------

    Attachment(s)

    zip
    DB2 Thread Profile Manager.zip   23 KB 1 version
    Original Message


  • 7.  RE: DSN_PROFILE_TABLE behavior

    Posted Tue February 10, 2026 02:59 PM
    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


  • 8.  RE: DSN_PROFILE_TABLE behavior

    Posted Wed February 11, 2026 11:26 AM

    Hello George,

    All the answers or replies to your question are correct. For your reference, when using a MONITOR THREADS profile with any kind of filtering criteria if you want the next thread to terminate immediate when the exception threshold (ATTRIBUTE2 value) is exceeded, simply set the ATTRIBUTE3 value to 0. So in your example, with ATTRIBUTE2=1 and if you specify ATTRIBUTE3=0 then first connection matching the ID from the MONITOR THREADS profile would be accepted without issue but the second connection with the same ID would be terminated immediately. Hope this helps.



    ------------------------------
    DEREK TEMPONGKO
    ------------------------------

    Original Message


Related Content