DSN_PROFILE_ATTRIBUTES profile table

The profile attributes table, DSN_PROFILE_ATTRIBUTES, defines the attributes that are associated with each profile. It contains one or more rows for each DSN_PROFILE_TABLE row. The schema is SYSIBM.

Begin program-specific programming interface information. For an overview of how to use profile tables and a summary of the specific uses for profile tables, see Monitoring and controlling Db2 with profile tables.

The profile tables and related indexes are created when you run job DSNTIJSG during Db2 installation, as described in Job DSNTIJSG.

The following tables describes the columns in the SYSIBM.DSN_PROFILE_ATTRIBUTES table.

Table 1. SYSIBM.DSN_PROFILE_ATTRIBUTES description
Column name Data type Description
PROFILEID INTEGER NOT NULL FOREIGN KEY REFERENCES SYSIBM.DSN_PROFILE_TABLE ON DELETE CASCADE The identifier for the profile. This value identifies DSN_PROFILE_TABLE row that specifies the filtering criteria for the profile.
KEYWORDS VARCHAR(128) NOT NULL The function action that Db2 takes for processes that meeting that filtering criteria specified in the profile. For a list of the supported values, see KEYWORDS and ATTRIBUTEn values for SYSIBM.DSN_PROFILE_ATTRIBUTES.
ATTRIBUTE1 VARCHAR(1024) A string attribute that is associated with the function in the KEYWORDS column, if any.
ATTRIBUTE2 INTEGER An integer attribute that is associated with the function in the KEYWORDS column, if any.
ATTRIBUTE3 FLOAT A float attribute that is associated with the function in the KEYWORDS column, if any.
ATTRIBUTE_
TIMESTAMP
TIMESTAMP NOT NULL WITH DEFAULT The time when the row was inserted or updated.
REMARKS VARCHAR(762) Comments for this row.

KEYWORDS and ATTRIBUTEn values for SYSIBM.DSN_PROFILE_ATTRIBUTES

Function keywords specify the action that you want Db2 to apply when the attributes of a process, such as a query, or remote thread or connection, meets the filtering criteria specified the related SYSIBM.DSN_PROFILE_TABLE row. Each keyword uses as many as three attributes that control how the specified action is applied by Db2. These values are specified in the KEYWORDS, ATTRIBUTE1, ATTRIBUTE2, and ATTRIBUTE3 columns of the SYSIBM.DSN_PROFILE_ATTRIBUTES table.

Start of changeACCEL_NAME_EXPLAINEnd of change
Start of changeSpecifies that a dynamic EXPLAIN statement will evaluate a dynamic SQL query for acceleration to the accelerator that is defined in ATTRIBUTE1. When the CURRENT EXPLAIN MODE special register is set to EXPLAIN, the query is evaluated for acceleration during the prepare phase of a dynamic SQL query.
ATTRIBUTE1
The name of a real or virtual accelerator.

ACCEL_NAME_EXPLAIN cannot be used for the following queries:

  • Static SQL queries
  • Dynamic SQL queries that were prepared when using CURRENT EXPLAIN MODE = YES
End of change
ACCEL_TABLE_THRESHOLD
Specifies one of the criteria that Db2 uses to determine whether to send a query to an accelerator server. ACCEL_TABLE_THRESHOLD specifies the maximum total table cardinality for a query to be treated as a short running query. If a query has a total table cardinality that is less than ACCEL_TABLE_THRESHOLD, that query is not accelerated.

If you use an accelerator product, contact IBM® Support for that accelerator product for information about how to set this keyword.

ATTRIBUTE1
NULL
ATTRIBUTE2
A positive integer that represents the total number of rows for a query, or -1. -1 means that this check is disabled. 1000000 is the default.
ATTRIBUTE3
NULL
ACCEL_RESULTSIZE_THRESHOLD
Specifies one of the criteria that Db2 uses to determine whether to send a query to an accelerator server. ACCEL_RESULTSIZE_THRESHOLD specifies the maximum number of rows that a query that is sent to an accelerator server can return. If a query returns over that number of rows, the query is not sent to an accelerator server.

If you use an accelerator product, contact IBM Support for that accelerator product for information about how to set this keyword.

ATTRIBUTE1
NULL
ATTRIBUTE2
A positive integer that represents a number of rows, in thousands, or -1. For example, a value of 20 means 20000 rows. -1 means that result set size checking is disabled. -1 is the default.
ATTRIBUTE3
NULL
ACCEL_TOTALCOST_THRESHOLD
Specifies one of the criteria that Db2 uses to determine whether to send a query to an accelerator server. ACCEL_TOTALCOST_THRESHOLD specifies the maximum estimated total cost for a query to be treated as a short running query. If a query has a total cost that is less than ACCEL_TOTALCOST_THRESHOLD, that query is not accelerated.

If you use an accelerator product, contact IBM Support for that accelerator product for information about how to set this keyword.

ATTRIBUTE1
NULL
ATTRIBUTE2
NULL
ATTRIBUTE3
A positive floating point value that represents the estimated total cost for a query, or -1.0. -1.0 means that this check is disabled. 5000 is the default.
BPname
Where name is any of the following values:
  • 0 through 49
  • 8K0 through 8K9
  • 16K0 through 16K9
  • 32K
  • 32K1 through 32K9
ATTRIBUTE1
NULL
ATTRIBUTE2
A positive integer that specifies the size of the corresponding buffer pool that the test system uses to model the configuration of a production system. When a value of zero or a negative number is specified, the profile attribute entry is rejected.
ATTRIBUTE3
NULL
MAX_RIDBLOCKS
Specifies the maximum number of RID blocks per RID pool that a test system uses to model a production system. The attribute value for this keyword corresponds to the value of the MAXRBLK subsystem parameter of the production system. To determine the appropriate value to specify, refer to the value of the PLAN_TABLE.MAX_RIDBLOCKS_EXP column in the EXPLAIN output on the production system.
ATTRIBUTE1
NULL
ATTRIBUTE2
0 (zero) or a positive integer that is valid for the value of the MAXRBLK subsystem parameter.
ATTRIBUTE3
NULL
MIN STAR JOIN TABLES
Specifies that Db2 is to set the minimum number of tables for star join processing (the SJTABLES subsystem parameter) to the value that is specified for the ATTRIBUTE1 column. This value indicates that Db2 is to use star joins when the number of tables in the statement is equal to or greater than the specified integer.
ATTRIBUTE1
NULL
ATTRIBUTE2
An integer between 3 and 225 that specifies the minimum number of tables for star join processing.
ATTRIBUTE3
NULL
MONITOR CONNECTIONS
Specifies that Db2 monitors the total number of remote connections from application servers, including active connections and live inactive connections.

The connections are filtered only according to the IP address or domain name value that is specified in the LOCATION column of the DSN_PROFILE_TABLE table.

Db2 takes certain actions when the threshold is exceeded, according to the value that is specified in the ATTRIBUTE1 column. When the total number of connections being queued or suspended reaches the exception threshold, Db2 either issues a message if a WARNING value is specified or starts to fail the connection request with SQLCODE -30041 if EXCEPTION is specified.

ATTRIBUTE1
Specifies the action and console messages to issue when the threshold is reached for threads that match the filtering criteria specified in the profile, as shown in the following table.
ATTRIBUTE1 value Action when exceeding thresholds Messages issued
EXCEPTION Fail the connection request and issue the console message. DSNT771I
EXCEPTION_ DIAGLEVEL1 Fail the connection request and issue the console message. DSNT771I
EXCEPTION_DIAGLEVEL2 Fail the connection request and issue the console message. DSNT772I
EXCEPTION_DIAGLEVEL3 Fail the connection request and issue the console message. DSNT774I
WARNING Allow the connection to remain open, and issue the console message one time every 5 minutes at most. DSNT771I
WARNING_DIAGLEVEL1 Allow the connection to remain open, and issue the console message one time every 5 minutes at most. DSNT771I
WARNING_ DIAGLEVEL2 Allow the connection to remain open, and issue the console message one time every 5 minutes at most. DSNT772I
WARNING_ DIAGLEVEL3 Allow the connection to remain open, and issue the console message for every thread that exceeds a profile threshold. DSNT773I
ATTRIBUTE2
An integer value that indicates the threshold of the total number of remote connections that is allowed.

The maximum allowed value is equal to the value of the CONDBAT subsystem parameter. See MAX REMOTE CONNECTED field (CONDBAT subsystem parameter).

When the specified value is a negative number, this monitor function is disabled and a message is recorded in the profile attributes history table to indicate that this row is rejected.

ATTRIBUTE3
NULL
MONITOR IDLE THREADS
Specifies that Db2 monitors the approximate time (in seconds) that an active server thread is allowed to remain idle.

When the specified value is zero, threads are allowed to remain idle indefinitely. When the specified value is a negative number, this monitor function is disabled and a message is recorded in the profile attributes history table to indicate that the row is rejected.

ATTRIBUTE1
Specifies the type and level of detail for messages issued for monitored idle threads that meet the conditions specified in the profile, as shown in the following table.
ATTRIBUTE1 value Action when exceeding thresholds Messages issued
EXCEPTION Abort the thread, pool the DBAT, terminate the connection, and issue the console message. DSNT771I
EXCEPTION_ DIAGLEVEL1 Abort the thread, pool the DBAT, terminate the connection, and issue the console message. DSNT771I
EXCEPTION_DIAGLEVEL2 Abort the thread, pool the DBAT, terminate the connection, and issue the console message. DSNT772I
EXCEPTION_ROLLBACK Abort any active threads that remain idle longer than the specified threshold maintain the connection with the remote application, and issue the console message.

If the aborted transaction updated the database, the application environment is placed in a must-abort state so that the application environment is notified that resources were destroyed as a result of the aborted threads. 1

DSNT771I
EXCEPTION_ROLLBACK_DIAGLEVEL1 Abort any active threads that remain idle longer than the specified threshold and maintain the connection with the remote application, and issue the console message.

If the aborted transaction updated the database, the application environment is placed in a must-abort state so that the application environment is notified that resources were destroyed as a result of the aborted threads. 1

DSNT771I
EXCEPTION_ROLLBACK_DIAGLEVEL2 Abort any active threads that remain idle longer than the specified threshold and maintain the connection with the application environment.

If the aborted transaction updated the database, the application environment is placed in a must-abort state so that the application environment is notified that resources were destroyed as a result of the aborted threads. 1

DSNT772I
WARNING Issue the console message one time every 5 minutes at most, and allow the thread to remain idle. 2 DSNT771I
WARNING_DIAGLEVEL1 Issue the console message one time every 5 minutes at most, and allow the thread to remain idle. 2 DSNT771I
WARNING_ DIAGLEVEL2 Issue the console message one time every 5 minutes at most, and allow the thread to remain idle. 2 DSNT773I
WARNING_MESSAGE_FOR_IDLE_TIMEOUT
  1. Issue a single DSNT773I message for a thread that remains in an idle state. 2
  2. When a client request message is received, and a COMMIT or ROLLBACK is completed with no resources active past the end of the unit-of-work, remove the warning against the thread.
DSNT773I
Notes:
  1. If either of the following situations are true, Db2 hides the EXCEPTION_ROLLBACK event from the remote application environment:
    • The aborted transaction performed only read-only operations.
    • The transaction committed or aborted, but the associated database access thread remained active before it became idle.

    Because the EXCEPTION_ROLLBACK event is not visible to the remote application environment in those cases, it might appear to the remote application environment that the database resources are unexpectedly disappearing. For example, held cursors, kept dynamic statements, and declared temporary tables that were created in the transaction are destroyed. Use EXCEPTION_ROLLBACK only if the remote application environment can account for this apparent loss of database resources.

  2. The timeout limit specified by the IDTHTOIN subsystem parameter setting does not apply to threads that match the filtering criteria of a MONITOR IDLE THREADS profile. Consequently, threads that qualify for a MONITOR IDLE THREADS profile with a WARNING value in the ATTIRBUTE1 column can remain idle indefinitely. To prevent that situation, you can add another DSN_PROFILE_ATTRIBUTES row with EXCEPTION value in the ATTRIBUTES1 column and same the same PROFILEID value.
ATTRIBUTE2
An integer value that indicates the threshold (in seconds) that a thread is allowed to remain idle. It can be any value that is valid for the IDTHTOIN subsystem parameter. See IDLE THREAD TIMEOUT field (IDTHTOIN subsystem parameter).

Threads that meet the criteria of this type of profile are not limited by the value that is specified by the IDTHTOIN subsystem parameter. Consequently you can use MONITOR IDLE THREADS to enable longer idle wait times for certain threads, without increasing the system-wide limit for idle thread timeouts.

A zero value means that matching threads are allowed to remain idle indefinitely. When a negative number is specified, this monitor function is disabled and a message is recorded in the profile attributes history table to indicate that this row is rejected.

ATTRIBUTE3
NULL
MONITOR THREADS
Specifies that Db2 monitors the total number of concurrent active threads.

Db2 takes certain actions when the threshold is reached according to the value that is specified in the ATTRIBUTE1 column. When the total number of threads being queued or suspended reaches the specified threshold, Db2 either issues a message, if a WARNING value is specified, or starts to fail the connection requests with SQLCODE -30041 if an EXCEPTION value is specified.

ATTRIBUTE1
Specifies the action and messages issued when the threads that match the filtering criteria of the profile reach the specified thresholds, as shown in the following table.
ATTRIBUTE1 value Action when exceeding thresholds Messages issued
EXCEPTION
  1. When the number of active threads exceeds the threshold, queues and suspend subsequent threads.
  2. When the number of queued threads exceeds the threshold, the action depends on the filtering scope, as shown in Table 2.

The connection associated the threads is not terminated and remains open.

DSNT771I
EXCEPTION_ DIAGLEVEL1
  1. When the number of active threads exceeds the threshold, queue and suspend subsequent threads.
  2. When the number of queued threads exceeds the threshold, the action depends on the filtering scope, as shown in Table 2.

The connection associated the threads is not terminated and remains open.

DSNT771I
EXCEPTION_DIAGLEVEL2
  1. When the number of active threads exceeds the threshold, queue and suspend subsequent threads.
  2. When the number of queued threads exceeds the threshold, the action depends on the filtering scope, as shown in Table 2.

The connection associated the threads is not terminated and remains open.

DSNT772I
EXCEPTION_DIAGLEVEL3
  1. When the number of active threads exceeds the threshold, queue and suspend subsequent threads.
  2. When the number of queued threads exceeds the threshold, the action depends on the filtering scope, as shown in Table 2.

The connection associated the threads is not terminated and remains open.

DSNT774I
WARNING Issue the console message, at most every 5 minutes. DSNT771I
WARNING_DIAGLEVEL1 Issue the console message, at most every 5 minutes. DSNT771I
WARNING_ DIAGLEVEL2 Issue the console message, at most every 5 minutes. DSNT772I
WARNING_ DIAGLEVEL3 Issue the console message for every thread that exceeds a profile threshold. DSNT773I

The following table summarizes the filtering actions taken for different filtering categories.

Table 2. Actions for EXCEPTION profiles for remote threads
Filtering category Action
IP Address or domain name The thread remains queued until another server thread becomes available. Threads become available when others deallocate in ACTIVE mode, or when others become inactive in INACTIVE mode.
Product identifier, role, authorization identifier, or server location name

When the total number of queued and suspended threads exceeds the threshold, the Db2 server fails subsequent connection requests and returns SQLCODE -30041 to the client.

Collection identifier, package name, client user name, client application name, or client workstation name

Start of changeWhen the total number of queued and suspended threads exceeds the threshold, Db2 fails subsequent SQL statements and returns SQLCODE -30041 to the client. End of change

Start of changeFor example, suppose that a profile for a package is started. That profile uses ATTRIBUTE2=2. If five threads request to run the package, two threads run concurrently, two threads are queued and suspended, and Db2 fails the SQL statements for the fifth thread.End of change

ATTRIBUTE2
An integer value that indicates that threshold of the total number of active server threads that is allowed.

The maximum allowed value is equal to the value of the MAXDBAT subsystem parameter. See MAX REMOTE ACTIVE field (MAXDBAT subsystem parameter).

When the specified value is a negative number, this monitor function is disabled and a message is recorded in the profile attributes history table to indicate that this row is rejected.

ATTRIBUTE3
NULL
NPAGES THRESHOLD
Specifies that Db2 is to set the pages threshold (NPGTHRSH subsystem parameter) to the value that is specified for the ATTRIBUTE2 column. This value indicates when Db2 should use index access. You can specify one of the following values:
-1
Db2 uses index access for as many tables as possible.
0
Db2 selects the access path based on the cost. This value is the default.
1 - n
Db2 uses index access on tables for which the total number of pages (NPAGES) is less than n. Ensure that statistics are up to date before you specify a value of 1 or greater.
ATTRIBUTE1
NULL
ATTRIBUTE2
An integer 0 or greater
ATTRIBUTE3
NULL
SORT_POOL_SIZE
Specifies the sort pool size that a test system uses to model a production system. This attribute value for this keyword corresponds to the value of the SRTPOOL subsystem parameter of the production system. To determine the appropriate value to specify, refer to the SORTL_POOL_SIZE_EXP value on the production system.
ATTRIBUTE1
NULL
ATTRIBUTE2
A positive integer that is valid for the value SRTPOOL subsystem parameter. When 0 (zero) or a negative number is specified, the profile attribute entry is rejected. When a positive integer that is invalid for the SRTPOOL subsystem parameter is specified, the profile attribute entry is accepted. However, the access paths of SQL statements that are shown in the EXPLAIN tables on the test subsystem might not match the access paths on the production system that is modeled.
ATTRIBUTE3
NULL
Start of changeSPECIAL_REGISTEREnd of change
Start of changeSpecifies the SET special register assignment statement for remote connections. A special register value that is changed by an SQL statement in an application takes precedence over the SPECIAL_REGISTER profile keyword.
ATTRIBUTE1
Specifies a SET statement that assigns a value to a special register. The value consists of a string that contains the SET keyword, the name of the special register to be set, an equal sign, and the value to be set. SET statements can be specified for the following Db2 special registers :
  • Start of changeCURRENT ACCELERATOREnd of change
  • CURRENT APPLICATION COMPATIBILITY
  • CURRENT DEBUG MODE
  • CURRENT DECFLOAT ROUNDING MODE
  • CURRENT DEGREE
  • CURRENT EXPLAIN MODE
  • CURRENT GET_ACCEL_ARCHIVE
  • CURRENT FUNCTION PATH
  • CURRENT LOCALE LC_CTYPE
  • CURRENT MAINTAINED TABLE TYPES
  • CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
  • CURRENT OPTIMIZATION HINT
  • CURRENT PACKAGE PATH
  • CURRENT PATH
  • CURRENT PRECISION
  • CURRENT QUERY ACCELERATION
  • CURRENT REFRESH AGE
  • CURRENT ROUTINE VERSION
  • CURRENT RULES
  • CURRENT SCHEMA
  • CURRENT SQLID1
  • CURRENT TEMPORAL BUSINESS_TIME
  • CURRENT TEMPORAL SYSTEM_TIME
  • ENCRYPTION PASSWORD
  • PATH
  • SCHEMA
  • SESSION TIME ZONE
  • TIME ZONE

The following rules apply to the ATTRIBUTE1 value for SET statements for special registers:

  • The SET statement must have an equal sign (=) between the special register name and the value that is assigned.
  • The maximum length of the SET statement is 1024 bytes.
  • The special register names are allowed to be in mixed case. Extraneous blanks between keywords are removed.
  • The special register assigned value is stored as specified. The value must be valid for the special register.
  • Special register values cannot be expressions, cannot reference other special registers, and cannot reference global variables.
  • For CURRENT REFRESH AGE, the value 99999999999999 is not supported. Use the value ANY instead.
  • Db2 treats all special register values that are passed through this interface as literal strings. For example, if a specified special register value is the same as the name of a special register, Db2 stores the special register name, and not the special register value.
  • The SET statement must be a Unicode string and encoded with the appropriate CCSID for the application.
ATTRIBUTE2
NULL
ATTRIBUTE3
NULL
End of change
STAR JOIN
Specifies whether Db2 is to use star join processing (the STARJOIN subsystem parameter). You can specify one of the following values for ATTRIBUTE1:
ATTRIBUTE1
One of the following values:
DISABLE
Db2 is not to use star join processing.
ENABLE
Db2 is to use star join processing when possible.
ATTRIBUTE2
NULL
ATTRIBUTE3
NULL
End program-specific programming interface information.
1 If the SET CURRENT SQLID statement is run in a stored procedure or user-defined function package that has a dynamic SQL behavior other than run behavior, the SET CURRENT SQLID statement does not affect the authorization ID that is used for dynamic SQL statements in the package. The dynamic SQL behavior determines the authorization ID. For more information, see DYNAMICRULES bind option.