Example - Resource Usage by Database

Here is a query against one of the repository views that could be used to report on resource consumption by week for selected databases in your enterprise.  The appropriate view for this purpose is throughput_all, documented here Repository View THROUGHPUT_ALL.

You can see sample results from this query at the end of this page.

Query

    select

        julian_day(collected) / 7 as julian_week

      , substr(dbconn_id,1,20) as dbconn_id

      , date(max(collected)) as end_date

      , sum(total_cpu_usec_delta) / 1000000.0 as cpu_sec

      , sum(logical_reads_delta) as logical_reads

      , sum(act_completed_total_delta) as activities

      , sum(total_app_commits_delta) as commits

    from ibm_dsm_views.throughput_all

    where

      dbconn_id in ('tpchk', 'trade9', 'gsdbk scenenv', 'gsdbdev 64266') and

      prev_collected >= '2015-06-01-00.00.00.000000' and

      collected < '2015-07-01-00.00.00.000000'

    group by julian_day(collected) / 7, dbconn_id

    order by julian_week, dbconn_id;

Explanation

Julian Week

To group the data by week, we need to assign each collection interval to a calendar week.  This is done by taking the Julian day of the end time for the collection interval and dividing by 7.  In the simple form shown here, this produces weeks starting on Monday and ending on Sunday.  If you want a different start day for the week, you can add or subtract the appropriate number of days before dividing.  For example the following will result in weeks that run from Sunday through Saturday:

(julian_day(collected)+1) / 7

Dbconn_id

The field dbconn_id is the name given to the connection when monitoring was configured for this database.  This name is unique within DSM, unlike the actual database name because it is possible to create identically named databases in different instances.

End Date

This is the date of the last collection interval for which data is available in the repository.  In the test data used to generate this example, there are some gaps in the monitoring data for some databases, and you can see it reflected as reporting periods that do not end on a Sunday.  This can happen for several reasons such as if monitoring was disabled for a database or the instance was shut down for an extended period of time.  You might prefer to show the end of each reporting period instead.  One way to accomplish this is like so:

date(((julian_day(collected))/7)*7-1721419)

CPU Seconds

DB2 reports CPU time consumed in microseconds.  For this example, the CPU time was scaled to seconds by multiplying by 1,000,000.

Logical Reads

To keep this example simple, the query selects logical reads because it is available in the view throughput_all.  A better measure of IO consumed by a database would be physical reads.  Physical reads can be found in another view mon_get_bufferpool, documented here <link TBD>.

Activities

Activities tells how many queries were run against the database.  LOAD also is counted as an activity, as are a few other things.  So this is only an approximation for the number of queries run.  For most databases it should be fairly close to the number of queries run.

Sample Results

JULIAN_WEEK DBCONN_ID      END_DATE   CPU_SEC            LOGICAL_READS ACTIVITIES COMMITS

----------- -------------- ---------- ------------------ ------------- ---------- ----------

     351025 gsdbdev 64266  06/07/2015   2635.32712800000       3911641     838629     721948

     351025 gsdbk scenenv  06/07/2015   2422.06318300000      15432008     814110     652700

     351025 tpchk          06/07/2015   2210.31616000000       2830431     741392     627471

     351025 trade9         06/07/2015   2095.73718400000      12393515     810989     721085

     351026 gsdbdev 64266  06/14/2015   2866.52342700000       3311209     753097     635174

     351026 gsdbk scenenv  06/14/2015  66866.77884300000    2999907087    2454780    2168171

     351026 tpchk          06/14/2015   1846.91719700000       2988276     694545     603778

     351026 trade9         06/14/2015   2747.33528700000      13483291     812204     732206

     351027 gsdbdev 64266  06/18/2015   1154.65592500000       1550837     305404     239552

     351027 gsdbk scenenv  06/18/2015   3170.11955200000      75471385    1030416     941513

     351027 tpchk          06/20/2015    790.14760800000      11265193     369949     298602

     351027 trade9         06/21/2015   2134.18148800000      14166517     506101     421946

     351028 gsdbdev 64266  06/28/2015   1730.27004400000       3118583     521837     381476

     351028 gsdbk scenenv  06/28/2015   8012.35184200000     296411529    1517370    1320814

     351028 tpchk          06/28/2015    710.79818400000       2452285     447051     339693

     351028 trade9         06/28/2015   1460.23883400000      12556437     638793     552259

     351029 gsdbdev 64266  06/30/2015    587.86657200000        768863     148427     113215

     351029 gsdbk scenenv  06/30/2015   1489.08032700000      60489475     738007     583377

     351029 tpchk          06/30/2015    234.94692800000       1037820     175842     141879

     351029 trade9         06/30/2015    470.14606000000       4885936     166955     142342

  20 record(s) selected.