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:
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.