Application vs DB2 CPU time

Donna Domovic

Application vs DB2 CPU time

We're trying to get a better feel for the amount of CPU time being spent in the application vs in DB2 but we're having issues finding the data in SMF.  The following DB2 traces are active:

TYPE   CLASS        DEST

STAT   01,03,04,05, SMF

       06              

AUDIT  01,02,03     SMF

ACCTG  01,02,03     SMF

AUDIT  30           SMF

 

Can someone point us in the right direction as far as which SMF fields we should be looking at and if this is even possible?  We've tried several fields but so far it's not giving us what we're looking for.

Thanks,

Donna Domovic 

Bill Gallagher

Application vs DB2 CPU time
(in response to Donna Domovic)
I wouldn’t think that a DB2 trace would be able to tell you about something that is happening outside of DB2, i.e. application CPU time.

I would guess that the data you are looking for would be coming from non-DB2 related SMF records. Check with your favorite MVS systems programmer for help in identifying where that might be.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Donna Domovic [mailto:[login to unmask email]
Sent: Wednesday, December 06, 2017 2:13 PM
To: [login to unmask email]
Subject: [DB2-L] - Application vs DB2 CPU time


We're trying to get a better feel for the amount of CPU time being spent in the application vs in DB2 but we're having issues finding the data in SMF. The following DB2 traces are active:

TYPE CLASS DEST

STAT 01,03,04,05, SMF

06

AUDIT 01,02,03 SMF

ACCTG 01,02,03 SMF

AUDIT 30 SMF



Can someone point us in the right direction as far as which SMF fields we should be looking at and if this is even possible? We've tried several fields but so far it's not giving us what we're looking for.

Thanks,

Donna Domovic

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

Chad Walmer

Application vs DB2 CPU time
(in response to Bill Gallagher)
Actually it does. The fields for the TCB time from MVS are QWACBJST (beginning) and QWACEJST (end) in the 101 records. Just realize that it won’t have all of the CPU for the given application as it only starts to measure it once the first SQL statement is executed and the thread is created. The following link has a good explanation and diagram:

https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/perf/src/tpc/db2z_responsetimereports.html

Chad Walmer

From: GALLAGHER, WILLIAM [mailto:[login to unmask email]
Sent: Wednesday, December 6, 2017 3:01 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: Application vs DB2 CPU time

I wouldn’t think that a DB2 trace would be able to tell you about something that is happening outside of DB2, i.e. application CPU time.

I would guess that the data you are looking for would be coming from non-DB2 related SMF records. Check with your favorite MVS systems programmer for help in identifying where that might be.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Donna Domovic [mailto:[login to unmask email]
Sent: Wednesday, December 06, 2017 2:13 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Application vs DB2 CPU time


We're trying to get a better feel for the amount of CPU time being spent in the application vs in DB2 but we're having issues finding the data in SMF. The following DB2 traces are active:

TYPE CLASS DEST

STAT 01,03,04,05, SMF

06

AUDIT 01,02,03 SMF

ACCTG 01,02,03 SMF

AUDIT 30 SMF



Can someone point us in the right direction as far as which SMF fields we should be looking at and if this is even possible? We've tried several fields but so far it's not giving us what we're looking for.

Thanks,

Donna Domovic

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

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

Daniel Luksetich

Application vs DB2 CPU time
(in response to Bill Gallagher)
Typically Accounting class 1 provides total non-zIIP CPU and zIIP CPU in separate fields. Accounting class 2 provides values for DB2 CPU zIIP and non-zIIP. So, the difference would be the application CPU. This works for batch programs, and CICS, but may not completely reflect usage in a CICS open transaction environment. In that case you may be better off relying on CICS to tell you the amount of application time (SMF type 110).

Cheers,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

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: GALLAGHER, WILLIAM [mailto:[login to unmask email]
Sent: Wednesday, December 6, 2017 2:01 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: Application vs DB2 CPU time



I wouldn’t think that a DB2 trace would be able to tell you about something that is happening outside of DB2, i.e. application CPU time.



I would guess that the data you are looking for would be coming from non-DB2 related SMF records. Check with your favorite MVS systems programmer for help in identifying where that might be.



Bill Gallagher

DB2 Database Administrator

State of Connecticut





From: Donna Domovic [mailto:[login to unmask email]
Sent: Wednesday, December 06, 2017 2:13 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - Application vs DB2 CPU time



We're trying to get a better feel for the amount of CPU time being spent in the application vs in DB2 but we're having issues finding the data in SMF. The following DB2 traces are active:

TYPE CLASS DEST

STAT 01,03,04,05, SMF

06

AUDIT 01,02,03 SMF

ACCTG 01,02,03 SMF

AUDIT 30 SMF



Can someone point us in the right direction as far as which SMF fields we should be looking at and if this is even possible? We've tried several fields but so far it's not giving us what we're looking for.

Thanks,

Donna Domovic



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



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

Bill Gallagher

Application vs DB2 CPU time
(in response to Chad Walmer)
Chad,

The link and diagram seems to only be talking about elapsed time, not CPU time.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Chad A. Walmer [mailto:[login to unmask email]
Sent: Wednesday, December 06, 2017 3:12 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Application vs DB2 CPU time

Actually it does. The fields for the TCB time from MVS are QWACBJST (beginning) and QWACEJST (end) in the 101 records. Just realize that it won’t have all of the CPU for the given application as it only starts to measure it once the first SQL statement is executed and the thread is created. The following link has a good explanation and diagram:

https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/perf/src/tpc/db2z_responsetimereports.html

Chad Walmer

From: GALLAGHER, WILLIAM [mailto:[login to unmask email]
Sent: Wednesday, December 6, 2017 3:01 PM
To: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Subject: [DB2-L] - RE: Application vs DB2 CPU time

I wouldn’t think that a DB2 trace would be able to tell you about something that is happening outside of DB2, i.e. application CPU time.

I would guess that the data you are looking for would be coming from non-DB2 related SMF records. Check with your favorite MVS systems programmer for help in identifying where that might be.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Donna Domovic [mailto:[login to unmask email]
Sent: Wednesday, December 06, 2017 2:13 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Application vs DB2 CPU time


We're trying to get a better feel for the amount of CPU time being spent in the application vs in DB2 but we're having issues finding the data in SMF. The following DB2 traces are active:

TYPE CLASS DEST

STAT 01,03,04,05, SMF

06

AUDIT 01,02,03 SMF

ACCTG 01,02,03 SMF

AUDIT 30 SMF



Can someone point us in the right direction as far as which SMF fields we should be looking at and if this is even possible? We've tried several fields but so far it's not giving us what we're looking for.

Thanks,

Donna Domovic

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

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

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

Chad Walmer

Application vs DB2 CPU time
(in response to Bill Gallagher)
True, that particular link is focused on elapsed time but the DB2 class 1 times whether elapsed or CPU (which are generally referred to as “application”) in comparison to the class 2 times (known as “in DB2”) are both relevant. These are fairly common fields that are listed in nearly every DB2 monitor and the DB2 accounting reports. This is the first place one should look to determine whether most of the elapsed and/or CPU time is being spent in DB2 and this will dictate how you proceed to troubleshoot any service level issues for wall clock time or billing/charge back.

Perhaps this link shows it a little better on an Omegamon accounting report (see the “DB2 (CL. 1) CP CPU TIME” field description.)

https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/perf/src/tpc/db2z_accountingreports.html

Chad

From: GALLAGHER, WILLIAM [mailto:[login to unmask email]
Sent: Wednesday, December 6, 2017 3:39 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: Application vs DB2 CPU time

Chad,

The link and diagram seems to only be talking about elapsed time, not CPU time.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Chad A. Walmer [mailto:[login to unmask email]
Sent: Wednesday, December 06, 2017 3:12 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Application vs DB2 CPU time

Actually it does. The fields for the TCB time from MVS are QWACBJST (beginning) and QWACEJST (end) in the 101 records. Just realize that it won’t have all of the CPU for the given application as it only starts to measure it once the first SQL statement is executed and the thread is created. The following link has a good explanation and diagram:

https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/perf/src/tpc/db2z_responsetimereports.html https://urldefense.proofpoint.com/v2/url?u=https-3A__www.ibm.com_support_knowledgecenter_SSEPEK-5F11.0.0_perf_src_tpc_db2z-5Fresponsetimereports.html&d=DwMFaQ&c=dXExZTjGJCKlGVuOpvLOSA&r=rjAbDHfQX9PPHUggzwPYc9fgjQITYeiAp7KPknlMlyw&m=_aDG_fUZ6vPb7Beu78CC5m1I9Dnlq5_6AbtfA-GSywo&s=uFoXrWMXXQlbRGg_6HfIWSfzKc-xEWh9qt0mMe9hL5s&e=

Chad Walmer

From: GALLAGHER, WILLIAM [mailto:[login to unmask email]
Sent: Wednesday, December 6, 2017 3:01 PM
To: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Subject: [DB2-L] - RE: Application vs DB2 CPU time

I wouldn’t think that a DB2 trace would be able to tell you about something that is happening outside of DB2, i.e. application CPU time.

I would guess that the data you are looking for would be coming from non-DB2 related SMF records. Check with your favorite MVS systems programmer for help in identifying where that might be.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Donna Domovic [mailto:[login to unmask email]
Sent: Wednesday, December 06, 2017 2:13 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Application vs DB2 CPU time


We're trying to get a better feel for the amount of CPU time being spent in the application vs in DB2 but we're having issues finding the data in SMF. The following DB2 traces are active:

TYPE CLASS DEST

STAT 01,03,04,05, SMF

06

AUDIT 01,02,03 SMF

ACCTG 01,02,03 SMF

AUDIT 30 SMF



Can someone point us in the right direction as far as which SMF fields we should be looking at and if this is even possible? We've tried several fields but so far it's not giving us what we're looking for.

Thanks,

Donna Domovic

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

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

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

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

Michael Hannan

RE: Application vs DB2 CPU time
(in response to Daniel Luksetich)

Dan,

It turns out that Class 1 CPU Time fields in the SMF Accounting Detail Trace for threads, does include application incurred CPU Time after the first SQL call. So Application CPU can be roughly estimated to be Class 1 CPU minus the Class 2 CPU. Non DB2 SMF Type 30 records for batch job steps can be used to see just how close the DB2 Class 1 CPU time comes to the job step CPU time.

Now what I have stated above is not the Case for CICS in a non-thread-safe scenario. The application CPU time for CICS regions (not Thread safe) cannot be estimated by this method at all. The reason comes from the mechanism CICS uses to call DB2. To enable multi-tasking, CICS switches control to a sub-task, which in turn calls DB2. The Class 1 CPU time includes only the DB2 Class 2 CPU time plus the subtask switch overhead and potentially a few other subsystem overheads. It does not include real application processing CPU time.

As you stated, there are CICS oriented SMF data records that can be used to determine the application CPU time. That would be the reliable method. However the true indication of the DB2 overhead in a CICS region is the Class2 CPU time plus the CICS subtask switch overhead.

In a thread safe environment, CICS application processing may take place in another address space, so that scenario becomes far more complex. However still use the CICS SMF records to get the total CPU time.
 
In Reply to Daniel Luksetich:

Typically Accounting class 1 provides total non-zIIP CPU and zIIP CPU in separate fields. Accounting class 2 provides values for DB2 CPU zIIP and non-zIIP. So, the difference would be the application CPU. This works for batch programs, and CICS, but may not completely reflect usage in a CICS open transaction environment. In that case you may be better off relying on CICS to tell you the amount of application time (SMF type 110).

Cheers,

Dan

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd