Thread count from Omegamon

Pankaj zope

Thread count from Omegamon

Hello Everyone,

There is a requirement from customer to generate a report for the thread count of Distributed thread from SMF records sorted by the Connection ID

I have run an accounting long report with following SYSIN

      INCLUDE (SUBSYSTEMID(DB2P)        

      CONNTYPE(DRDA)                    

              )                         

   ACCOUNTING                           

   REPORT                               

    LAYOUT (LONG)                       

    ORDER  (PRIMAUTH-PLANNAME)          

 EXEC                 

I am confused if #OCCURRENCES  will give me counts.To me it looks like the number of accounting records cut for that connection.Any Suggestions on report or a calculation to get the exact thread count per connection per day.

Daniel Luksetich

Thread count from Omegamon
(in response to Pankaj zope)
That is the number of accounting records. The actual number of threads may vary depending upon a number of factors, and also what exactly they want.



Number of concurrent threads (peak/non-peak)?

Number of transactions?

Number of threads (considering some may be reused)?



For number of transactions I would use commit count. Maybe for number of threads you can use number of occurrences, but that will be influenced by accounting rollup if you have that enabled.



Cheers,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Pankaj zope <[login to unmask email]>
Sent: Tuesday, March 27, 2018 1:06 PM
To: [login to unmask email]
Subject: [DB2-L] - Thread count from Omegamon



Hello Everyone,

There is a requirement from customer to generate a report for the thread count of Distributed thread from SMF records sorted by the Connection ID

I have run an accounting long report with following SYSIN

INCLUDE (SUBSYSTEMID(DB2P)

CONNTYPE(DRDA)

)

ACCOUNTING

REPORT

LAYOUT (LONG)

ORDER (PRIMAUTH-PLANNAME)

EXEC

I am confused if #OCCURRENCES will give me counts.To me it looks like the number of accounting records cut for that connection.Any Suggestions on report or a calculation to get the exact thread count per connection per day.



-----End Original Message-----

Michael Hannan

RE: Thread count from Omegamon
(in response to Pankaj zope)

In Reply to Pankaj zope:

Hello Everyone,

There is a requirement from customer to generate a report for the thread count of Distributed thread from SMF records sorted by the Connection ID

I have run an accounting long report with following SYSIN

      INCLUDE (SUBSYSTEMID(DB2P)        

      CONNTYPE(DRDA)                    

              )                         

   ACCOUNTING                           

   REPORT                               

    LAYOUT (LONG)                       

    ORDER  (PRIMAUTH-PLANNAME)          

 EXEC                 

I am confused if #OCCURRENCES  will give me counts.To me it looks like the number of accounting records cut for that connection.Any Suggestions on report or a calculation to get the exact thread count per connection per day.

If we are dealing with un-summarised Accounting data, More than one thread can still be rolled up into one accounting record in some circumstances. #OCCURENCES should indicate how many Threads rolled up into a record I think. Value 1 is seen often in un-summarised data, I believe. In summarised data, the #OCCURENCES would be summed up. I sometimes display this column because using summarised data. However I don't use it a lot. Not of greatest interest, unless looking at Thread reuse (I think).

It is not the number of transactions. I use the number of Commits and Rollbacks more so. This gets around the point that Threads can be reused.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Pankaj zope

RE: Thread count from Omegamon
(in response to Daniel Luksetich)

Dan,Michael,

The client is interested to have A report which has DATE,AUTHID ,Thread count,CPU Hours for an authid and average CPU hours(total CPU Hours by an authid/divided by number of thread).This will help them to understand which distributed application needs tuning.

Eventually the previous Vendor was sending this and hence the client is demanding it now from us as a process.

Edited By:
Pankaj zope[Organization Members] @ Mar 29, 2018 - 03:54 PM (Asia/Calcutta)

Daniel Luksetich

Thread count from Omegamon
(in response to Pankaj zope)
So, Omegamon isn’t really good at customized reports. I have had good success with two techniques.



1. Generate standard Omegamon reports and then scrape them using REXX to get just what I want
2. Create the Omegamon Performance Database, load it, and then use SQL to generate a custom report



I much prefer option 2.



Number of occurrences counts the accounting records. Thread count might be a little harder, especially if ACCUMAC is set and what ACCUMUID is then set to. Then if you use a similar AUTHID for batch and online transactions that may complicate it even more. I have good success with option 2, then aggregating using SQL the thread count (for online that would be commit count), CPU, etc. You may need significant DASD resources to store all that SMF data.



Cheers,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Pankaj zope <[login to unmask email]>
Sent: Thursday, March 29, 2018 5:24 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Thread count from Omegamon



Dan,

The client is interested to have A report which has DATE,AUTHID ,Thread count,CPU Hours for an authid and average CPU hours(total CPU Hours by an authid/divided by number of thread).This will help them to understand which distributed application needs tuning.

Eventually the previous Vendor was sending this and hence the client is demanding it now from us as a process.



-----End Original Message-----

Michael Hannan

RE: Thread count from Omegamon
(in response to Pankaj zope)

In Reply to Pankaj zope:

Dan,Michael,

The client is interested to have A report which has DATE,AUTHID ,Thread count,CPU Hours for an authid and average CPU hours(total CPU Hours by an authid/divided by number of thread).This will help them to understand which distributed application needs tuning.

Eventually the previous Vendor was sending this and hence the client is demanding it now from us as a process.

Under normal Accounting SMF collection, one detail record is cut per Thread, assuming no rollup options used, and assuming not using settings to force one record per transaction. Summing the #OCCURENCES probably gives a Thread count, but is it really useful? Thread is a very technical thing so Thread count sounds more like a solution than a requirement.

I guess I don't understand for certain what a "Thread Count" is intended to mean, and why it is wanted. If there was no Thread reuse at all, then it is easier to understand. However thread re-use certainly muddies the waters, especially in the DRDA scenario. DBATs are used by DRDA, and reusable with Release Deallocate.

Parameters can be set to control how long Threads last before cleanup, affecting Thread reuse..

I hate to produce reports just because someone says so, without being sure they are really meaningful. so why count Threads?

I agree with Dan's Option 2, but that is because I am highly SQL oriented. A colleague loads his Omegamon DB2 Accounting  reports into Excel spreadsheets. There are many different ways.

For tuning, I think the total CPU is a better thing to decide where to focus tuning efforts. The CPU per Thread can be not very meaningful. I like to tune Packages or SQLs, firstly with high total CPU in the peak hours, and then focussing on those SQLs with very high Getpages per SQL call or per Cursor. The later is as indicator that tuning might be easier. SQLs with very low Getpages per call are harder to tune. When total CPU is low, it does not matter whether application is efficient or not. Just not worth wasting human effort on it. A large portion of tuning is indeed measurement to find out what parts cost a lot. However CPU per Thread is not that useful to me. I never calculate it. I do look at CPU and Getpages per Transaction, as a means to see if transaction performance is stable or growing as example. Per Transaction is more useful than per Thread for online processes, but not so useful for batch jobs. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Mar 30, 2018 - 11:35 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 30, 2018 - 11:38 AM (Europe/Berlin)

Philip Sevetson

Thread count from Omegamon
(in response to Michael Hannan)
Michael,

Your comment here is well-placed. Is there an accounting class which cuts on a unit of work (Implicit or explicit COMMIT)? That would seem likely to be more useful in measuring the frequency of Client-Server OLTP and queries (through the Java and DDF gateways).

--Phil Sevetson

From: Michael Hannan [mailto:[login to unmask email]
Sent: Friday, March 30, 2018 5:27 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Thread count from Omegamon


In Reply to Pankaj zope:

Dan,Michael,

The client is interested to have A report which has DATE,AUTHID ,Thread count,CPU Hours for an authid and average CPU hours(total CPU Hours by an authid/divided by number of thread).This will help them to understand which distributed application needs tuning.

Eventually the previous Vendor was sending this and hence the client is demanding it now from us as a process.

Under normal Accounting SMF collection, one detail record is cut per Thread, assuming no rollup options used, and assuming not using settings to force one record per transaction. Summing the #OCCURENCES probably gives a Thread count, but is it really useful? Thread is a very technical thing so Thread count sounds more like a solution than a requirement.

I guess I don't understand for certain what a "Thread Count" is intended to mean, and why it is wanted. If there was no Thread reuse at all, then it is easier to understand. However thread re-use certainly muddies the waters, especially in the DRDA scenario. DBATs are used by DRDA, and reusable with Release Deallocate.

Parameters can be set to control how long Threads last before cleanup, affecting Thread reuse..

I hate to produce reports just because someone says so, without being sure they are really meaningful. so why count Threads?

I agree with Dan's Option 2, but that is because I am highly SQL oriented. A colleague loads his Omegamon DB2 Accounting reports into Excel spreadsheets. There are many different ways.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Pankaj zope

RE: Thread count from Omegamon
(in response to Philip Sevetson)

I think the best will be to give the number of transaction(occurenece) and the class2 CPU.I believe the client is interested in knowing which AUTH ID is the largest CPU consumer.But to perform this I have another roadblock. These task is supposed to be performed by the reporting tool using SAS. For this they need the exact field name of SMF record.I gave them the following from Omegamon manual but it look to be specific to tool and doesnot make any sense to them.

#OCCURRENCES -Field Name: ASOCCURS

CLASS 2 CPU- Field Name: ADDBCPUT

 Does someone has an idea if this filed name has some other name for SMF DSECT mapping. I searched SDSNMACS but didnot find anything relative.May be its a calculation of several fields together?

J&#248;rn Thyssen

RE: Thread count from Omegamon
(in response to Pankaj zope)

Hi Pankaj,

The SMF records are described in hlq.SDSNIVPD(DSNWMSGS)

Note that when a field starts with A in Omegamon the field is derived or calculated. You can find the explanation in hlq.TKO2SAMP(DGOAxxxx) members. 


In Reply to Pankaj zope:

I think the best will be to give the number of transaction(occurenece) and the class2 CPU.I believe the client is interested in knowing which AUTH ID is the largest CPU consumer.But to perform this I have another roadblock. These task is supposed to be performed by the reporting tool using SAS. For this they need the exact field name of SMF record.I gave them the following from Omegamon manual but it look to be specific to tool and doesnot make any sense to them.

#OCCURRENCES -Field Name: ASOCCURS

CLASS 2 CPU- Field Name: ADDBCPUT

 Does someone has an idea if this filed name has some other name for SMF DSECT mapping. I searched SDSNMACS but didnot find anything relative.May be its a calculation of several fields together?



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 

Michael Hannan

RE: Thread count from Omegamon
(in response to Philip Sevetson)

In Reply to Philip Sevetson:

Michael,

Your comment here is well-placed. Is there an accounting class which cuts on a unit of work (Implicit or explicit COMMIT)? That would seem likely to be more useful in measuring the frequency of Client-Server OLTP and queries (through the Java and DDF gateways).

Phil,

I think you need to look for this (quoting IBM web site): "You need to specify either ACCOUNTREC(TASK) or ACCOUNTREC(UOW) in the DB2 connection and DB2 entry resource definitions".  I am not really expert in this area, as it would be hard to convince sites to cut more accounting records than they currently do, but would be a big advantage to matching up Accounting records with CICS records, and also SMF Type 30 Interval records showing total task CPU consumption (e.g. by the hour).

It is a topic for me to look at further, since perhaps the accounting record increase might not be too large, depending a lot on how heavy is thread reuse in CICS and also DRDA DBATs. Batch jobs normally cut accounting records at the very end, instead of for each UOW, where the later would be nicer. "Query only" jobs with no commits would not be affected.

Normally I am using COMMIT and ROLLBACK counts in accounting records to determine the number of transactions for the Thread level record. I don't think can do that for Thread/Package level accounting records unless join them to the Thread level records or perhaps avoid that using Accounting Class 10 (IFCID 0239) in Db2 11 or higher, which provides some extra Package level accounting fields.  This comes with some overhead but is useful.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Apr 03, 2018 - 06:58 AM (Europe/Berlin)

Norbert Jenninger

RE: Thread count from Omegamon
(in response to Pankaj zope)

The best resource to refer to is the OMEGAMON DB2 Report Reference ( http://www-01.ibm.com/support/docview.wss?uid=swg27020910#omegaxepe-lib ) which describes this field:

#OCCURRENCES

The number of logical accounting records. A logical accounting record can contain more than one physical record.

This is the case, for example, in query CP and SYSPLEX query parallelism, where several accounting records (IFCID 003 and, optionally, 239) are generated, namely one for the entire thread and one for each parallel task within the thread.

In case of Distributed Data Facility (DDF) or Recoverable Resource Manager Services Attach Facility (RRSAF) threads, it is the number of accounting intervals rolled up in a record.

This number is used for calculating averages (as a divisor) for class 1, 2, 3, and 5 times and events.

Field Name: ASOCCURS

Means ASOCCURS is a derived and calculated field which reports real number of thread executions (= accounting intervals, for example, independent on any roll-up (ACCUMAC), or any parallel query executions, or any other situation an SMF 101 - IFCID 3 record was written by DB2.) 

When an IFCID 3 record is written depends on various conditions, e.g. 

RRSAF THREAD REUSE:                                        
EACH NEW SIGNON REQUEST CREATES A SEPARATE DB2 ACCOUNTING RECORD

or

CICS THREAD REUSE:                                         
IF THE AUTHORIZATION ID CHANGES, AN ACCOUNTING RECORD IS WRITTEN AND THE REASON FOR INVOCATION (QWACRINV) IS NEW USER.

or  ...

ASOCCURS will provide you always with "The number of logical accounting records."  and is used to calculate the average values in your Accounting REPORT

Accounting TRACE reports on each IFCID 3 record separately , n this case the field QUANTITY in the Highlights block will tell you how many logical accounting records were rolled-up (e.g. ACCUMAC) into this IFCID 3 record (again please refer to the Report Reference manual for more details).  

Norbert Jenninger

Michael Hannan

RE: Thread count from Omegamon
(in response to Norbert Jenninger)

In Reply to Norbert Jenninger:

#OCCURRENCES

The number of logical accounting records. A logical accounting record can contain more than one physical record.

This is the case, for example, in query CP and SYSPLEX query parallelism, where several accounting records (IFCID 003 and, optionally, 239) are generated, namely one for the entire thread and one for each parallel task within the thread.

In case of Distributed Data Facility (DDF) or Recoverable Resource Manager Services Attach Facility (RRSAF) threads, it is the number of accounting intervals rolled up in a record.

Interesting about the parallelism, I will have to look into that to see how affects #occurences. Sysplex Parallelism is dead now, right? I thought it was no longer supported.
 
"number of accounting intervals"? What intervals are you referring to? Basic accounting records are generally for the duration of a thread, not having any fixed time intervals. I don't know of an option to cut interval records for DB2 Acctg. SMF30 records do have intervals.

Perhaps you refer to summarised Db2 Acctg records, which can of course be summed by all records with a certain key value within an interval. However "number of intervals rolled into a record" still doesn't make sense to me.

Because of the complexity of exactly what you describe #occurences being dependent on, I don't see how most of the time it is going to make a sensible divisor for something. You will end with answers that are not very meaningful. Hence I rarely consider it to be a sensible divisor.

We need numbers that are meaningful and easy to understand rather than "mystical" result numbers that could mean something, some of the time. Do I go too far? Perhaps not.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Philip Sevetson

Thread count from Omegamon
(in response to Michael Hannan)
Michael,

1) For us, interested in performance statistics generally: Thanks.

2) For myself personally (my agency): now I have to figure out how to file this somewhere I won’t lose it, and how to relate this to the JDBC/Websphere connections which are the main online activity in our prod system.

--Phil

From: Michael Hannan [mailto:[login to unmask email]
Sent: Tuesday, April 03, 2018 12:57 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Thread count from Omegamon


In Reply to Philip Sevetson:
Michael,

Your comment here is well-placed. Is there an accounting class which cuts on a unit of work (Implicit or explicit COMMIT)? That would seem likely to be more useful in measuring the frequency of Client-Server OLTP and queries (through the Java and DDF gateways).

Phil,

I think you need to look for this (quoting IBM web site): "You need to specify either ACCOUNTREC(TASK) or ACCOUNTREC(UOW) in the DB2 connection and DB2 entry resource definitions". I am not really expert in this area, as it would be hard to convince sites to cut more accounting records than they currently do, but would be a big advantage to matching up Accounting records with CICS records, and also SMF Type 30 Interval records showing total task CPU consumption (e.g. by the hour).

It is a topic for me to look at further, since perhaps the accounting record increase might not be too large, depending a lot on how heavy is thread reuse in CICS and also DRDA DBATs. Batch jobs normally cut accounting records at the very end, instead of for each UOW, where the later would be nicer. "Query only" jobs with no commits would not be affected.

Normally I am using COMMIT and ROLLBACK counts in accounting records to determine the number of transactions for the Thread level record. I don't think can do that for Thread/Package level accounting records unless join them to the Thread level records or perhaps avoid that using Accounting Class 10 (IFCID 0239) in Db2 11 or higher, which provides some extra Package level accounting fields (see DSN1110.SDSNMACS). This comes with some overhead but is useful.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Norbert Jenninger

RE: Thread count from Omegamon
(in response to Michael Hannan)

#OCCURRENCES

Usually a transaction or application execution is considered as one thread for which one physical accounting records (IFCID 3) is created by Db2. In case of SQL query parallelism there is one or more additional physical record (IFCID 3) per each parallel thread execution. (you are right SYSPLEX query || is dead, but we still support older DB2 versions). In order to see the complete execution counts we accumulate all parallel thread info into the originating parent record for the REPORT, means you will see the complete execution counts of your application w/o having to accumulate the parallel thread execution by yourself.  That is why we talk about logical accounting record which can consist of several physical (parallel) records.

The other case is if a ACCUMAC roll-up contains a number RRSAF or DDF thread executions. In order to calculate the correct average times and counters per rolled-up thread we have to count the real number of rolled-up thread execution data.   

That is #OCCURRENCES is the number of originating threads (w/o counting the physical parallel thread because they are already accumulated by OM DB2  into the originating thread) and the number of rolled-up RSAF and DDF threads.

With this #OCCURRENCES we/you will have a divisor to get the average values application/transaction execution.  The REPORT is always an aggregated view of several application execution Ordered by the criteria you defined in your command language.

If you want to see the exact single execution values per accounting records (IFCID3 ) you have to run the TRACE report or even the RECTRACE report.   

 

"number of accounting intervals"

There exist several connection types to DB2 (e.g. DDF, CICS) which may allow to keep the connection alive, but DB2 would cut an accounting records (IFCID 3) in certain situations, e.g.  with each Re-signon.

Please refer to the DB2 manual “Managing Performance” (e.g. V11 SC19-4060), here an extract:

You get an accounting trace record each time that a thread is pooled rather than once for the entire time that you are connected. When a pooled thread becomes active, the accounting fields for that thread are re-initialized. As a result, the accounting record contains information about active threads only, which makes it easier to study how distributed applications are performing. If a pooled mode thread remains active because of sections that are specified with the KEEPDYNAMIC(YES) bind option, an accounting record is still written.

Exception: If you employ account record accumulation, an accounting trace is not produced each time that a thread becomes pooled. Accounting records can be rolled up by concatenated combinations of the following values:

– User ID

– End transaction name

– User workstation name

 

Or

You can use the accounting trace to ensure that your parallel queries are meeting their response time goals. DB2 rolls task accounting into an accounting record for the originating task. OMEGAMON also summarizes all

accounting records generated for a parallel query and presents them as one logical accounting record.  

Or

When CICS implements thread reuse, a change in the authorization ID or the transaction code initiates the sign-on process. This process terminates the accounting interval and creates the accounting record.

TXIDSO=NO eliminates the sign-on process when only the transaction code changes.

When a thread is reused without initiating sign-on, several transactions are accumulated into the same accounting record. The accumulated transactions can make it difficult to analyze a specific transaction

occurrence and correlate DB2 accounting with CICS accounting. However, applications that use ACCOUNTREC(UOW) or ACCOUNTREC(TASK) in the DBENTRY RDO definition initiate a partial sign-on,

which creates an accounting record for each transaction. You can use this data to tune your programs and to determine DB2 resource costs.

Michael Hannan

RE: Thread count from Omegamon
(in response to Norbert Jenninger)

Nobert,

Thanks, I am going to go read very carefully in the Manual. Always plenty of nuances to learn. "Each time a Thread is Pooled" is an interesting expression. I probably treated each occurrence as a Thread even if they did not choose to use that term, and technically it was not a new Thread. I still don't see the word "interval" mentioned, so I will assume that was a slight misnomer, or I was interpreting differently.

To me the complexities add to the point, that there maybe little meaningful point to counting Threads, or to using #OCCURENCES too heavily.

SMF data is hard to process, firstly due to understanding all the variations (site dependent), also coping with bugs in the population of some fields at some releases, e.g. when Class 2 CPU was bigger than Class 1 CPU ( a few years ago), etc.

Finally I did see the word interval used, and in this case it was merely meaning a duration of time, as each accounting record has a start timestamp and a finish timestamp, the difference being an "interval" in the manual terminology. If you count  these you get a record count (#occurrences). Calling it an "interval count" confused me (thinking of a number of fixed intervals).

In the end, typically when summarising or rolling up Accounting records, you keep the grouping columns, and for remainder, sum the numeric fields, and possibly take minimum and maximum of other non-numeric fields if they are of interest, or even count the number of distinct values.  Convert numbers to float before attempting any multiplication or division. DECFLOAT can be used (with restrictions) to avoid rounding of binary values. DECFLOAT probably contains too much precision for DB2 Tables storing Accounting data for the long term, with at min of 16 digits stored in 8 bytes, or 16 bytes for long form.

With and division, ensure there is no zero divide, and ensure the answer is meaningful at least most of the time.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Apr 06, 2018 - 04:37 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 06, 2018 - 02:13 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 06, 2018 - 02:14 PM (Europe/Berlin)