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.

Begin program-specific programming interface information.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.

Tip: Start of changeInsert 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.End of change
Start of change
Table 1. Summary of uses for profiles, applicable filtering categories, and applicable KEYWORDS values
What the profile controls DSN_PROFILE_ATTRIBUTES.KEYWORDS values Applicable DSN_PROFILE_TABLE filtering categories Where to find more information
Remote TCP/IP 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'.

The value is not case-sensitive, and profile matches can occur regardless of the case of the input values.

Start of changeIf a domain name value can resolve to more than one IP address, Db2 uses only the first resolved IP address for profile filtering.End of change

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)
Start of changeSecure TCP/IP connectivityEnd of change Start of changeMONITOR product_type CONNECTIONS FOR SECURITYEnd of change Start of change
LOCATION only: specify one of the following values:
  • '*', '::0', or '0.0.0.0' (for all connections).
  • Start of changeA domain name that resolves to an IP address. An example fully qualified domain name is 'stlmvs1.svl.example.com'.End of change
  • Start of changeIPV4 or IPV6 IP address.End of change
  • Start of changeIPV4 or IPV6 subnet address.End of change
End of change
Start of changeDiscovering and controlling secure connectivity with profile tablesEnd of change
Remote TCP/IP threads MONITOR THREADS
  • LOCATION only
  • PRDID only
  • AUTHID, ROLE, or both
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME
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
  • LOCATION only
  • PRDID only
  • AUTHID, ROLE, or both
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME
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 (remote TCP/IP)
  • LOCATION only
  • PRDID only
  • AUTHID, ROLE, or both
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME
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
Start of changeFL 500 SPECIAL_REGISTER (local)End of change
  • AUTHID, ROLE, or both
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME
The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values.
Global variables GLOBAL_VARIABLE (remote TCP/IP)
  • LOCATION only
  • PRDID only
  • AUTHID, ROLE, or both
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME
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
FL 500 GLOBAL_VARIABLE (local)
  • AUTHID, ROLE, or both
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME
The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values.
Lock sharing for RRS connections SHARE_LOCKS
  • LOCATION only
  • PRDID only
  • AUTHID, ROLE, or both
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME
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
Start of changePackage release behaviorEnd of change Start of changeFL 500 RELEASE_PACKAGEEnd of change Start of change
For local threads and remote threads (DBATs), you can use the following filtering categories:
  • AUTHID, ROLE, or both
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME
You can also specify the following filtering categories for DBATs, but not for local threads:
  • LOCATION only
  • PRDID only
The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values.
End of change
Start of changeOverriding the RELEASE(DEALLOCATE) option for packages by using profile tablesEnd of change
Optimization subsystem parameters
  • MIN STAR JOIN TABLES
  • NPAGES THRESHOLD
  • STAR JOIN
PLANNAME='*', COLLID, and PKGNAME (specify all three) Optimizing subsystem parameters for SQL statements by using profile tables
Evaluating queries for acceleration ACCEL_NAME_EXPLAIN
  • None (global scope)
  • AUTHID and LOCATION
  • PLANNAME, COLLID, and PKGNAME
See the accelerator product documentation.
Query acceleration
  • ACCEL_TABLE_THRESHOLD
  • ACCEL_RESULTSIZE_THRESHOLD
  • ACCEL_TOTALCOST_THRESHOLD
Contact IBM® Support for the specific accelerator product. See the accelerator product documentation.
Subsystem modeling
  • BPname
  • MAX_RIDBLOCKS
  • SORT_POOL_SIZE
None. Profiles for this purpose have a global scope on the test subsystem. Modeling a production environment on a test subsystem
End of change

Column descriptions for DSN_PROFILE_TABLE

The following table describes the columns in SYSIBM.DSN_PROFILE_TABLE.

Table 2. SYSIBM.DSN_PROFILE_TABLE description
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:
  1. A fully specified value
  2. A set of characters followed by an asterisk (*) wildcard
  3. A single-byte asterisk value ('*') that applies to all threads and connections

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:
  1. A fully specified value
  2. A single-byte asterisk value ('*') that applies to all threads and connections
When an asterisk is used, the specified limit applies separately to the number of connections or threads that are allowed for each requester.

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:
  1. A fully specified value
  2. A single-byte asterisk value ('*') that applies to all threads and connections
When an asterisk is used, the specified limit applies separately to the number of connections or threads that are allowed for each requester.

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 IPv4 or IPv6 IP address of a remote client.
  • IP address '127.0.0.1' to specify the local Db2 subsystem.
  • A domain name that resolves to the IP address of a remote client. An example fully qualified domain name is 'stlmvs1.svl.example.com'.

    Start of changeIf a domain name value can resolve to more than one IP address, Db2 uses only the first resolved IP address for profile filtering.End of change

  • One of the following values that remote clients use to connect to the server:
    • Database name.
    • Location name.
    • Location alias.
  • An IPv4 subnet address which is the initial IPv4 address of a subnet followed by the subnet prefix as follows: IPv4address/mm where mm is 8, 16, or 24 and represents the number of initial bits of an IPv4 address which belongs in the subnet.
  • An IPv6 subnet address which is the initial IPv6 address of a subnet followed by the subnet prefix as follows: IPv6-address/mmm where mmm is 16, 32. 48, 64, 80, 96, or 112 and represents the number of initial bits of an IPv6 address which belongs in the subnet.
  • '0.0.0.0' or '::0' which represents any IP address.

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:
  1. A fully specified value
  2. A single-byte asterisk value ('*') that applies to all threads and connections
When an asterisk is used, the specified limit applies separately to the number of connections or threads that are allowed for each requester.
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:
Y
The profile is enabled.
N
The profile is disabled.
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:
  1. A fully specified value
  2. A single-byte asterisk value ('*') that applies to all threads and connections
When an asterisk is used, the specified limit applies separately to the number of connections or threads that are allowed for each requester.

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. Start of changeFor Db2 13 for z/OS®, the modification level (0–9 or A–Z) indicates a specific function level. For example:End of change
  • DSN13012 for V13R1M501.
  • DSN13011 for V13R1M500.
  • DSN13010 for V13R1M100.
Start of changeFor more information, see Product identifier (PRDID) values in Db2 for z/OS.End of change
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:
  1. A fully specified value
  2. A set of characters followed by an asterisk (*) wildcard
  3. A single-byte asterisk value ('*') that applies to all threads and connections

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:
  1. A fully specified value
  2. A single-byte asterisk value ('*') that applies to all threads and connections
When an asterisk is used, the specified limit applies separately to the number of connections or threads that are allowed for each requester.
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:
  1. A fully specified value
  2. A single-byte asterisk value ('*') that applies to all threads and connections
When an asterisk is used, the specified limit applies separately to the number of connections or threads that are allowed for each requester.
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:
  1. A fully specified value
  2. A single-byte asterisk value ('*') that applies to all threads and connections
When an asterisk is used, the specified limit applies separately to the number of connections or threads that are allowed for each requester.
End program-specific programming interface information.