DSN_PROFILE_ATTRIBUTES profile table

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.

FL 501 Profiles that assign values to the DEADLOCK_RESOLUTION_PRIORITY built-in global variable can apply to remote applications only, local applications only, or both. Profiles for other built-in global variables apply only to remote applications. For more information, see the ATTRIBUTE2 column description.

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'
FL 501 DEADLOCK_RESOLUTION_PRIORITY	SET SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY = NULL
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

For GLOBAL_VARIABLE, species whether the profile applies to threads for remote applications, local applications, or both.

FL 501 The value must be NULL unless the ATTRIBUTE1 value is DEADLOCK_RESOLUTION_PRIORITY.

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.

1

The profile applies to local threads only. The profile is evaluated and SET statements are processed when each package is loaded.

2

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

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

MONITOR product-type CONNECTIONS FOR SECURITY

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.

The DSN_PROFILE_TABLE row must specify filtering only in the LOCATION column. The other filtering columns must contain the null value.

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

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:

• The TCPALVER subsystem parameter value is used. See TCP/IP ALREADY VERIFIED field (TCPALVER subsystem parameter).
• The ATTRIBUTE3 value is ignored.
• No profile warnings or exceptions are issued.

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 1 or 4.

6

Use basic authentication with MFA or client certificate: use the behavior of 2 or 4.

ATTRIBUTE3

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 AT-TLS policy
• Basic authentication with MFA: the connection does not require AT-TLS policy
• Client certificate: the connection must be secured with an AT-TLS policy

1

The connection must be secured with an AT-TLS policy.

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.

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

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	Issue a single DSNT773I message for a thread that remains in an idle state. 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.	DSNT771I 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.

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

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	When the number of active threads exceeds the threshold, queue and suspend subsequent threads. 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	When the number of active threads exceeds the threshold, queue and suspend subsequent threads. 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	When the number of active threads exceeds the threshold, queue and suspend subsequent threads. 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	When the number of active threads exceeds the threshold, queue and suspend subsequent threads. 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

RELEASE_PACKAGE

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

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.

FL 500 Profiles that specify SET CURRENT LOCK TIMEOUT statements can apply to remote 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.

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
FL 500 SET CURRENT LOCK TIMEOUT	The following syntax variations are not supported in profiles: WAIT integer-constant MODE TO
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.

FL 500 The value must be NULL unless the ATTRIBUTE1 value specifies a SET CURRENT LOCK TIMEOUT statement.

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.

1

The row applies to local threads only. Profiles are evaluated and SET statements are processed 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

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