Db2 profiles: a less used but interesting feature

28 September 2020

How to define Db2 profiles?

DB2 profiles are managed by 2 "active" tables:
  • DSN_PROFILE_TABLE : filtering scopes
  • DSN_PROFILE_ATTRIBUTES : actions to take when criteria is met

and 2 history tables containing profiles that were in effect at some point in time:

  • DSN_PROFILE_HISTORY
  • DSN_PROFILE_ATTRIBUTES_HISTORY
Db2 profiles are created by inserting rows in both "active" tables.  To validate the coherency of your inserts you have to check the STATUS column in the history tables: 'ACCEPTED' when definition is correct, 'REJECTED' when it has to be investigated ...
 
Standards Db2 commands are usable with profiles: START, STOP, DISPLAY

Usage of Db2 Profiles

System modeling

The most famous use of Db2 Profiles is system modeling. You can easily simulate components properties and validate the impact on the optimizer:
  • Buffer Pool size
  • Rid Pool size
  • Sort Pool size
You can also custom Db2 Profiles to override subsystem parameters like STARJOIN, NPAGES, …and dedicate them to specific plans, collections or packages. 

Connections and Threads monitoring

Connections and threads parameters can be managed by Db2 Profiles: CONDBAT, MAXDBAT, IDTHOIN can be specified and targeted to specific connections/threads based on different parameters like IP address, AUTH ID, CLIENT_APPLNAME, CLIENT_USERID, ….
 

Setting special registers and global variables

You can set special registers for application that meet criteria defined in your profile.  More than 25 registers are available; the most widespread are:
  • APPLICATION COMPATIBILITY
  • SCHEMA
  • DEGREE
  • PACKAGESET
Global variables like GET_ARCHIVE , TEMPORAL_LOGICAL_TRANSACTIONS can be managed more specifically with DB2 PROFILE in Db2 v12.  And Function Level 507 introduces more application granularity when using profiles with 2 additional variables: 
  • MAX_LOCKS_PER_TABLESPACE
  • MAX_LOCKS_PER_USER
All these attributes can also be targeted to specific IP ADDRESS , AUTH_ID , COLLID, PKGNAME….
 

Pros and Cons

Pros

  • Configuration simulation
  • Granularity of parameter settings
  • Centralized in the DBMS

Cons

  • Must be well documented, because they are not very visible from an external point of view
    • Content of profile
    • Content of attribute
    • Activation of profile
  • Review the management procedure of your Db2’s
    • New messages
    • New traces
    • New IFCID
    • New commands

Use Case 1 : Evaluate a parameter change

You want to evaluate the impact of a modification at a system or application parameter. You can create a specific profile with the values you want to activate and an action level of type WARNING.  After starting the new profile, follow the message DSNT773I to monitor the future impact of your modification without impacting the subsystem behaviour. 
 

Use Case 2 : Avoid to adapt attributes of your NULLID Collection

For dynamic SQL, it is not recommended to adapt parameters (APPLCOMPAT, CONCENTRATESTMT ...) of the NULLID collection, as it would impact every client working with the default collection.  So, if you want an application to use specific options, you could duplicate the packages of the NULLID collection in a specific collection with the appropriate bind parameters.  Defining a profile that identify the application, you can redirect to the new COLLID with the use of the special register PACKAGE PATH.  Should your new settings not be optimal, a simple stop of the profile will restore the situation.
  

Conclusions

Db2 profiles can be very powerful and can be used to very finely customize your subsystems. Exceptions can be managed by Db2 Profiles and you will always have to weigh the trade-off between the flexibility of this feature and it's lack of visibility.  But it's of course up to you to see how this elegant and useful feature of Db2 could best apply to your configuration.