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.
Insert only a single DSN_PROFILE_TABLE row for any specific set of filtering criteria. If multiple DSN_PROFILE_TABLE rows specify the same filtering criteria, only the newest is row is accepted when you start the profiles, and the other duplicates are rejected.
| 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 |
| MONITOR ALL CONNECTIONS |
LOCATION only: specify '*', '::0', or '0.0.0.0'. |
Monitoring remote connections by using profile tables (see step 3) |
|
| Remote threads | MONITOR THREADS |
The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values. |
Monitoring threads by using profile tables |
| MONITOR IDLE THREADS |
The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values. |
Monitoring idle threads by using profile tables | |
| MONITOR ALL THREADS |
LOCATION only: specify '*', '::0', or '0.0.0.0'. |
Monitoring threads by using profile tables (see step 3) |
|
| Special registers | SPECIAL_REGISTER |
The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values. |
Setting special registers by using profile tables |
| Global variables |
GLOBAL_VARIABLE |
The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values. |
Setting built-in global variables by using profile tables |
| Lock sharing for RRS connections |
SHARE_LOCKS |
The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values. |
Sharing locks for stored procedures that invoke transactions in RRS contexts 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. You can use an asterisk (*) wildcard in this column to specify that multiple values match the criteria for this profile. However, for any thread or connection that matches multiple profiles, Db2 applies only the single most specific profile, in the following order of precedence:
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. You can use a single-byte asterisk value ('*') in this column to specify that the profile matches all connections or threads that connect to the Db2 subsystem. However, for any thread or connection that matches multiple profiles, Db2 applies only the single most specific profile, in the following order of precedence:
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. You can use a single-byte asterisk value ('*') in this column to specify that the profile matches all connections or threads that connect to the Db2 subsystem. However, for any thread or connection that matches multiple profiles, Db2 applies only the single most specific profile, in the following order of precedence:
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. You can use a single-byte asterisk value ('*') in this column to specify that the profile matches all connections or threads that connect to the Db2 subsystem. However, for any thread or connection that matches multiple profiles, Db2 applies only the single most specific profile, in the following order of precedence:
|
| 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. .You can use a single-byte asterisk value ('*') in this column to specify that the profile matches all connections or threads that connect to the Db2 subsystem. However, for any thread or connection that matches multiple profiles, Db2 applies only the single most specific profile, in the following order of precedence:
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 more information, see Product identifier (PRDID) values in Db2 for z/OS.You can use an asterisk (*) wildcard in this column to specify that multiple values match the criteria for this profile. However, for any thread or connection that matches multiple profiles, Db2 applies only the single most specific profile, in the following order of precedence:
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. You can use a single-byte asterisk value ('*') in this column to specify that the profile matches all connections or threads that connect to the Db2 subsystem. However, for any thread or connection that matches multiple profiles, Db2 applies only the single most specific profile, in the following order of precedence:
|
| 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. You can use a single-byte asterisk value ('*') in this column to specify that the profile matches all connections or threads that connect to the Db2 subsystem. However, for any thread or connection that matches multiple profiles, Db2 applies only the single most specific profile, in the following order of precedence:
|
| 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. You can use a single-byte asterisk value ('*') in this column to specify that the profile matches all connections or threads that connect to the Db2 subsystem. However, for any thread or connection that matches multiple profiles, Db2 applies only the single most specific profile, in the following order of precedence:
|
