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.

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.

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 connections MONITOR CONNECTIONS LOCATION only (IP address or domain name )

Start of changeThe value is not case sensitive, and profile matches can occur regardless of the case of the input values.End of change

Monitoring remote connections by using profile tables
Start of changeMONITOR ALL CONNECTIONSEnd of change Start of change

LOCATION only (specify '*', '::0', or '0.0.0.0'.)

End of change
Start of changeMonitoring remote connections by using profile tables (see step 3)End of change
Remote threads MONITOR THREADS
  • LOCATION only
  • PRDID only
  • AUTHID, ROLE, or both
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME
Start of changeThe filtering values are not case sensitive, and profiles can match regardless of the case of the input values.End of change
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
Start of changeThe filtering values are not case sensitive, and profiles can match regardless of the case of the input values.End of change
Monitoring idle threads by using profile tables
Start of changeMONITOR ALL THREADSEnd of change Start of change

LOCATION only (specify '*', '::0', or '0.0.0.0'.)

End of change
Start of changeMonitoring threads by using profile tables (see step 3)End of change
Special registers SPECIAL_REGISTER
  • LOCATION only
  • PRDID only
  • AUTHID, ROLE, or both
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME
Start of changeThe filtering values are not case sensitive, and profiles can match regardless of the case of the input values.End of change
Setting special registers by using profile tables
Start of changeGlobal variablesEnd of change Start of changeGLOBAL_VARIABLEEnd of change Start of change
  • LOCATION only
  • PRDID only
  • AUTHID, ROLE, or both
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME
Start of changeThe filtering values are not case sensitive, and profiles can match regardless of the case of the input values.End of change
End of change
Start of changeSetting built-in global variables by using profile tables End 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 thresholds
  • 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

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.
Start of changeYou 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
End of change

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.

PLANNAME VARCHAR(24) The name of a plan.
COLLID VARCHAR(128) A collection identifier of a monitored collection.
Start of changeYou 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 allowed for each requester.End of change
PKGNAME VARCHAR(128) A package name of a monitored plan.
Start of changeYou 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 allowed for each requester.End of change
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.
  • The domain name of a remote client, for example: 'stlmvs1.svl.example.com'
  • One of the following values that remote clients use to connect to the server:
    • Database name.
    • Location name.
    • Location alias.
  • Start of changeAn 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.End of change
  • Start of changeAn 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.End of change
  • Start of change'0.0.0.0' or '::0' which represents any IP address.End of change

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.

Start of changeYou 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 allowed for each requester.End of change
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. .
Start of changeYou 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 allowed for each requester.End of change
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 format of product identifier values is pppvvrrm, where ppp is a 3-letter product code (such as DSN for Db2), vv is the version, rr is the release, and m is the modification level. Start of changeFor example, DSN12015 identifies Db2 12 after the activation of function level 500 or higher.End of change The product code (ppp) is one of the following values:
  • AQT for IBM Db2 Analytics Accelerator for z/OS®
  • ARI for DB2® Server for VSE & VM
  • DSN for Db2 for z/OS
  • Start of changeHTP for non-secure HTTP URL connections for Db2 native REST servicesEnd of change
  • Start of changeHTS for secure HTTPS connections for Db2 native REST servicesEnd of change
  • JCC for IBM Data Server Driver for JDBC and SQLJ
  • QSQ for DB2 for i
  • SQL for Db2 for Linux®, UNIX, and Windows
Modification (m) values have the following meanings:Start of change
Start of change0End of change
Start of changeFunction level 100 or 100*.End of change
5
Start of changeFunction level 500 or higher.End of change
End of change
Start of changeYou 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
End of change

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.

Start of changeYou 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 allowed for each requester.End of change
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.

Start of changeYou 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 allowed for each requester.End of change
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.

Start of changeYou 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 allowed for each requester.End of change
End program-specific programming interface information.