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 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.
KEYWORDS VARCHAR(128) NOT NULL The action that Db2 takes for processes that meet the 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.

ACCEL_NAME_EXPLAIN
Specifies 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.
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 disabled. 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 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
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 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
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
Start of changeGLOBAL_VARIABLEEnd of change
Start of changeSpecifies that the ATTRIBUTE1 column value contains a SET statement to assign a value to a supported built-in global variable.

Setting a built-in global variable in the profile table applies only to remote applications.

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 specified for assignment must be valid for the variable and must not be an expression, or reference other special registers or variables.
  • The value specified for assignment is passed through this interface as a literal string. 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.
Global variable Considerations and examples
GET_ARCHIVE Example:
SET SYSIBMADM.GET_ARCHIVE = 'Y'
Start of changeFL 507 MAX_LOCKS_PER_TABLESPACEEnd of change Start of changeExample:
SET SYSIBMADM.MAX_LOCKS_PER_TABLESPACE = NULL 
End of change
Start of changeFL 507 MAX_LOCKS_PER_USEREnd of change Start of changeExample:
SET SYSIBMADM.MAX_LOCKS_PER_USER = DEFAULT
End of change
MOVE_TO_ARCHIVE Example:
SET SYSIBMADM.MOVE_TO_ARCHIVE = 'N'
TEMPORAL_LOGICAL_TRANSACTION_TIME Example:
SET SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME = '2014-11-14-08.05.01.123456789'
TEMPORAL_LOGICAL_TRANSACTIONS Example:
SET SYSIBM.TEMPORAL_LOGICAL_TRANSACTIONS = 0
ATTRIBUTE2
NULL
ATTRIBUTE3
NULL
End of change
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 between 3 and 225 that specifies the minimum number of tables for star join processing.
ATTRIBUTE3
NULL
Start of changeMONITOR ALL CONNECTIONSEnd of change
Start of changeSpecifies 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 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 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. Issue the console message, at most every 5 minutes. DSNT771I
EXCEPTION_ DIAGLEVEL1 Fail the connection request and issue the console message. Issue the console message, at most every 5 minutes. DSNT771I
EXCEPTION_DIAGLEVEL2 Fail the connection request and issue the console message. Issue the console message, at most every 5 minutes. 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
For MONITOR ALL CONNECTIONS, an integer that specifies the threshold fortotal 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 disabled and a message is recorded in the profile attributes history table to indicate that this row is rejected.

ATTRIBUTE3
NULL
End of change
Start of changeMONITOR ALL THREADSEnd of change
Start of changeSpecifies 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 issued when the number of active server 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 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. Issue the console message, at most every 5 minutes. DSNT771I
EXCEPTION_ DIAGLEVEL1 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. Issue the console message, at most every 5 minutes. DSNT771I
EXCEPTION_DIAGLEVEL2 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. Issue the console message, at most every 5 minutes. DSNT772I
EXCEPTION_DIAGLEVEL3 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. 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
ATTRIBUTE2
For MONITOR ALL THREADS, an integer that specifies the threshold for the total cumulative number of active server threads 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 disabled and a message is recorded in the profile attributes history table to indicate that this row is rejected.

ATTRIBUTE3
NULL
End of change
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 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
For MONITOR CONNECTIONS, 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. Issue the console message, at most every 5 minutes. DSNT771I
EXCEPTION_ DIAGLEVEL1 Fail the connection request and issue the console message. Issue the console message, at most every 5 minutes. DSNT771I
EXCEPTION_DIAGLEVEL2 Fail the connection request and issue the console message. Issue the console message, at most every 5 minutes. 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
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).

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
For MONITOR IDLE THREADS, 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
For MONITOR IDLE THREADS, an integer that specifies 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 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 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
For MONITOR THREADS, 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, 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 with 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.

Connections associated with the queued or suspended threads are not terminated and remain 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.

Connections associated with the queued or suspended threads are not terminated and remain 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.

Connections associated with the queued or suspended threads are not terminated and remain 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
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).

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
Start of changeFor MONITOR THREADS, specifies the threshold for the maximum number of server threads allowed to be suspended after meeting the profile criteria. The value must be a whole number, less than or equal to the ATTRIBUTE2 column.End of change
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.Start of changeThe default value is 1.End of change 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
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.

Assigning a value to a special register using the profile tables applies only to remote applications.

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 specified for assignment must be valid for the special register and must not be an expression, or reference other special registers or variables
  • The value specified for assignment is passed through this interface as a literal string. 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.
  • Special register names can be specified in mixed case, and extraneous blanks are allowed between keywords.
  • 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 by profiles:
  • CURRENT LC_CTYPE
  • CURRENT_LC_CTYPE
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION The following syntax variations are not supported by 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
NULL
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.