DB2 - L

 View Only
  • 1.  Excessive SMF type 101 accounting records

    Posted Nov 10, 2021 01:52 PM
    Hello, listers! We are running DB2 v12 on z/OS, and a server running DB2 Connect and Adobe ColdFusion. This server is generation millions of SMF type 101 records per day in which the summarization fields QWHCEUID, QWHCEUTX, and QWHCEUWN contain hex zeroes, so I cannot reduce the volume via ACCUMACC. I opened a case with IBM, and they told me the developers will have to update their applications to set those fields via a call to the java.sql.Connection.setClientInfo. Sometimes we get flooded with these records so quickly (greater than 500,000 in a minute) that the SMF buffers get overrun and we lose SMF data, which makes me question whether those records represent actual users making SQL calls. My question for you all is, have you faced a similar situation in which ACCUMACC cannot reduce excessive SMF type 101's, and how did you address it? I am loathe to tell developers what to do when I don't have a firm grasp of the situation myself. Thanks in advance!

    Regards,
    Cathy Taddei

    ------------------------------
    CathyTaddeiState of Oregon
    ------------------------------


  • 2.  RE: Excessive SMF type 101 accounting records

    Posted Nov 11, 2021 07:19 AM
    The fields you're mentioning need to be set by the user (the application), so yes, your developers would need to set them explicitly to populate them. In my experience, though, this is one of those things that developers SAY they're going to do, and never do. Never quite makes it into the next "scrum". :)

    Couple of thoughts:

    * Do you have a good working relationship with your developers? It's their code, so it may be helpful to ping them and ask, "Hey guys, we see these bursts of transactions coming from your server...any idea what's going on here?" 

    * That conversation may go better if you can point to a specific operation. The 101s should also have a count of the SQL statements being executed, so you could tell exactly what's happening on each thread. Additionally, what monitor package are you using for Db2? I know IBM's tools, as well as some other vendors', will give you the dynamic SQL in a DDF thread if you turn that feature on...you could see if it's one statement being issued over and over again.

    * If it's as "bursty" as you say, this could be some kind of programmatic loop. It may be that the application is coded to grab a new thread for every SQL statement it issues, instead of opening a single thread and using it to drive 500,000 statements. They could also be just COMMIT-ting every statement and be setting db2.jcc.accountingInterval=commit, which would cut an SMF101 each time. Developers aren't always aware of the overhead of poor thread management in development/testing, they just know, "code works".

    If the 101s actually represent "legit" workload, then you can move into managing them.

    ------------------------------
    MarkWieczorkowski...
    ------------------------------



  • 3.  RE: Excessive SMF type 101 accounting records

    Posted Nov 11, 2021 09:48 AM
    Edited by David Alban Nov 11, 2021 02:12 PM

    Cathy,

    While the best solution is to have the developers set the end user fields, to get past the problem initially what might help is to set ACCUMUID to 11 and ACCUMAC to something like 5 or 10. ACCUMUID is set to 0 by default and has a footnote "Note: At least one value in the set of criteria must have a value other than a string of X'00's or a string of X'40's." However, ACCUMUID with a value of 11 does not denote this limitation and could help for the near-term.

    AGGREGATION FIELDS field (ACCUMUID subsystem parameter) - IBM Documentation

    For our environment the three user fields usually have some default and are not blank, so I cannot speak to why that is happening in your environment. The default values we see are things like java.exe or db2jcc which are not helpful either (but better than blanks at least). That said, we had some success in convincing some developers to set client application names to be meaningful by having WLM prioritization rules set up based on an application name/naming scheme. i.e. if they want better CPU prioritization they have to set their end user fields where applicable (driver config file, connection string, DSN, or explicitly in application code).



    ------------------------------
    Thanks,
    David
    ------------------------------



  • 4.  RE: Excessive SMF type 101 accounting records

    Posted Nov 17, 2021 08:14 PM
    Thank you, David and Mark, for your helpful feedback. I did engage our developers in a conversation, and they are willing to help. Although IBM had instructed us to call method java.sql.Connection.setClientInfo, it appears that we may be able to set the required fields in the connection string, so we are going to try to devise a test, and see if we can get rid of the hex zeroes in the accounting fields. At this point, I don't care what they put in there, whether the data is meaningful or not, I just need something to summarize on. Here is one of their connection strings that we want to update, I'm curious if anyone else would be willing to share theirs? I have obscured the data values for privacy.

    locationName=DB2SUBSYS;collectionID=APPCOLLID;packageName=APPPACK;CreateDefaultPackage=FALSE;

    Our hope is that adding CLIENT_APPLNAME, CLIENT_USERID, and CLIENT_WRKSTNNAME will be possible and produce the desired results. Does anyone know if this can be added to the connection string, or will the actual applications have to be updated to call setClientInfo?

    Thanks and regards,
    Cathy Taddei

    ------------------------------
    CathyTaddeiState of Oregon
    ------------------------------



  • 5.  RE: Excessive SMF type 101 accounting records

    Posted Nov 18, 2021 10:28 AM
    I've had guys developing code in AWS cloud (using non-IBM datasources) putting connection identifiers in the Java connect-string for WLM classification and such...if there's a JDBC parm for it, it should work.

    -Mark

    ------------------------------
    MarkWieczorkowski...
    ------------------------------



  • 6.  RE: Excessive SMF type 101 accounting records

    Posted Nov 18, 2021 11:58 AM

    Thanks, Mark.  I'll let you all know if we are successful.

     

    Regards,

    Cathy Taddei

     






  • 7.  RE: Excessive SMF type 101 accounting records

    Posted Nov 18, 2021 06:46 PM
    Merely noting that the JDBC parm names are not the same as the Db2 special register
    names.

    https://www.ibm.com/docs/en/db2-for-zos/12?topic=pecidscip-client-info-properties-support-by-data-server-driver-jdbc-sqlj


    James Campbell


    On 18 Nov 2021 at 16:58, Cathy Taddei via Internationa wrote:

    > Thanks, Mark. I'll let you all know if we are successful.
    >
    > Regards,
    > Cathy Taddei
    >
    >
    >
    > -------------------------------------------
    > Original Message:
    > Sent: 11/18/2021 10:28:00 AM
    > From: Mark Wieczorkowski
    > Subject: RE: Excessive SMF type 101 accounting records
    >
    > I've had guys developing code in AWS cloud (using non-IBM datasources) putting connection identifiers in the Java connect-string for WLM classification and such...if there's a JDBC parm for it, it should work.
    >
    > -Mark
    >
    > ------------------------------
    > MarkWieczorkowski...
    > ------------------------------
    > -------------------------------------------
    > Original Message:
    > Sent: Nov 17, 2021 08:14 PM
    > From: Cathy Taddei
    > Subject: Excessive SMF type 101 accounting records
    >
    > Thank you, David and Mark, for your helpful feedback. I did engage our developers in a conversation, and they are willing to help. Although IBM had instructed us to call method java.sql.Connection.setClientInfo, it appears that we may be able to set the required fields in the connection string, so we are going to try to devise a test, and see if we can get rid of the hex zeroes in the accounting fields. At this point, I don't care what they put in there, whether the data is meaningful or not, I just need something to summarize on. Here is one of their connection strings that we want to update, I'm curious if anyone else would be willing to share theirs? I have obscured the data values for privacy.
    >
    > locationName=DB2SUBSYS;collectionID=APPCOLLID;packageName=APPPACK;CreateDefaultPackage=FALSE;
    >
    > Our hope is that adding CLIENT_APPLNAME, CLIENT_USERID, and CLIENT_WRKSTNNAME will be possible and produce the desired results. Does anyone know if this can be added to the connection string, or will the actual applications have to be updated to call setClientInfo?
    >
    > Thanks and regards,
    > Cathy Taddei
    >
    > ------------------------------
    > CathyTaddeiState of Oregon
    > ------------------------------
    >
    > Original Message:
    > Sent: Nov 10, 2021 01:52 PM
    > From: Cathy Taddei
    > Subject: Excessive SMF type 101 accounting records
    >
    > Hello, listers! We are running DB2 v12 on z/OS, and a server running DB2 Connect and Adobe ColdFusion. This server is generation millions of SMF type 101 records per day in which the summarization fields QWHCEUID, QWHCEUTX, and QWHCEUWN contain hex zeroes, so I cannot reduce the volume via ACCUMACC. I opened a case with IBM, and they told me the developers will have to update their applications to set those fields via a call to the java.sql.Connection.setClientInfo. Sometimes we get flooded with these records so quickly (greater than 500,000 in a minute) that the SMF buffers get overrun and we lose SMF data, which makes me question whether those records represent actual users making SQL calls. My question for you all is, have you faced a similar situation in which ACCUMACC cannot reduce excessive SMF type 101's, and how did you address it? I am loathe to tell developers what to do when I don't have a firm grasp of the situation myself. Thanks in advance!
    >
    > Regards,
    > Cathy Taddei
    >
    > ------------------------------
    > CathyTaddeiState of Oregon
    > ------------------------------
    >
    >

    --
    This email has been checked for viruses by AVG.
    https://www.avg.com