DSN_PROFILE_TABLE profile table
Each row in the profile table, DSN_PROFILE_TABLE, defines a profile. A system profile is a set of criteria that identifies a specific context on a Db2 subsystem. Examples include threads, connections, or SQL statements that have certain attributes. The schema is SYSIBM.
Profile tables identify filtering contexts in which Db2 takes particular actions such as resource monitoring, subsystem parameter customization, and dynamic SQL stabilization. The contexts might identify statements, threads, or connections that are based on information about the originating application, system, or user. For an overview of how to use profile tables and a summary of the specific uses for profile tables, see Monitoring and controlling Db2 by using profile tables.
The profile tables and related indexes are created when you run job DSNTIJSG during Db2 installation, as described in Job DSNTIJSG.
Db2 supports a maximum of 4096 active DSN_PROFILE_TABLE rows.
Filtering categories for DSN_PROFILE_TABLE
The filtering criteria that you specify for each profile depend on its purpose. Each DSN_PROFILE_TABLE row specifies filtering criteria from a single filtering category, which is a set of one or more specific DSN_PROFILE_TABLE columns. The row must contain null values in other filtering columns not in same category. For a list of the filtering categories that you can specify for each type of profile, see the following table.
![Start of change](../../delta.gif)
![End of change](../../deltaend.gif)
What the profile controls | DSN_PROFILE_ATTRIBUTES.KEYWORDS values | Applicable DSN_PROFILE_TABLE filtering categories | Where to find more information |
---|---|---|---|
Remote connections | MONITOR CONNECTIONS | LOCATION only: specify an IP address or a domain name that resolves to an IP address. An example fully qualified domain name is 'stlmvs1.svl.example.com'. An example fully qualified domain name is 'stlmvs1.svl.example.com'.
|
Monitoring remote connections by using profile tables |
![]() ![]() |
![]() LOCATION only: specify '*', '::0', or '0.0.0.0'. ![]() |
![]() ![]() |
|
Remote threads | MONITOR THREADS |
![]() ![]() |
Monitoring threads by using profile tables |
MONITOR IDLE THREADS |
![]() ![]() |
Monitoring idle threads by using profile tables | |
![]() ![]() |
![]() LOCATION only: specify '*', '::0', or '0.0.0.0'. ![]() |
![]() ![]() |
|
Special registers | SPECIAL_REGISTER |
![]() ![]() |
Setting special registers by using profile tables |
![]() ![]() |
![]() ![]() |
![]()
![]() ![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
![]() ![]() ![]() |
![]() ![]() |
Optimization subsystem parameters |
|
PLANNAME='*', COLLID, and PKGNAME (specify all three) | Optimizing subsystem parameters for SQL statements by using profile tables |
Evaluating queries for acceleration | ACCEL_NAME_EXPLAIN |
|
See the accelerator product documentation. |
Query acceleration thresholds |
|
Contact IBM® Support for the specific accelerator product. | See the accelerator product documentation. |
Subsystem modeling |
|
None. Profiles for this purpose have a global scope on the test subsystem. | Modeling a production environment on a test subsystem |
Column descriptions for DSN_PROFILE_TABLE
The following table describes the columns in SYSIBM.DSN_PROFILE_TABLE.
Column name | Data type | Description |
---|---|---|
AUTHID | VARCHAR(128) | The authorization ID of a monitored user. ![]()
![]() For example, if the column value is 'TEMP*', then any authorization ID which begins with TEMP (and including TEMP by itself), would match this profile specification. Default profiles specified by '*' cannot be used for both ROLE and AUTHID. If it is used, '*' must be specified for only one of these attributes. |
PLANNAME | VARCHAR(24) | The name of a plan. |
COLLID | VARCHAR(128) | A collection identifier of a monitored collection. ![]()
![]() Default profiles specified by '*' cannot be used for both COLLID and PKGNAME. If it is used, '*' must be specified for only one of these attributes. |
PKGNAME | VARCHAR(128) | A package name of a monitored plan. ![]()
![]() Default profiles specified by '*' cannot be used for both COLLID and PKGNAME. If it is used, '*' must be specified for only one of these attributes. |
LOCATION | VARCHAR(254) |
One of the following items:
The value is interpreted as a location name when the name string is less than or equal to 16 bytes and does not contain colon (:) or period (.) characters. When these characters are found, the value is checked for a valid IP address or a valid domain name. ![]()
![]() |
PROFILEID | INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY NOT NULL | The unique identifier for the profile that is defined by this row. |
PROFILE_TIMESTAMP | TIMESTAMP NOT NULL WITH DEFAULT | The time when the row was inserted or updated. |
PROFILE_ENABLED | CHAR(1) NOT NULL WITH DEFAULT 'Y' | Indicates whether the profile is enabled. This column can have one of the following values:
|
GROUP_MEMBER | VARCHAR(24) | The name of the Db2 member in a data sharing group. The column can be blank. When the column is blank, the row applies to a Db2 subsystem that is not part of a data sharing group, or to every Db2 subsystem in a data sharing group. |
REMARKS | VARCHAR(762) | Comments about this profile. |
ROLE | VARCHAR(128) WITH DEFAULT NULL | The role of a monitored user or users. .![]()
![]() Default profiles specified by '*' cannot be used for both ROLE and AUTHID. If it is used, '*' must be specified for only one of these attributes. |
PRDID | CHAR(8) WITH DEFAULT NULL | The product-specific identifier of a monitored remote requester. The product identifier is an 8-byte alphanumeric field. The product identifier (PRDID) value is an 8-byte character value in pppvvrrm format, where: ppp is a 3-letter product code; vv is the version;rr is the release; and m is the modification level. In Db2 12 for z/OS®, the modification level indicates a range of function levels:
![]() ![]() ![]()
![]() For example, if the column value is 'DSN*', the profile applies to any Db2 for z/OS. |
CLIENT_APPLNAME | VARCHAR(255) | The client application name information. It contains the value of the application name or transaction name from the client information that is specified for the connection. This value corresponds to the CURRENT CLIENT_APPLNAME special register. The client application driver version determines the maximum length of the client application name. ![]()
![]() |
CLIENT_USERID | VARCHAR(255) | The client user ID name information. It contains the value of the client user ID from the client information that is specified for the connection. This value corresponds to the CURRENT CLIENT_USERID special register. If the length of the client user ID name value exceeds 128 bytes, it is truncated to 128 bytes for filtering criteria. The client application driver version determines the maximum length of the client user ID which can be set. ![]()
![]() |
CLIENT_WRKSTNNAME | VARCHAR(255) | The client workstation name information. It contains the value of the workstation name from the client information that is specified for the connection. This value corresponds to the CURRENT CLIENT_WRKSTNNAME special register. The client application driver version determines the maximum length of the client workstation name which can be set. ![]()
![]() |