Special Database Parameters

Use

The following overview describes the special database parameters. For more information about other database parameters, see General Database Parameters, Support Database Parameters and liveCache Database Parameters.

Changes to database parameters do not take effect until the database instance is restarted.

You can also change some database parameters while the database is running. In addition you can choose how long the change is to be effective for these database parameters:

¦      Until the next restart

¦      Not until after the next restart

¦      Immediately and permanently

Special Database Parameters (Extended)

Parameter

Meaning

How can I change this Parameter?

_COMMENT

Description of the database instance

Changes only apply after restart

_DATA_CACHE_RGNS

Number of critical sections into which the data cache is divided

This division enables parallel writing to the data cache. The parameter is calculated by the database system and cannot be changed. You can override the parameter for testing purposes, however, using the XP_DATA_CACHE_RGNS support database parameter.

It cannot be altered.

_MAX_MESSAGE_FILES

Maximum number of trace files that can be open simultaneously

Changes only apply after restart

_MAXEVENTS

Maximum number of events stored in the working memory by the kernel that are to be processed by the Database Manager

Changes only apply after restart

_ROW_RGNS

Number of critical sections into which the total of all rows are divided in the lock list

This division enables parallel access to the lock list.

Changes only apply after restart

_TAB_RGNS

Number of critical sections into which the total of all tables in the lock list are divided

This division enables parallel access to the lock list.

Changes only apply after restart

_TRANS_RGNS

Number of critical sections into which the total of all transactions in the lock list are divided

This division enables parallel access to the lock list.

Changes only apply after restart

_UNICODE

Defines whether the database system uses Unicode as the character set for the names of database objects in the database catalog

You define this database parameter when you create the database instance. Afterwards you can only change the database parameter by re-initializing the database instance. However, in doing so you lose all application data.

AUTO_RECREATE_BAD_INDEXES

Indexes marked as BAD are recreated automatically after a restore.

Changes only apply after restart

BACKUP_BLOCK_CNT

Block size used by the database system when writing backups to data carriers

Only useful for tapes that have an optimal block size

Changes only apply after restart

CAT_CACHE_SUPPLY

Memory size of the catalog cache for all user tasks (in pages)

Changes only apply after restart

CLUSTERED_LOBS

Specifies whether the database system stores the LONG values (LOBs) as clusters in the data area

Can be changed during operation

CLUSTER_WRITE_THRESHOLD

Minimum amount of blocks that the database system stores as clusters in the data area

Can be changed during operation

CONVERTER_REGIONS

Number of critical sections into which the converter is divided

This division enables parallel writing to the converter.

The parameter is calculated by the database system and cannot be changed. You can override the parameter for testing purposes, however, using the XP_CONVERTER_REGIONS support database parameter.

Changes only apply after restart

DATE_TIME_FORMAT

System default for the date and time format

You can overwrite the date and time format by using database tools (for example through standard_date_mask in the Loader or through corresponding entries in SQL statements (datetimeformat). See Date and Time Format.

Can be changed during operation

DEADLOCK_DETECTION

Maximum search level for deadlock detection

Any deadlocks that the database system does not detect at this search level are only resolved by the parameter REQUEST_TIMEOUT.

Changes only apply after restart

DEFAULT_CODE

System default for the code attribute, applies only to the column values of the data types CHAR[ACTER], VARCHAR and LONG[VARCHAR].

Can be changed during operation

DIAG_HISTORY_NUM

Number of histories in the <diag_history_path> directory

Changes only apply after restart

DIAG_HISTORY_PATH

Directory <diag_history_path> in which the database system saves diagnosis files (backups of the most important log files and memory dumps) after a database error

Changes only apply after restart

FILEDIR_SPINLOCKPOOL_SIZE

Number of spinlocks available for reader and writer locks for the internal file directory

Changes only apply after restart

FORMAT_DATAVOLUME

Suppresses the formatting of data volumes when a new database instance is created; only useful for test purposes

Changes only apply after restart

FORMATTING_MODE

The database system formats the volumes when the database instance is created. With this parameter, you define the type of formatting. Parallel formatting is only useful for data volumes of the file type which are located on different hard disks.

Changes only apply after restart

HASHED_RESULTSET

Specifies whether the database system, using a hash procedure, optimizes result sets that result from the execution of a join

Can be changed during operation

HASHED_RESULTSET_CACHESIZE

Maximum size of the working memory that the database can use for result sets that are optimized with a hash procedure (in KB)

Can be changed during operation

HIRES_TIMER_TYPE

Type of time measurement that is used by the kernel for internal operations; only useful for multiprocessor computers on which there can be synchronization problems between the CPUs

Changes only apply after restart

HS_DELAY_TIME_<NNN>

Only for hot standby systems

Delay in seconds until the standby instance NNN redoes the changes in the master instance

Changes only apply after restart

HS_NODE_<NNN>

Only for hot standby systems

Computer name / IP address of the hot standby instance NNN

The default value for the master instance is HSS_NODE_001.

Changes only apply after restart

HS_STORAGE_DLL

Only for hot standby systems

Name of the library through which the hot standby system addresses the memory management system

The default value is libhsscopy.

Changes only apply after restart

HS_SYNC_INTERVAL

Only for hot standby systems

Minimum time span between two commands of the master instance to synchronize the standby instances

Changes only apply after restart

JOIN_MAXTAB_LEVEL4

Parameter of the SQL Optimizer; see JOIN_SEARCH_LEVEL

Can be changed during operation

JOIN_MAXTAB_LEVEL9

Parameter of the SQL Optimizer; see JOIN_SEARCH_LEVEL

Can be changed during operation

JOIN_SEARCH_LEVEL

Algorithm used by the SQL Optimizer for the join sequence search; see Database Parameter JOIN_SEARCH_LEVEL

Can be changed during operation

JOIN_TABLEBUFFER

Size of the buffer that the SQL Optimizer can use for each join step for a read operation

Changes only apply after restart

KERNELDIAGSIZE

Size of the log file knldiag (in KB)

Changes only apply after restart

LOAD_BALANCING_CHK

Time interval in which the database system checks whether load balancing is useful (in s)

Changes only apply after restart

LOG_BACKUP_TO_PIPE

NO: After a log backup has been exported to a pipe, the connection to the pipe is then closed without the database receiving any information on whether the log backup was successful. As a result, the log area is not released and cannot be overwritten, in which case it may become full and further data changes impossible.

YES: the database system can overwrite the log entries in the log area that have already been written to the pipe. Note that the system does not check whether the log backup to the pipe was successful.

Changes only apply after restart

LOG_IO_QUEUE

Size of the log queue (in pages)

Changes only apply after restart

LRU_FOR_SCAN

Specifies whether the whole data cache is used for scans

Can be changed during operation

MAX_HASHTABLE_MEMORY

Maximum size of the working memory that the database system can use to cache all tables when executing joins (in KB)

Can be changed during operation

MAX_SINGLE_HASHTABLE_SIZE

Maximum size of the working memory that the database system can use to cache a single table when executing a join (in KB)

Can be changed during operation

MAXRGN_REQUEST

Maximum number of attempts a task should make to access a region

If this number is exceeded, the task releases the access to the CPU for a different task of the same user kernel thread.

Can be changed during operation

MAXSERVERTASKS

Number of server tasks

Changes only apply after restart

MEM_ENHANCE_LIMIT

Maximum additional memory to be used, which is possible through a Memory Scalability Enhancement for example (in MB)

Changes only apply after restart

MEMORY_ALLOCATION_LIMIT

Maximum memory to be used by the database instance (in KB)

Changes only apply after restart

MP_RGN_LOOP

Maximum number of times a task may attempt to access a critical section that has been locked by another task

If this number is exceeded, the accessing task changes its state to “Waiting”.

Can be changed during operation

OFFICIAL_NODE

Only for hot standby systems

Virtual server name by which the cluster is addressed from outside

The system administrator initially assigns the virtual server name to the computer on which the master instance of the hot standby system is located. If the master instance breaks down, then the name transfers to the standby instance, which takes on the master role.

Changes only apply after restart

OPTIM_INV_ONLY

Defines whether the SQL Optimizer uses the index-only strategy for joins

Can be changed during operation

OPTIM_MAX_MERGE

Influences the decision as to whether the SQL Optimizer uses an index-merge strategy

If the number of pages of an index that need to be merged exceeds the value specified in OPTIM_MAX_MERGE, the SQL Optimizer does not use this index for an index-merge strategy.

Can be changed during operation

OPTIMIZE_QUERYREWRITE

Automatic Transformation of SQL Statements

Default value for software versions >= 7.6: OPERATOR (the database system transforms SQL statements).

To activate the old transformation behavior (versions < 7.6) choose STATEMENT.

Can be changed during operation

REQUEST_TIMEOUT

Maximum wait for a lock to be released (in s); for all database sessions, limits wait times for a lock to be released by another user.

If a lock request cannot be satisfied within the time defined here, a message is sent to the waiting database session. The database system rolls back any changes previously executed within the transactions (ROLLBACK).

Changes only apply after restart

SEQUENCE_CACHE

Size of the sequence cache (in pages)

Changes only apply after restart

SESSION_TIMEOUT

Timeout value for database sessions (in s)

Can be changed during operation

SHAREDSQL

Activating Shared SQL

Can be changed during operation

SHAREDSQL_COMMANDCACHESIZE

Maximum size of the Shared SQL cache (in KB)

Changes only apply after restart

SHAREDSQL_EXPECTEDSTATEMENTCOUNT

Expected number of SQL statements to be stored in the shared SQL cache

Changes only apply after restart

TRACE_PAGES_<task>

Maximum number of pages that are available in the individual task types for each user kernel thread

Each user kernel thread has its own trace area in the working memory.

<task> can have the following values: BUP (Backup Task, not yet implemented), EV (Event Task), GC (Garbage Collector), LW (Log Writer), PG (Pager), SV (Server Task), TI (Timer Task), US (User Task), UT (Utility Task)

Changes only apply after restart

UPDATESTAT_SAMPLE_ALGO

Defines the algorithm the database system uses to update the SQL optimizer statistics

Can be changed during operation

USED_LOG_QUEUE_COUNT

See LOG_QUEUE_COUNT

Changes only apply after restart

VOLUMENO_BIT_COUNT

Number of bits in the converter block address that are reserved for the logical volume number of a data volume

Permitted values: 6 ? VOLUMENO_BIT_COUNT ? 12 (see Technical Restrictions)

We recommend that you do not change the value that was selected when the database instance was created. If you change the database parameter when the database is running, you then have to restore the database instance to update the data volume numbering.

Changes only apply after restart

XP_CONVERTER_REGIONS

Overriding the parameter CONVERTER_REGIONS

Changes only apply after restart

XP_DATA_CACHE_RGNS

Overriding the parameter _DATA_CACHE_RGNS

Changes only apply after restart

XP_MAXPAGER

Overriding the parameter MAXPAGER

Changes only apply after restart