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 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.

The following table 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.
Tip: Use the same PROFILEID value for any DSN_PROFILE_ATTRIBUTES rows that require the same filtering criteria. If multiple DSN_PROFILE_TABLE rows contain exactly matching filtering criteria, only the newest duplicate row is accepted when you start the profiles, and the others are rejected and disabled.
KEYWORDS VARCHAR(128) NOT NULL The action that Db2 takes for processes that meet the filtering criteria that are 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 Db2 applies when 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.

ACCEL_NAME_EXPLAIN
Specifies that a dynamic EXPLAIN statement evaluates 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.
ACCEL_NAME_EXPLAIN cannot be used for the following types of queries:
  • Static SQL queries
  • Dynamic SQL queries that were prepared with CURRENT EXPLAIN MODE = YES
ATTRIBUTE1
For ACCEL_NAME_EXPLAIN, specifies the name of a real or virtual accelerator.
ATTRIBUTE2
NULL
ATTRIBUTE3
NULL
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
For ACCEL_TABLE_THRESHOLD, a positive integer that specifies the total number of rows for a query, or -1. -1 means that this check is not used. The default is 1000000.
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
For ACCEL_RESULTSIZE_THRESHOLD, a positive integer that specifies 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 not used. -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
For ACCEL_TOTALCOST_THRESHOLD, a positive floating point value that specifies the estimated total cost for a query, or -1.0. -1.0 means that this check is not used. 5000 is the default.
BPname
Where name is any of the following values:
  • 0 - 49
  • 8K0 - 8K9
  • 16K0 - 16K9
  • 32K
  • 32K1 - 32K9
ATTRIBUTE1
NULL
ATTRIBUTE2
For BPname, 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
SHARE_LOCKS

Specifies that ATTRIBUTE1 column contains a property that applies to global transactions. The property applies only to remote applications.

ATTRIBUTE1
Assigns one or more stored procedure names to allow the sharing of locks with any transactions that it invokes in an RRS context, such as a CICS transaction through the External CICS interface (EXCI). The value must be in the following format:
PROCEDURE_LIST=procedure-name, procedure-name, ...

Each procedure-name must identify an external procedure (not an external SQL procedure), be qualified with the procedure schema, and not specify a three-part name. The length attribute of the ATTRIBUTE1 value must not exceed 1024 bytes.

ATTRIBUTE2
NULL
ATTRIBUTE3
NULL
GLOBAL_VARIABLE
Specifies that the ATTRIBUTE1 column value contains a SET statement to assign a value to a supported built-in global variable. A built-in global variable value that is changed by an SQL statement in an application takes precedence over a specification in the DSN_PROFILE_ATTRIBUTES table.

Start of changeFL 501 Profiles that assign values to the DEADLOCK_RESOLUTION_PRIORITY built-in global variable can apply to remote TCP/IP applications only, local applications only, or both. Profiles for other built-in global variables apply only to remote TCP/IP applications. For more information, see the ATTRIBUTE2 column description.End of change

ATTRIBUTE1

For GLOBAL_VARIABLE, specifies a SET statement to provide a value for a built-in global variable.

The SET statement must adhere to the rules described in SET assignment-statement. Also, the following rules apply to the ATTRIBUTE1 value for SET statements for all supported built-in global variables:

  • The schema qualifier of the variable must be specified.
  • The SET statement must have an equal sign (=) between the global variable name and the value that is assigned.
  • Variable names can be specified in mixed case. Extraneous blanks between keywords are removed.
  • The value that is specified for assignment must be valid for the variable and must not be an expression, or reference other special registers or variables.
  • The value that is specified for assignment is passed through this interface as a string constant. For example, if a value to be assigned is the same as the name of a special register, Db2 stores the special register name, and not the special register value.
  • The maximum length of the SET statement is 1024 bytes.
  • The statement must be a Unicode string and encoded with the appropriate CCSID for the application.
  • The variable is assigned the value and it is stored as specified.

You can set the following global variable values by using profile tables:

Global variable Examples
GET_ARCHIVE
SET SYSIBMADM.GET_ARCHIVE = 'Y'
Start of changeFL 501 DEADLOCK_RESOLUTION_PRIORITYEnd of change Start of change
SET SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY = NULL 
End of change
MAX_LOCKS_PER_TABLESPACE
SET SYSIBMADM.MAX_LOCKS_PER_TABLESPACE = NULL 
MAX_LOCKS_PER_USER
SET SYSIBMADM.MAX_LOCKS_PER_USER = DEFAULT
MOVE_TO_ARCHIVE
SET SYSIBMADM.MOVE_TO_ARCHIVE = 'N'
TEMPORAL_LOGICAL_TRANSACTION_TIME
SET SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME = '2014-11-14-08.05.01.123456789'
TEMPORAL_LOGICAL_TRANSACTIONS
SET SYSIBM.TEMPORAL_LOGICAL_TRANSACTIONS = 0
ATTRIBUTE2
Start of changeFor GLOBAL_VARIABLE, species whether the profile applies to threads for remote applications, local applications, or both.

Start of changeFL 501 The value must be NULL unless the ATTRIBUTE1 value is DEADLOCK_RESOLUTION_PRIORITY.End of change

NULL
The profile applies to remote threads only. The profile is evaluated and SET statements are processed only when the first package is loaded, and when the first SQL statement (other than a SET statement) in the package executes.
Start of change1End of change
Start of changeThe profile applies to local threads only. The profile is evaluated and SET statements are processed when each package is loaded.End of change
Start of change2End of change
Start of changeThe profile applies to both local and remote threads. For remote threads, the behavior is the same as if the value is NULL. For local threads, the behavior is the same as if the value is 1.End of change
End of change
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
For MAX_RIDBLOCKS, specifies 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 specifies that Db2 is uses star joins when the number of tables in the statement is equal to or greater than the specified integer.
ATTRIBUTE1
NULL
ATTRIBUTE2
When the KEYWORDS value is MIN STAR JOIN TABLES, the ATTRIBUTE2 column value is an integer in the range 3 - 225 that specifies the minimum number of tables for star join processing.
ATTRIBUTE3
NULL
MONITOR ALL CONNECTIONS

Specifies that Db2 monitors the total cumulative number of remote connections from all application servers, including active connections and live inactive connections.

If this keyword is specified, the profile-filtering criteria in the LOCATION column in the DSN_PROFILE_TABLE must contain one of the following values: '*', '::0', or '0.0.0.0'.

ATTRIBUTE1

For MONITOR ALL CONNECTIONS, specifies the action and console messages to issue when the threshold is reached for connections that match the filtering criteria that are specified in the profile.

ATTRIBUTE1 value Result for exceeded thresholds Messages issued
EXCEPTION Fail the connection request and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
EXCEPTION_ DIAGLEVEL1 Fail the connection request and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
EXCEPTION_DIAGLEVEL2 Fail the connection request and issue the console message. DSNT772I for a specific active profile, every 5 minutes at most
EXCEPTION_DIAGLEVEL3 Fail the connection request and issue the console message. DSNT774I for every exception
WARNING Allow the connection to remain open and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
WARNING_DIAGLEVEL1 Allow the connection to remain open and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
WARNING_ DIAGLEVEL2 Allow the connection to remain open and issue the console message. DSNT772I for a specific active profile, every 5 minutes at most
WARNING_ DIAGLEVEL3 Allow the connection to remain open and issue the console message. DSNT773I for every warning
ATTRIBUTE2
For MONITOR ALL CONNECTIONS, an integer that specifies the threshold for total cumulative number of remote connections allowed from all application servers. This threshold value is compared to an approximate count, which is maintained periodically by a background process, of server threads that are under the control of the default location profile.

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 not used and a message is recorded in the profile attributes history table to indicate that this row is rejected.

ATTRIBUTE3
NULL
MONITOR ALL THREADS

Specifies that Db2 monitors the total number of cumulative concurrent active threads from all application servers.

If this keyword is specified, the profile-filtering criteria LOCATION column in the DSN_PROFILE_TABLE must contain one of the following values: '*', '::0', or '0.0.0.0'.

ATTRIBUTE1
For MONITOR ALL THREADS, specifies the action and messages that are issued when the number of active server threads that match the filtering criteria of the profile reach the specified thresholds.
ATTRIBUTE1 value Result for exceeded thresholds Messages issued
EXCEPTION The thread remains queued until another server thread becomes available and issue the console message.

Threads become available when others deallocate in ACTIVE mode, or when others become inactive in INACTIVE mode.

DSNT771I for all active profiles, every 5 minutes at most
EXCEPTION_ DIAGLEVEL1 The thread remains queued until another server thread becomes available and issue the console message.

Threads become available when others deallocate in ACTIVE mode, or when others become inactive in INACTIVE mode.

DSNT771I for all active profiles, every 5 minutes at most
EXCEPTION_DIAGLEVEL2 The thread remains queued until another server thread becomes available and issue the console message.

Threads become available when others deallocate in ACTIVE mode, or when others become inactive in INACTIVE mode.

DSNT772I for a specific active profile, every 5 minutes at most
EXCEPTION_DIAGLEVEL3 The thread remains queued until another server thread becomes available and issue the console message.

Threads become available when others deallocate in ACTIVE mode, or when others become inactive in INACTIVE mode.

DSNT774I for every exception
WARNING Issue the console message. DSNT771I for all active profiles, every 5 minutes at most
WARNING_DIAGLEVEL1 Issue the console message. DSNT771I for all active profiles, every 5 minutes at most
WARNING_ DIAGLEVEL2 Issue the console message. DSNT772I for a specific active profile, every 5 minutes at most
WARNING_ DIAGLEVEL3 Issue the console message for every thread that exceeds a profile threshold. DSNT773I for every warning
ATTRIBUTE2

For MONITOR ALL THREADS, an integer that specifies the threshold for the total cumulative number of active server threads that are allowed from all application servers. This threshold value is compared to an approximate count, which is maintained periodically by a background process, of server threads that are under the control of the default location profile.

The maximum allowed value is equal to the value of the MAXDBAT subsystem parameter. For more information, see MAX REMOTE ACTIVE field (MAXDBAT subsystem parameter).

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

ATTRIBUTE3
NULL
MONITOR CONNECTIONS

Specifies that Db2 monitors the total number of remote connections from each application server, including active connections and live inactive connections.

The connections are filtered only according to the IP address, or domain name that resolves to an IP address, 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
For MONITOR CONNECTIONS, specifies the action and console messages to issue when the threshold is reached for threads that match the filtering criteria that are specified in the profile.
ATTRIBUTE1 value Result for exceeded thresholds Messages issued
EXCEPTION Fail the connection request and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
EXCEPTION_ DIAGLEVEL1 Fail the connection request and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
EXCEPTION_DIAGLEVEL2 Fail the connection request and issue the console message. DSNT772I for a specific active profile, every 5 minutes at most
EXCEPTION_DIAGLEVEL3 Fail the connection request and issue the console message. DSNT774I for every exception
WARNING Allow the connection to remain open and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
WARNING_DIAGLEVEL1 Allow the connection to remain open and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
WARNING_ DIAGLEVEL2 Allow the connection to remain open, and issue the console message. DSNT772I for a specific active profile, every 5 minutes at most
WARNING_ DIAGLEVEL3 Allow the connection to remain open and issue the console message for every thread that exceeds a profile threshold. DSNT773I for every warning
ATTRIBUTE2
For MONITOR CONNECTIONS, an integer that specifies the threshold of the total number of remote connections that are allowed from each application server.

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

A negative number deactivates this monitor function, and a message is recorded in the profile attributes history table to indicate that the row is rejected.

ATTRIBUTE3
NULL
Start of changeMONITOR product-type CONNECTIONS FOR SECURITYEnd of change
Start of change

Specifies that Db2 monitors all connections for the specified product type for the desired authentication mechanisms and encryption requirements.

MONITOR product-type CONNECTIONS FOR SECURITY validation occurs when new connections are made; they do not occur when existing connections are reused.

Profiles defined for this keyword apply regardless of whether a connection can be matched to a more specific profile.

When multiple matching security profiles are defined, the most specific profile filter applicable for the connection’s product type is used.

Start of changeThe DSN_PROFILE_TABLE row must specify filtering only in the LOCATION column. The other filtering columns must contain the null value.End of change

product-type is one of the following values:
  • REST
  • JDBC
  • CLI
  • DB2CONNECT
  • DSN
  • * (the asterisk is a wildcard that applies for application requesters that do not match any of the more specific product types above)
ATTRIBUTE1
Specifies the action and console message to issue when the desired authentication mechanisms and encryption requirements are not met for connections with the product type specified in the profile, as shown in the following table.
ATTRIBUTE1 value Result for exceeded thresholds Messages issued
EXCEPTION Fail the connection request and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
EXCEPTION_ DIAGLEVEL1 Fail the connection request and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
EXCEPTION_DIAGLEVEL2 Fail the connection request and issue the console message. DSNT772I for a specific active profile, every 5 minutes at most
EXCEPTION_DIAGLEVEL3 Fail the connection request and issue the console message. DSNT776I for every exception
WARNING Allow the connection to remain open and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
WARNING_DIAGLEVEL1 Allow the connection to remain open and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
WARNING_ DIAGLEVEL2 Allow the connection to remain open and issue the console message. DSNT772I for a specific active profile, every 5 minutes at most
WARNING_ DIAGLEVEL3 Allow the connection to remain open and issue the console message. DSNT775I for every warning
ATTRIBUTE2
FL 505 In the ATTRIBUTE2 column, specify the desired authentication mechanism.
NULL
Honor the value of the TCP/IP ALREADY VERIFIED field. This option is mutually exclusive with all other values. When this value is specified:
1
Use basic authentication: A user ID and password or passphrase for authentication.
2
Use a basic authentication with multifactor authentication (MFA).
4
Use a client certificate for authorization. When this value is specified:
  • The connection must be secured with an AT-TLS policy.
  • The ATTRIBUTE3 value is ignored.
5
Use basic authentication or client certificate: Use the behavior of value 1 or 4.
6
Use basic authentication with MFA or client certificate: Use the behavior of value 2 or 4.
Start of change8End of change
Start of changeAuthentication token: Use RACF®-generated token (IDT) for authentication.End of change
Start of change10End of change
Start of changeBasic authentication with MFA or authentication token: Use the behavior of value 2 or 8.End of change
Start of change12End of change
Start of changeClient certificate or authentication token: Use the behavior of value 4 or 8.End of change
Start of change14End of change
Start of changeBasic authentication with MFA, client certificate, or authentication token: Use the behavior of value 2, 4, or 8.End of change
ATTRIBUTE3
FL 505 In the ATTRIBUTE3 column, specify whether the connection must be secured with an AT-TLS policy.
NULL
Use the following default AT-TLS policy behavior for the authentication mechanism specified in ATTRIBUTE2:
  • Basic authentication: The connection does not require an AT-TLS policy.
  • Basic authentication with MFA: The connection does not require an AT-TLS policy.
  • Client certificate: The connection must be secured with an AT-TLS policy.
  • Start of changeAuthentication token: The connection does not require an AT-TLS policy.End of change
1
The connection must be secured with an AT-TLS policy.
End of change
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. A negative number deactivates this monitor function, and a message is recorded in the profile attributes history table to indicate that the row is rejected.

Start of changeThreads are checked every two minutes to see if they have exceeded the timeout value. If the timeout value is less than two minutes, the thread might not be canceled if it has been inactive for more than the timeout value but less than two minutes.End of change

ATTRIBUTE1
For MONITOR IDLE THREADS, specifies the type and level of detail for messages that are issued for monitored idle threads that meet the conditions that are specified in the profile.
ATTRIBUTE1 value Result for exceeded thresholds Messages issued
EXCEPTION Abort the thread, pool the DBAT, terminate the connection, and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
EXCEPTION_ DIAGLEVEL1 Abort the thread, pool the DBAT, terminate the connection, and issue the console message. DSNT771I for all active profiles, every 5 minutes at most
EXCEPTION_DIAGLEVEL2 Abort the thread, pool the DBAT, terminate the connection, and issue the console message. DSNT772I for a specific active profile, every 5 minutes at most
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 for all active profiles, every 5 minutes at most
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 for all active profiles, every 5 minutes at most
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 for a specific active profile, every 5 minutes at most
WARNING Issue the console message one time every 5 minutes at most, and allow the thread to remain idle. 2 DSNT771I for all active profiles, every 5 minutes at most
WARNING_DIAGLEVEL1 Issue the console message one time every 5 minutes at most, and allow the thread to remain idle. 2 DSNT771I for all active profiles, every 5 minutes at most
WARNING_ DIAGLEVEL2 Issue the console message, and allow the thread to remain idle. 2 DSNT773I for every warning
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 for every warning
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 that is 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 that is specified by the IDTHTOIN subsystem parameter setting does not apply to threads that match the filtering criteria of a MONITOR IDLE THREADS profile. So, 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

For MONITOR IDLE THREADS, an integer that specifies the threshold (in seconds) that a thread is allowed to remain idle.

Start of changeThreads are checked every two minutes to see if they have exceeded the timeout value. If the timeout value is less than two minutes, the thread might not be canceled if it has been inactive for more than the timeout value but less than two minutes.End of change

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. So 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 deactivates this monitor function, 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 for each remote application that qualifies for the filtering criteria of the profile.

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 queued or suspended threads reaches the specified threshold, Db2 issues a message if a WARNING value is specified, or starts failing the connection requests with SQLCODE -30041 if an EXCEPTION value is specified.

ATTRIBUTE1
For MONITOR THREADS, specifies the action and messages that are issued when the threads that match the filtering criteria of the profile reach the specified thresholds.
ATTRIBUTE1 value Result for exceeded thresholds Messages issued
EXCEPTION
  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 that is associated that is with the threads is not terminated and remains open.

DSNT771I for all active profiles, every 5 minutes at most
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.

Connections that are associated with the queued or suspended threads are not terminated and remain open.

DSNT771I for all active profiles, every 5 minutes at most
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.

Connections that are associated with the queued or suspended threads are not terminated and remain open.

DSNT772I for a specific active profile, every 5 minutes at most
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.

Connections that are associated with the queued or suspended threads are not terminated and remain open.

DSNT774I for every exception
WARNING Issue the console message. DSNT771I for all active profiles, every 5 minutes at most
WARNING_DIAGLEVEL1 Issue the console message. DSNT771I for all active profiles, every 5 minutes at most
WARNING_ DIAGLEVEL2 Issue the console message. DSNT772I for a specific active profile, every 5 minutes at most
WARNING_ DIAGLEVEL3 Issue the console message. DSNT773I for every warning

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

Table 2. Actions for EXCEPTION profiles for remote threads
Filtering category Action
IP address or fully qualified 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

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

For 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.

ATTRIBUTE2
For MONITOR THREADS, an integer that specifies the threshold of the total number of active server threads that are allowed from each remote application.

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

A negative number deactivates this monitor function, and a message is recorded in the profile attributes history table to indicate that the row is rejected.

ATTRIBUTE3
For MONITOR THREADS, specifies the threshold for the maximum number of server threads that are allowed to be suspended the profile criteria. The value must be a whole number, less than or equal to the ATTRIBUTE2 column.
NPAGES THRESHOLD

Specifies that Db2 is to set the pages threshold for index access (NPGTHRSH subsystem parameter).

ATTRIBUTE1
NULL
ATTRIBUTE2
For NPAGES THRESHOLD, an integer 0 or greater that specifies the pages threshold for index access.The default value is 1. 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.
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.
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
For SORT_POOL_SIZE, 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 changeRELEASE_PACKAGEEnd of change
Start of change

FL 500 Specifies an override of the RELEASE bind option for newly loaded packages. Additionally, for packages that are already loaded when the -START PROFILE command is issued, the release behavior is overridden at the end of a transaction. COMMIT is the only valid value for the ATTRIBUTE1 column.

Profiles for the RELEASE_PACKAGE keyword can apply to local applications only, remote applications only, or both. For more information, see the description of the ATTRIBUTE2 column.

ATTRIBUTE1
COMMIT is the only valid value, and it specifies that Db2 uses release commit behavior regardless of value that is specified when a package was bound.
ATTRIBUTE2
For RELEASE_PACKAGE, a value that indicates whether the attribute applies to local or remote threads.
NULL
The row applies to remote threads only. The profile is processed when each package is loaded.
1
The row applies to local threads only. The profile is evaluated when each package is loaded.
2
The row applies to local and remote threads. For remote threads, the behavior is the same as if the value is NULL. For local threads, the behavior is the same as if the value is 1.
ATTRIBUTE3
NULL
End of change
SPECIAL_REGISTER

Specifies that the ATTRIBUTE1 column contains a SET special register assignment statement to assign a value to a supported special register.

A special register value that is changed by an SQL statement in an application takes precedence over a specification in the DSN_PROFILE_ATTRIBUTES table.

Start of changeFL 500 Profiles that specify SET CURRENT LOCK TIMEOUT statements can apply to remote TCP/IP applications only, local applications only, or both. Profiles for other special registers apply only to remote applications. For more information, see the ATTRIBUTE2 column description. End of change

ATTRIBUTE1
For SPECIAL_REGISTER, specifies a SET statement that assigns a value to a special register. The column value consists of a string that contains the SET keyword, the name of the special register, an equal sign, and the value to be assigned.

The SET statement must follow the rules for the SET special register statement. Also, the following rules apply to the ATTRIBUTE1 value for SET statements for all supported special registers:

  • An equal sign (=) must be specified between the special register name and the value that is assigned.
  • The value that is specified for assignment must be valid for the special register.
  • The value must not be an expression, or reference other special registers or variables, unless the statement is SET CURRENT PACKAGE PATH.
  • The value that is specified for assignment is passed through this interface as a literal string, unless the statement is SET CURRENT PACKAGE PATH. For example, if a value to be assigned is the same as the name of a special register, Db2 stores the special register name, and not the special register value.
  • The maximum length of a SET statement is 1024 bytes.
  • The statement must be a Unicode string and encoded with the appropriate CCSID for the application.

The following table lists the SET statements that can be specified in a profile and considerations for specific special registers.

SET special register statement Profile-specific considerations
SET CURRENT ACCELERATOR  
SET CURRENT APPLICATION COMPATIBILITY  
SET CURRENT DEBUG MODE  
SET CURRENT DECFLOAT ROUNDING MODE  
SET CURRENT DEGREE  
SET CURRENT EXPLAIN MODE  
SET CURRENT GET_ACCEL_ARCHIVE  
SET CURRENT LOCALE LC_CTYPE The following syntax variations are not supported in profiles:
  • CURRENT LC_CTYPE
  • CURRENT_LC_CTYPE
Start of changeFL 500 SET CURRENT LOCK TIMEOUTEnd of change Start of changeThe following syntax variations are not supported in profiles:
  • WAIT integer-constant
  • MODE
  • TO
End of change
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION The following syntax variations are not supported in profiles:
  • CURRENT MAINTAINED TYPES
  • CURRENT MAINTAINED TYPES FOR OPTIMIZATION
SET CURRENT OPTIMIZATION HINT  
SET CURRENT PACKAGE PATH  
SET CURRENT PRECISION  
SET CURRENT QUERY ACCELERATION  
SET CURRENT QUERY ACCELERATION WAITFORDATA  
SET CURRENT REFRESH AGE The value 99999999999999 is not supported.

Use the value ANY instead.

SET CURRENT ROUTINE VERSION  
SET CURRENT RULES  
SET CURRENT SQLID 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.

SET CURRENT TEMPORAL BUSINESS_TIME  
SET CURRENT TEMPORAL SYSTEM_TIME  
SET ENCRYPTION PASSWORD  
SET PATH  
SET SCHEMA The following syntax variations are not supported in profiles:
  • CURRENT_SCHEMA (with underscore)
SET SESSION TIME ZONE The following syntax variations are not supported in profiles:
  • TIMEZONE
  • TIME ZONE
  • SESSION TIMEZONE
ATTRIBUTE2
For SPECIAL_REGISTER, specifies whether the profile applies to threads for remote applications, local applications, or both.

Start of changeFL 500 The value must be NULL unless the ATTRIBUTE1 value specifies a SET CURRENT LOCK TIMEOUT statement.End of change

NULL
The profile applies to remote threads only. The profile is evaluated and SET statements are processed only when the first package is loaded, and when the first SQL statement (other than a SET statement) in the package executes.
Start of change1End of change
Start of changeThe row applies to local threads only. Profiles are evaluated and SET statements are processed when each package is loaded.End of change
Start of change2End of change
Start of changeThe row applies to local and remote threads. For remote threads, the behavior is the same as if the value is NULL. For local threads, the behavior is the same as if the value is 1.End of change
ATTRIBUTE3
NULL
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
For STAR JOIN, 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.