Table of Contents

Search

  1. Introduction
  2. Samples and Examples
  3. Tools and Utilities
  4. SDK Resources

IBM DB2 Configuration Parameters

IBM DB2 Configuration Parameters

To run the database performance test, prepare the IBM DB2 database environment to be similar to the Informatica benchmark environment. You can compare the database performance test results with the benchmark environment if the configuration of the two database environments are similar.
The following table describes the parameters to configure in the
INIT.ORA
file:

IBM DB2 Registry Variables

The following table describes the IBM DB2 registry parameters that were configured in the benchmark environment:
Registry Variable
Description
DB2INSTOWNER
Created in the DB2 profile registry when the instance is created, and is set to the name of the machine that owns the instance. The Informatica benchmark environment was set to
TORPERF4
. Default is
NULL
.
DB2CODEPAGE
Specifies the code page of the data. DB2 derives the code page information from the operating system. The Informatica benchmark environment was set to
1208
.
DB2_RESTORE_GRANT_ADMIN_AUTHORITIES
Grants SECADM, DBADM, DATAACCESS, and ACCESSCTRL authorities when you restore to a new or existing database. The Informatica benchmark environment was set to
ON
. Default is
OFF
.
DB2_GRP_LOOKUP
Enables access token support. The Informatica benchmark environment was set to
LOCAL
.
DB2COMM
Configures the communication managers to start when the database manager is started. The Informatica benchmark environment was set to
TCPIP
. Default is
NULL
.
DB2INSTPROF
Specifies the location of the instance directory on Windows operating systems. The Informatica benchmark environment was set to
C:\IBM\PROGRAMDATA\\IBM\DB2\DB2SERVICE
.
DB2PORTRANGE
Specifies the TCP/IP port range used by FCM so that any additional database partitions created on another machine has the same port range. The Informatica benchmark environment was set to
60000:60003
.
DB2_DEFERRED_PREPARE_SEMANTICS
Specifies whether or not to allow dynamic statements that contain untyped parameter markers to use deferred prepare semantics in CLI and JDBC applications. The Informatica benchmark environment was set to
YES
. Default is
NO
.
DB2_SKIPINSERTED
Specifies whether or not to allow statements that use cursor stability or read stability isolation levels to skip uncommitted inserted rows. The Informatica benchmark environment was set to
YES
. Default is
NO
.
DB2_SKIPDELETED
Specifies whether to allow statements that use cursor stability or read stability isolation levels to skip deleted keys during index access and deleted rows during table access. The Informatica benchmark environment was set to
YES
. Default is
NO
.
DB2_EVALUNCOMMITTED
Specifies whether to allow, where possible, table or index access scans to avoid row locking until a data record satisfies predicate evaluation. The Informatica benchmark environment was set to
YES
. Default is
NO
.
DB2_SELECTIVITY
Controls where the SELECTIVITY clause can be used in search conditions in SQL statements. The Informatica benchmark environment was set to
ALL
. Default is
NO
.
DB2NTNOCACHE
Specifies whether or not the IBM DB2 opens database files with a NOCACHE option. The Informatica benchmark environment was set to
ON
. Default is
OFF
.
DB2_INLIST_TO_NLJN
Configures the optimizer to prefer or not prefer nested loop joins. The Informatica benchmark environment was set to
NO
. Default is
NO
.
DB2_ANTIJOIN
Configures the optimizer to transform subqueries into anti-joins. The Informatica benchmark environment was set to
YES
. Default is
NO
.
DB2_REDUCED_OPTIMIZATION
Configures reduced optimization features or rigid use of optimization features at the specified optimization level. The Informatica benchmark environment was set to
REDUCE_LOCKING
. Default is
NO
.
DB2_EXTENDED_OPTIMIZATION
Configures whether or not the query optimizer uses optimization extensions to improve query performance. The Informatica benchmark environment was set to
ON,ENHANCED_MULTIPLE_DISTINCT,IXOR,SNHD
. Default is
OFF
.
DB2_HASH_JOIN
Configures hash join as a possible join method when compiling an access plan. The Informatica benchmark environment was set to
YES
. Default is
YES
.

Database Manager Configuration Parameters

The following table describes the IBM DB2 database manager configuration parameters that require optimization and their recommended settings:
Configuration Parameter
Description
AGENT_STACK_SZ
Determines the amount of memory that DB2 allocates for each agent thread stack. The Informatica benchmark environment was set to
256
. Default value varies for the different operating systems.
AGENTPRI
Control the priority given to the database manager instance processes and threads that run on the machine. The Informatica benchmark environment was set to
SYSTEM
. Default is
SYSTEM
.
ALT_DIAGPATH
Specifies the fully qualified alternate path for DB2 diagnostic information to use when the primary diagnostic data path, diagpath, is unavailable. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
ALTERNATE_AUTH_ENC
Specifies the alternate encryption algorithm to encrypt the user IDs and passwords submitted to a DB2 database server for authentication. The Informatica benchmark environment was set to
NOT_SPECIFIED
. Default is
NOT_SPECIFIED
.
ASLHEAPSZ
Specifies the communication buffer between the local application and its associated agent that is allocated as shared memory by each database manager agent that starts. The Informatica benchmark environment was set to
15
. Default is
15
.
AUDIT_BUF_SZ
Specifies the size of the buffers used when you audit the DB2 instance. The Informatica benchmark environment was set to
0
. Default is
0
.
AUTHENTICATION
Specifies and determines how and where user authentication occurs. The Informatica benchmark environment was set to
SERVER
. Default is
SERVER
.
CATALOG_NOAUTH
Specifies whether or not users can catalog and uncatalog databases and nodes, or DCS and ODBC directories, without SYSADM authority. The Informatica benchmark environment was set to
NO
. Default is
NO
when the database server has local and remote clients.
CLNT_KRB_PLUGIN
Specifies the default Kerberos plug-in library name to use for client-side authentication and local authorization. The Informatica benchmark environment was set to
IBMkrb5
. Default is
IBMkrb5
.
CLNT_PW_PLUGIN
Specifies the name of the userid-password plug-in library to use for client-side authentication and local authorization. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
CLUSTER_MGR
Specifies the cluster manager to which the database manager must communicate incremental cluster configuration changes. The Informatica benchmark environment was not set to use the parameter. This parameter does not have a default value.
COMM_BANDWIDTH
Specifies the bandwidth between database partition servers to help the query optimizer determine access paths. The Informatica benchmark environment was set to
1.000000e+002
. Default is
-1
.
CONN_ELAPSE
Specifies the time in seconds within which a TCP/IP connection must be established between two database partition servers. The Informatica benchmark environment was set to
10
. Default is
10
.
CPUSPEED
Specifies the CPU speed of the database machines. The Informatica benchmark environment was set to
1.731929e-007
. Default is
-1
.
DFT_ACCOUNT_STR
Specifies the default suffix of accounting identifiers. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
DFTDBPATH
Specifies the default file path for creating databases under the database manager. The Informatica benchmark environment was set to
C:
. Default on UNIX is the home directory of the instance owner, and on Windows it is the drive on which DB2 is installed.
DFT_DEGREE
Configures the default for the DEGREE bind option. The Informatica benchmark environment was set to
ANY
. Default is
ANY
.
DFT_MONSWITCHES
Sets a number of database system monitor switches. The Informatica benchmark environment was set to
ON
. Default is
OFF
.
DIAGLEVEL
Specifies the type of diagnostic errors to record in the db2diag log file. The Informatica benchmark environment was set to
4
. Default is
3
.
DIAGPATH
Specifies the fully qualified primary path for DB2 diagnostic information. The Informatica benchmark environment was set to
D:\DB2\DIAG\
. Default is
NULL
.
DIAGSIZE
Controls the maximum size of the diagnostic log and administration notification log files. The Informatica benchmark environment was set to
0
. Default is
0
.
DIR_CACHE
Determines whether the database, node, and DCS directory files are cached in memory. The Informatica benchmark environment was set to
YES
. Default is
YES
.
DISCOVER
Determines the type of discovery requests that the client can make. The Informatica benchmark environment was set to
SEARCH
. Default is
SEARCH
.
DISCOVER_INST
Specifies whether the database instance is detectable by DB2 discovery. The Informatica benchmark environment was set to
ENABLE
. Default is
ENABLE
.
FCM_NUM_BUFFERS
Specifies the number of 4KB buffers to use for internal communications. The Informatica benchmark environment was set to
AUTOMATIC(4096)
. Default is
AUTOMATIC(<n>)
.
<n>
depends on the database server client type and partitioning.
FCM_NUM_CHANNELS
Specifies the number of FCM channels for each database partition. The Informatica benchmark environment was set to
AUTOMATIC(2048)
. Default is
AUTOMATIC(<n>)
.
<n>
depends on the database server client type and partitioning.
FCM_PARALLELISM
Specifies the degree of parallelism to use for communication between members within a DB2 instance. The Informatica benchmark environment was set to
1
. Default is
1
.
FED_NOAUTH
Determines whether federated authentication is bypassed at the instance. The Informatica benchmark environment was set to
NO
. Default is
NO
.
FEDERATED
Specifies whether or not to support applications that submit distributed requests for data managed by data sources. The Informatica benchmark environment was set to
NO
. Default is
NO
.
FEDERATED_ASYNC
Specifies the maximum number of asynchrony table queues in the access plan that the federated server supports. The Informatica benchmark environment was set to
0
. Default is
0
.
FENCED_POOL
Specifies the number of threads to cache in each db2fmp process for threaded db2fmp processes, or the number of processes to cache for nonthreaded db2fmp processes. The Informatica benchmark environment was set to
AUTOMATIC(MAX_COORDAGENTS)
. Default is
AUTOMATIC(MAX_COORDAGENTS)
.
GROUP_PLUGIN
Specifies the name of the group plug-in library. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
HEALTH_MON
Specifies whether you want to monitor an instance, its associated databases, and database objects based on various health indicators. The Informatica benchmark environment was set to
OFF
. Default is
ON
.
INDEXREC
Specifies when the indexes that are rebuilt are not valid, and whether any index build log records are redone during rollforward or high availability disaster recovery log replay. The Informatica benchmark environment was set to
RESTART
. Default is
RESTART
.
INSTANCE_MEMORY
Specifies the maximum memory allocation for a database partition if you use DB2 database products with memory usage restrictions or if you set it to a specific value. Otherwise, a value is calculated for the instance memory when the database partition is activated. The Informatica benchmark environment was set to
13107200
. Default is
0
.
INTRA_PARALLEL
Configures whether or not the database manager can use intra-partition parallelism. The Informatica benchmark environment was set to
YES
. Default is
NO
.
JAVA_HEAP_SZ
Specifies the maximum heap size to use for the Java interpreter. The Informatica benchmark environment was set to
0
. Default for HP-UX is
4096
and for other operating systems, it is
2048
.
JDK_PATH
Specifies the directory under which the Software Developer's Kit for Java is installed. The Informatica benchmark environment was set to
C:\IBM\SQLLIB\java\jd
. Default is
NULL
.
KEEPFENCED
Specifies whether or not a fenced mode process is kept after a fenced mode routine call is complete. The Informatica benchmark environment was set to
YES
. Default is
0
.
LOCAL_GSSPLUGIN
Specifies the name of the default GSS API plug-in library to use for instance level local authorization when the value of the AUTHENTICATION parameter is not the default value. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
MAX_CONNECTIONS
Specifies the maximum number of client connections that are allowed for each database partition. The Informatica benchmark environment was set to
AUTOMATIC(MAX_COORDAGENTS)
. Default is
AUTOMATIC(MAX_COORDAGENTS)
.
MAX_CONNRETRIES
Specifies the maximum number of attempts to establish a TCP/IP connection between two database partition servers. The Informatica benchmark environment was set to
5
. Default is
5
.
MAX_COORDAGENTS
Specifies the maximum number of coordinating agents. The Informatica benchmark environment was set to
AUTOMATIC(200)
. Default is
AUTOMATIC(200)
.
MAX_QUERYDEGREE
Specifies the maximum degree of intrapartition parallelism to use for SQL statements that run on the database manager instance. The Informatica benchmark environment was set to
ANY
. Default is
ANY
.
MAX_TIME_DIFF
Specifies the maximum time difference, in minutes, to allow between the database partition servers listed in the node configuration file. The Informatica benchmark environment was set to
60
. Default is
60
.
MON_HEAP_SZ
Allocates the amount of the memory, in pages, for database system monitor data. The Informatica benchmark environment was set to
AUTOMATIC(66)
. Default is
AUTOMATIC
.
NOTIFYLEVEL
Specifies the type of administration notification messages that are written to the administration notification log. The Informatica benchmark environment was set to
3
. Default is
3
.
NUM_INITAGENTS
Specifies the initial number of idle agents that can be created in the agent pool when the db2start command is run. The Informatica benchmark environment was set to
15
. Default is
0
.
NUM_INITFENCED
Specifies the initial number of nonthreaded, idle db2fmp processes that can be created in the db2fmp pool when the database manager starts. The Informatica benchmark environment was set to
0
. Default is
0
.
NUM_POOLAGENTS
Sets the maximum size of the idle agent pool. The Informatica benchmark environment was set to
40
. Default is
100
.
NUMDB
Specifies the number of local databases that can be concurrently active, or the maximum number of different database aliases that can be cataloged on a DB2 Connect server. The Informatica benchmark environment was set to
8
. Default is
8
.
RESYNC_INTERVAL
Specifies the time interval in seconds at which a transaction manager (TM), resource manager (RM) or sync point manager (SPM) should try to recover outstanding indoubt transactions that are found in the TM, the RM, or the SPM. The Informatica benchmark environment was set to
180
. Default is
180
.
RQRIOBLK
Sets the communication buffer size between remote applications and their database agents on the database server. The Informatica benchmark environment was set to
32767
. Default is
256
.
SHEAPTHRES
Configures the sort heap threshold for sorts. The Informatica benchmark environment was set to
0
. Default is
0
.
SPM_LOG_FILE_SZ
Sets the SPM log file size in 4 KB pages. The Informatica benchmark environment was set to
256
. Default is
256
.
SPM_LOG_PATH
Specifies the directory in which the SPM logs are written. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SPM_MAX_RESYNC
Sets the number of agents that can simultaneously perform resync operations. The Informatica benchmark environment was set to
20
. Default is
20
.
SPM_NAME
Specifies the SPM instance name. The Informatica benchmark environment was set to
TORPERF4
. Default is derived from the TCP/IP host name.
SRVCON_AUTH
Specifies user authentication procedure to handle incoming connections at the server. The Informatica benchmark environment was set to
NOT_SPECIFIED
. Default is
NOT_SPECIFIED
.
SRVCON_GSSPLUGIN_LIST
Specifies the GSS API plug-in libraries that are supported by the database server. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SRVCON_PW_PLUGIN
Specifies the name of the default userid-password plug-in library to use for server-side authentication. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SRV_PLUGIN_MODE
Specifies whether plug-ins are to run in fenced mode or unfenced mode. The Informatica benchmark environment was set to
UNFENCED
. Default is
UNFENCED
.
SSL_CIPHERSPECS
Specifies the cipher suites that the server must allow for incoming connection requests when using SSL protocol. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SSL_CLNT_KEYDB
Specifies the fully qualified file path of the key file to use for SSL connection on the client-side. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SSL_CLNT_STASH
Specifies the fully qualified file path of the stash file to use for SSL connections at the client-side. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SSL_SVR_KEYDB
Specifies a fully qualified file path of the key file to use for SSL setup at server-side. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SSL_SVR_LABEL
Specifies a label of the personal certificate of the server in the key database. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SSL_SVR_STASH
Specifies a fully qualified file path of the stash file to use for SSL setup at server-side. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
START_STOP_TIME
Specifies the time, in minutes, within which all database partition servers must respond to a START DBM or a STOP DBM command. The Informatica benchmark environment was set to
10
. Default is
10
.
SSL_SVCENAME
Specifies the port name that a database server uses to wait for communications from remote client nodes using SSL protocol. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SSL_VERSIONS
Specifies SSL and TLS versions that the server supports for incoming connection requests. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SVCENAME
Specifies the name of the TCP/IP port that a database server can use to wait for communications from remote client nodes. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SYSADM_GROUP
Defines the group name with SYSADM authority for the database manager instance. The Informatica benchmark environment was set to
DB2ADMNS
. Default is
NULL
.
SYSCTRL_GROUP
Defines the group name with system control (SYSCTRL) authority. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SYSMAINT_GROUP
Defines the group name with system maintenance (SYSMAINT) authority. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SYSMON_GROUP
Defines the group name with system monitor (SYSMON) authority. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
TM_DATABASE
Specifies the name of the TM database for each DB2 instance. The Informatica benchmark environment was set to
1ST_CONN
. Default is
1ST_CONN
.
TP_MON_NAME
Specifies the name of the transaction processing (TP) monitor product being used. The Informatica benchmark environment was set to
1ST_CONN
. This parameter does not have a default value.
TRUST_ALLCLNTS
Used with the TRUST_CLNAUTH parameter to specify where users are validated to the database environment. The Informatica benchmark environment was set to
YES
. Default is
YES
.
TRUST_CLNTAUTH
Specifies whether a trusted client is authenticated at the server or the client when the client provides a user ID and password for a connection. The Informatica benchmark environment was set to
CLIENT
. Default is
CLIENT
.
UTIL_IMPACT_LIM
Allows the database administrator to limit the performance degradation of a throttled utility on the workload. The Informatica benchmark environment was set to
CLIENT
. Default is
CLIENT
.

Database Configuration Parameters

The following table describes the IBM DB2 database configuration parameters that require optimization and specifies their recommended settings:
Configuration Parameter
Description
ALT_COLLATE
Specifies the collating sequence to use for Unicode tables in a non-Unicode database. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
APP_CTL_HEAP_SZ
Configures the application control heap size. The Informatica benchmark environment was set to
0
. The default value depends on the database server type.
APPGROUP_MEM_SZ
Configures the maximum size of application group memory. The Informatica benchmark environment was set to
0
. The default value depends on the database server type.
APPL_MEMORY
Controls the maximum amount of application memory that is allocated by DB2 database agents to service application requests. The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
APPLHEAPSZ
Specifies the total application memory that is available for the entire application. The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
ARCHRETRYDELAY
Specifies the number of seconds to wait after an archive attempt fails before a retry to archive the log file. The Informatica benchmark environment was set to
0
. Default is
20
.
AUTO_DEL_REC_OBJ
Specifies whether database log files, backup images, and load copy images should be deleted when the associated recovery history file entry is cleaned up. The Informatica benchmark environment was set to
OFF
. Default is
OFF
.
AUTO_MAINT
Parent parameter of all the other automatic maintenance database configuration parameters (auto_db_backup, auto_tbl_maint, auto_runstats, auto_stats_prof, auto_stmt_stats, auto_prof_upd, and auto_reorg). The Informatica benchmark environment was set to
OFF
. Default is
ON
.
AUTO_DB_BACKUP
Enables or disables automatic backup operations for a database. The Informatica benchmark environment was set to
OFF
. Default is
OFF
.
AUTO_TBL_MAINT
Enables or disables table maintenance. The Informatica benchmark environment was set to
ON
. Default is
ON
.
AUTO_RUNSTATS
Enables or disables automatic table runstats operations for a database. The Informatica benchmark environment was set to
ON
. Default is
ON
.
AUTO_STATS_PROF
Configures statistical profile generation. The Informatica benchmark environment was set to
OFF
. Default is
OFF
.
AUTO_STMT_STATS
Enables or disables the collection of real-time statistics. The Informatica benchmark environment was set to
ON
. Default is
ON
.
AUTO_PROF_UPD
Specifies whether or not the runstats profile is to be updated with recommendations. The Informatica benchmark environment was set to
OFF
. Default is
OFF
.
AUTO_REORG
Enables or disables automatic table and index reorganization for a database. The Informatica benchmark environment was set to
OFF
. Default is
OFF
.
AUTO_REVAL
Controls the revalidation and invalidation semantics. The Informatica benchmark environment was set to
DEFERRED_FORCE
. Default is
DEFERRED
.
AUTORESTART
Specifies whether the database manager can, in the event of an abnormal database termination, automatically call the restart database utility when an application connects to a database. The Informatica benchmark environment was set to
ON
. Default is
ON
.
AVG_APPLS
Allows the query optimizer to estimate the buffer pool space that is available at run time for the access plan chosen. The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
BLK_LOG_DSK_FUL
Prevents disk full errors from being generated when DB2 cannot create a new log file in the active log path. The Informatica benchmark environment was set to
NO
. Default is
NO
.
BLOCKNONLOGGED
Specifies whether the database manager allows tables to have the NOT LOGGED or NOT LOGGED INITIALLY attributes activated. The Informatica benchmark environment was set to
NO
. Default is
NO
.
CATALOGCACHE_SZ
Configures the maximum memory that the catalog cache can use from the shared memory of the database.
The Informatica benchmark environment was set to
25000
. Default is
-1
.
CHNGPGS_THRESH
Specifies the percentage level of changed pages at which the asynchronous page cleaners start, if they are not currently active. The Informatica benchmark environment was set to
80
. Default is
60
.
CONNECT_PROC
Specifies a two-part connect procedure name that runs every time an application connects to the database. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
CUR_COMMIT
Controls the behavior of cursor stability scans. The Informatica benchmark environment was set to
ON
. Default is
ON
.
DATABASE_MEMORY
Specifies the size of the database memory set. The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
DBHEAP
Limits the maximum amount of memory allocated for the database heap. The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
DB_MEM_THRESH
Specifies the maximum percentage of committed, but currently unused, database shared memory that the database manager allows before the release of committed pages of memory back to the operating system. The Informatica benchmark environment was set to
10
. Default is
10
.
DEC_TO_CHAR_FMT
Controls the result of the CHAR scalar function and the CAST specification to convert decimal to character values. The Informatica benchmark environment was set to
NEW
. Default is
NEW
.
DECFLT_ROUNDING
Sets the rounding mode for decimal floating point, DECFLOAT. The Informatica benchmark environment was set to
ROUND_HALF_UP
. Default is
ROUND_HALF_EVEN
.
DFT_DEGREE
Specifies the default value for the CURRENT DEGREE special register and the DEGREE bind option. The Informatica benchmark environment was set to
ANY
. Default is
1
.
DFT_EXTENT_SZ
Sets the default extent size of table spaces. The Informatica benchmark environment was set to
32
. Default is
32
.
DFT_LOADREC_SES
Specifies the default number of sessions that can be used during a table load recovery. The Informatica benchmark environment was set to
1
. Default is
0
.
DFT_MTTB_TYPES
Specifies the default value for the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register. The Informatica benchmark environment was set to
SYSTEM
. Default is
SYSTEM
.
DFT_PREFETCH_SZ
Sets the default prefetch size of table spaces. The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
DFT_QUERYOPT
Sets the default query optimization class used when neither the SET CURRENT QUERY OPTIMIZATION statement nor the QUERYOPT option on the bind command are used. The Informatica benchmark environment was set to
5
. Default is
5
.
DFT_REFRESH_AGE
This parameter represents the maximum time duration since a REFRESH TABLE statement has been processed on a specific REFRESH DEFERRED materialized query table. After this time limit is exceeded, the materialized query table is not used to satisfy queries until the materialized query table is refreshed. The Informatica benchmark environment was set to
0
. Default is
0
.
DFT_SQLMATHWARN
Sets the default value that determines the handling of arithmetic errors and retrieval conversion errors during SQL statement compilation. The Informatica benchmark environment was set to
NO
. Default is
NO
.
DISCOVER_DB
Prevents information about a database from being returned to a client when a discovery request is received at the server. The Informatica benchmark environment was set to
ENABLE
. Default is
ENABLE
.
DLCHKTIME
Defines the frequency at which the database manager checks for deadlocks among all the applications connected to a database. The Informatica benchmark environment was set to
10000
. Default is
10000
.
DYN_QUERY_MGMT
Determines whether Query Patroller captures information about submitted queries. The Informatica benchmark environment was set to
0
. Default is
DISABLE
.
ENABLE_XMLCHAR
Determines whether XMLPARSE operations can be performed on non-BIT DATA CHAR, or CHAR-type, expressions in an SQL statement. The Informatica benchmark environment was set to
YES
. Default is
YES
.
FAILARCHPATH
Specifies a path to which DB2 tries to archive log files if the log files cannot be archived to either the primary or the secondary archive destinations because of a media problem that affects the destinations. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
HADR_DB_ROLE
Specifies the current role of a database, whether the database is online or offline. The Informatica benchmark environment was set to
STANDARD
. This parameter does not have a default value.
HADR_LOCAL_HOST
Specifies the local host for high availability disaster recovery (HADR) TCP communication. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
HADR_LOCAL_SVC
Specifies the TCP service name or port number for which the local HADR process accepts connections. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
HADR_PEER_WINDOW
When set to a non-zero time value, an HADR primary-standby database pair continues to behave as though still in peer state, for the configured amount of time, if the primary database loses connection with the standby database. The Informatica benchmark environment was set to
0
. Default is
0
.
HADR_REMOTE_HOST
Specifies the TCP/IP host name or IP address of the remote HADR database server. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
HADR_REMOTE_INST
Specifies the instance name of the remote server. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
HADR_REMOTE_SVC
Specifies the TCP service name or port number that are used by the remote HADR database server. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
HADR_SYNCMODE
Specifies the synchronization mode, which determines how primary log writes are synchronized with the standby when the systems are in peer state. The Informatica benchmark environment was set to
NEARSYNC
. Default is
NEARSYNC
.
HADR_TIMEOUT
Specifies the time for which the HADR process waits before it considers a communication attemptas failed. The Informatica benchmark environment was set to
120
. Default is
120
.
INDEXREC
Indicates when the database manager attempts to rebuild indexes that are not valid, and whether any index build is redone during rollforward or HADR log replay on the standby database. The Informatica benchmark environment was set to
RESTART
. Default is
RESTART
.
INDEXSORT
Specifies whether data must be sorted before it is passed to the index manager. The Informatica benchmark environment was set to
YES
. Default is
YES
.
LOCKLIST
Allocates the amount of storage to the lock list of a database. The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
LOCKTIMEOUT
Specifies the number of seconds that an application waits to obtain a lock, to avoid global deadlocks for applications. The Informatica benchmark environment was set to
-1
. Default is
-1
.
LOGARCHMETH1
Specifies the media type of the primary destination for archived logs. The Informatica benchmark environment was set to
OFF
. Default is
OFF
.
LOGARCHMETH2
Specifies the media type of the secondary destination for archived logs. The Informatica benchmark environment was set to
OFF
. Default is
OFF
.
LOGARCHOPT1
Specifies the options field for the primary destination for archived logs. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
LOGARCHOPT2
Specifies the options field for the secondary destination for archived logs. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
LOGBUFSZ
Configures the amount of the database heap to use as a buffer for log records before writing the records to disk. The Informatica benchmark environment was set to
4096
. Default is
256
.
LOGFILSIZ
Defines the size of each primary and secondary log file. The Informatica benchmark environment was set to
256000
. Default is
1000
.
LOGINDEXBUILD
Specifies whether index creation, recreation, or reorganization operations are logged so that indexes can be reconstructed during DB2 rollforward operations or HADR log replay procedures. The Informatica benchmark environment was set to
OFF
. Default is
OFF
.
LOGPRIMARY
Configures the number of primary log files to be pre-allocated.
The Informatica benchmark environment was set to
100
. Default is
3
.
LOGRETAIN
Sets the LOGRETAIN value. The Informatica benchmark environment was set to
OFF
. Default is
OFF
.
LOGSECOND
Configures the number of secondary log files that IBM DB2 can create and use for recovery log files.
The Informatica benchmark environment was set to
100
. Default is
2
.
MAX_LOG
Specifies the limit to the percentage of log space that a transaction can consume. The Informatica benchmark environment was set to
0
. Default is
0
.
MAXAPPLS
Specifies the maximum number of concurrent applications that can be connected to a database. The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
MAXFILOP
Specifies the maximum number of file handles to open for each database. The Informatica benchmark environment was set to
65535
. The default value depends on the operating system.
MAXLOCKS
Configures the percentage of the lock list that one application can use. The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
MINCOMMIT
Allows the delay of the writing of log records to disk, until a minimum number of commits are performed. The Informatica benchmark environment was set to
1
. Default is
1
.
MIRRORLOGPATH
Specifies a string of up to 242 bytes for the fully qualified mirror path, not a relative path. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
MON_ACT_METRICS
Controls the collection of activity metrics on the entire database and affects activities submitted by connections associated with any DB2 workload definitions. The Informatica benchmark environment was set to
BASE
. Default is
BASE
.
MON_DEADLOCK
Controls the generation of deadlock events at the database level for the lock event monitor. The Informatica benchmark environment was set to
WITHOUT_HIST
. Default is
WITHOUT_HIST
.
MON_LOCKTIMEOUT
Controls the generation of lock timeout events at the database level for the lock event monitor and affects all DB2 workload definitions. The Informatica benchmark environment was set to
NONE
. Default is
NONE
.
MON_LOCKWAIT
Controls the generation of lock wait events at the database level for the lock event monitor. The Informatica benchmark environment was set to
NONE
. Default is
NONE
.
MON_LW_THRESH
Controls the amount of time spent in lock wait before an event for mon_lockwait is generated. The Informatica benchmark environment was set to
5000000
. Default is
5000000
.
MON_LCK_MSG_LVL
Controls the logging of messages to the administration notification log when lock timeout, deadlock, and lock escalation events occur. The Informatica benchmark environment was set to
1
. Default is
1
.
MON_OBJ_METRICS
Controls the collection of data object metrics on the database. The Informatica benchmark environment was set to
BASE
. Default is
BASE
.
MON_PKGLIST_SZ
Controls the maximum number of entries that can appear in the package listing for each unit of work that the unit of work event monitor captures. The Informatica benchmark environment was set to
32
. Default is
32
.
MON_REQ_METRICS
Controls the collection of request metrics on the database and affects requests that run in DB2 service classes. The Informatica benchmark environment was set to
BASE
. Default is
BASE
.
MON_UOW_DATA
Controls the generation of unit of work events at the database level for the unit of work event monitor and affects units of work on the data server. The Informatica benchmark environment was set to
NONE
. Default is
NONE
.
NEWLOGPATH
Specifies a string of up to 242 bytes to change the location of the log files. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
NUM_DB_BACKUPS
Specifies the number of full database backups to retain for a database. The Informatica benchmark environment was set to
12
. Default is
12
.
NUM_FREQVALUES
Specifies the number of frequent values that will be collected if you specify the WITH DISTRIBUTION option on the RUNSTATS command. The Informatica benchmark environment was set to
10
. Default is
10
.
NUM_IOCLEANERS
Specifies the number of asynchronous page cleaners for a database. The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
NUM_IOSERVERS
Specifies the number of input/output servers for a database that can be in progress for prefetching and utilities. The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
NUM_LOG_SPAN
Specifies whether there is a limit to how many log files one transaction can span and what that limit is. The Informatica benchmark environment was set to
0
. Default is
0
.
NUM_QUANTILES
Controls the number of quantiles that will be collected if you specify the WITH DISTRIBUTION option on the RUNSTATS command. The Informatica benchmark environment was set to
20
. Default is
20
.
NUMARCHRETRY
Specifies the number of attempts that DB2 must make to archive a log file to the primary or the secondary archive directory before trying to archive log files to the failover directory. The Informatica benchmark environment was set to
5
. Default is
5
.
OVERFLOWLOGPATH
Specifies a location for DB2 databases to find log files that are needed for a rollforward operation, in addition to storing active log files retrieved from the archive. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
PCKCACHESZ
Configures the package cache size, which is allocated out of the database shared memory. The Informatica benchmark environment was set to
128000
. Default is
AUTOMATIC
.
REC_HIS_RETENTN
Specifies the number of days that historical information on backups are retained. The Informatica benchmark environment was set to
366
. Default is
366
.
SECTION_ACTUALS
Enables measurement of section actuals. The Informatica benchmark environment was set to
NONE
. Default is
NONE
.
SELF_TUNING_MEM
Specifies whether the memory tuner dynamically distributes the available memory resources between memory consumers that are enabled for self-tuning. The Informatica benchmark environment was set to
ON
. In single-database partition environments, default is
ON
. In multi-database partition environments, default is
OFF
.
SEQDETECT
Controls whether the database manager is allowed to detect sequential page reading during input/output activity. The Informatica benchmark environment was set to
YES
. Default is
YES
.
SHEAPTHRES_SHR
Configures the limit on the total amount of database shared memory that the sort memory consumers can use at a time.
Set the SHEAPTHRES_SHR parameter to AUTOMATIC if the SORTHEAP parameter is set to AUTOMATIC. The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
SMTP_SERVER
Identifies a simple mail transfer protocol (SMTP) server that transmits email sent by the UTL_MAIL built-in module. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
SOFTMAX
Determines the frequency of soft check points and the recovery range that help in the crash recovery process. The Informatica benchmark environment was set to
500
. Default is
100
.
SORTHEAP
Configures the sort heap size.
The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
SQL_CCFLAGS
Contains a list of conditional compilation values for use in conditional compilation of selected SQL statements. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
STAT_HEAP_SZ
Indicates the maximum heap size to use in collecting statistics using the RUNSTATS command. The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
STMTHEAP
Configures the limit of the statement heap, which is used during the compilation of an SQL statement.
The Informatica benchmark environment was set to
AUTOMATIC
. Default is
AUTOMATIC
.
STMT_CONC
Sets the default statement concentrator behavior. The Informatica benchmark environment was set to
OFF
. Default is
OFF
.
TRACKMOD
Specifies whether the database manager tracks database modifications so that the backup utility detects the subsets of the database pages that must be examined by an incremental backup, and potentially included in the backup image. The Informatica benchmark environment was set to
NO
. Default is
NO
.
TSM_MGMTCLASS
Determines how the Tivoli Storage Manager (TSM) server manages the backup versions of the objects that are backed up. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
TSM_NODENAME
Sets the value used to override the default setting for the node name associated with the TSM product. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
TSM_OWNER
Sets the value used to override the default setting for the owner associated with the TSM product. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
TSM_PASSWORD
Sets the value used to override the default setting for the password associated with the TSM product. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
USEREXIT
Sets the USEREXIT value. The Informatica benchmark environment was set to
OFF
. Default is
OFF
.
UTIL_HEAP_SZ
Configures the maximum amount of memory that the BACKUP, RESTORE, and LOAD utilities can use simultaneously. The Informatica benchmark environment was set to
50000
. Default is
5000
.
VENDOROPT
Specifies additional parameters that DB2 might need to use to communicate with storage systems during backup, restore, or load copy operations. The Informatica benchmark environment was set to
NULL
. Default is
NULL
.
WLM_COLLECT_INT
Specifies a collect and reset interval, in minutes, for workload management statistics. The Informatica benchmark environment was set to
BASE
. Default is
0
.

0 COMMENTS

We’d like to hear from you!