Introduction to Db2 for z/OS System Profiles

System profiles are available as of Db2 for z/OS version 9, but they are still not widely used and known. However they represent quite a strong Db2 capability that can help you with a variety of needs such as managing and monitoring Db2 connections, modelling production environment, tuning application behaviour, and few other.

In this article, we will discuss how to define and activate a profile. Let’s get started.

Db2 profiles are defined within 2 catalog tables:

  • DSN_PROFILE_TABLE (“profile table”)
    A row in this table defines a profile ID (PROFILEID column), its applicability context (columns AUTHID, LOCATION, PLANNAME, etc.), and whether the profile is enabled (PROFILE_ENABLED)
  • DSN_PROFILE_ATTRIBUTES (“attributes table”)
    A row in this table defines a profile attribute (column KEYWORDS), attribute value (column ATTRIBUTE1 for VarChar-type value, ATTRIBUTE2 for Integer, and ATTRIBUTE3 for Float), and includes PROFILEID to identify the row relation to the profile table.

So, the profile definition is combination of a row in the profile table and one or more rows in the attributes table. There is a logical dependency between both tables, or in other words, between profile scope and profile type.

Each profile attribute has its applicability context. For example, the “SORT_POOL_SIZE” attribute has a global scope and thus all scope criteria AUTHID, LOCATION, etc. must be set to NULL. While in case of the “MONITOR CONNECTIONS” attribute, the scope criteria must contain specific Db2 thread qualification values. All of these details we will cover in the next articles that would be devoted to each specific profile type.

In addition to the catalog tables mentioned above, there are 2 more tables:

  • DSN_PROFILE_HISTORY
  • DSN_PROFILE_ATTRIBUTES_HISTORY

As the table names suggest, these tables contain historical information about profiles that were activated at some point. The tables have a very similar column structure as profile definition tables. The primary usage of the history tables is to check whether a profile got activated or learn why activation had failed.

All four tables are created with the installation job DSNTIJSG.

After a profile is defined (meaning relevant rows are inserted into both profile and attribute tables), the profile needs to be started. This is done very easily by issuing -START PROFILE command.

The -START PROFILE command starts the profiling which means all defined and enabled profiles are started (and restarted) at once. Be careful not start what you were not intended to activate. Similarly, there is a command -STOP PROFILE to stop profiling (to stop all active profiles) and -DISPLAY PROFILE to check if profiling is active.

Information about whether the profile has been started or not can only be found in the history tables. Both tables have STATUS columns that reflect this information. Here are few status examples: ACCEPTED, REJECTED - DUPLICATED SCOPE SPECIFIED, REJECTED - INVALID LOCATION SPECIFIED, etc.

The below is a sample SQL that can be used to extract profile status details from both history tables:

SELECT P.PROFILE_TIMESTAMP, P.STATUS AS PROFILE_STATUS,
       A.KEYWORDS, A.STATUS AS ATTRIBUTE_STATUS       
  FROM SYSIBM.DSN_PROFILE_HISTORY P                   
  LEFT JOIN SYSIBM.DSN_PROFILE_ATTRIBUTES_HISTORY A   
         ON P.PROFILEID = A.PROFILEID                 
        AND P.PROFILE_TIMESTAMP = A.ATTRIBUTE_TIMESTAMP
 WHERE P.PROFILEID = 1                                 
 ORDER BY P.PROFILE_TIMESTAMP DESC, A.KEYWORDS         

The statement will return all rows for the PROFILEID 1 in time descending order so that relevant status rows are returned first. Here is a sample output:

PROFILE_TIMESTAMP           PROFILE_STATUS  KEYWORDS        ATTRIBUTE_STATUS  
2017-09-21-04.08.15.204932  ACCEPTED        MAX_RIDBLOCKS   ACCEPTED 
2017-09-21-04.08.15.204932  ACCEPTED        SORT_POOL_SIZE  ACCEPTED 
...

The output tells that profile was accepted (its scope was set correctly) and two attributes are now in effect. The timestamp tells when the profile activation took place.

This concludes the article. Hopefully it gave you a bit of understanding how to define and manage profiles. More articles are to come that will focus on specific use-cases and provide details on how to set the corresponding profiles.

1 Comment
3 Likes

very good summary of what it is..

September 25, 2017 08:13 AM by Michal Bialecki

Quite powerful, but not many folks using it

Recent Stories
Introduction to Db2 for z/OS System Profiles

10 questions to review and understand your active log data set configuration

DB2 for z/OS query support over FTP