Monitor the Monitors – Db2 Profiles

Db2 profiles can be used to control or let say further tailor your Db2 system beyond the customization provided by Db2 zparms.

From the distributed Db2 workload perspective you can monitor CONNECTIONS , ACTIVE THREADS , IDLE THREADS.

From the application behavior front ?you have the power to set SPECIAL REGISTERS , set zparms just for the scope of application and more.

All the customization that can be set and controlled is done by populating the following tables.

SYSIBM.DSN_PROFILE_TABLE

SYSIBM.DSN_PROFILE_ATTRIBUTES

Just like any other DDF Monitoring related zparms such as (CONDBAT , MAXDBAT etc.) which are frequently monitored for their performance , monitoring these Db2 profiles ensure their optimal performance especially the profiles which are set for system monitoring.

AMI Ops Monitor for Db2 (Mainview for Db2 earlier) comes handy for this task. It provides Db2 Profile monitoring options , which can be accessed using EZDDF View.

Questions I want to explore on Db2 Profiles using AMI Ops Monitor for Db2 .

Is Db2 Profile Enabled for Db2 regions ?

Select Monitor Profile All - PRFLIST view .

Data in PRFLIST view will show list of active profiles .

Profile Id which corresponds to the entries in DSN_PROFILE_TABLE

What details are available for active profiles ?

View PRFDETL provides you detail on each profile ID .

Tip: Users of ?BMC AMI Catalog Manager can create a hyperlink on Profile ID field which will enable them? to further drill down on the attributes set for the respective profiles.

?

How efficient are defined Db2 profiles for a particular Db2 ??

Use option Monitor Profile Xcepts on EZDDF view which will list profile experiencing exception or warning as defined in DSN_PROFILE_ATTRIBUTES .

For this example :

PROFILEID=103 is defined for MONITOR CONNECTIONS for location IN-MJADWANI-W1.BMC.COM and is for the maximum of 4 connections allowed.

Any connection beyond 4 from this location will throw an error “EXCEPTION_DIAGLEVEL2” .

Snippet from DSN_PROFILE_ATTRIBUTES

For this setting as soon as my connection goes beyond threshold of 4 exception can be seen.

On drilling further PRFDETL? view shows

Some of the important fields on view PRFDETL to note are Exceptions on “session” and “last” Event Counters section.

If “Last” counter is increasing or if “Session” counter is high right time is to look at threshold setting for “MONITOR CONNECTION” profile and tune it.

As this profile is only for “MONITOR CONNECTION” only Connection related Event Counters are being updated.

Is there a way to list Db2 regions which are using Db2 profiles ?

View PRFALLZ is the answer.

For my example I have 2 Db2 targets which where Db2 profile is currently enabled.

Further the fields also show highlights exceptions which allows for easy navigation to Db2 of interest.

This is simple example used to demonstrate how to monitor Db2 profiles .

On production environment where multiple Db2 profiles may be active and on multiple Db2 regions , AMI Ops Monitor for Db2 increases the visibility of all active Db2 profiles in play and exceptions/warnings raised for them.

Do let me know your thoughts and ideas if you want to any delighters on top of the functionality provided for Db2 profiles by AMI Ops Monitor for Db2 in comments.