CAT_CACHE_SUPPLY

CAT_CACHE_SUPPLY

Size of the task-specific catalog cache in memory which is allocated to user tasks (in pages)

For each user task the system reserves a minimum part of the catalog cache (see TaskSpecificCatalogCacheMinSize). Each task may extend its catalog cache, but all task-specific catalog caches together may not exceed the catalog cache size. Note that the catalog cache size influences performance: You achieve the best performance with a catalog cache hit rate of 100%. Use database monitoring to get information about the catalog cache hit rate.

Changes only apply after a restart of the database

Concepts of the Database System, Working Memory Areas

ClusterWriteThreshold

CLUSTER_WRITE_THRESHOLD

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

Can be changed in ONLINE or ADMIN operational state

-

ClusterCompressionFillThreshold

-

Maximum number of occupied blocks in clusters eligible for compression

If more blocks are occupied in a cluster, the database system will not compress the cluster.

Can be changed in ONLINE or ADMIN operational state

-

ClusterCompressionThreshold

-

Minimum number of blocks in a cluster which is eligible for compression that needs to be reached before the database system starts compressing the cluster (in 1/1000 of the total block count)

Can be changed in ONLINE or ADMIN operational state

-

ConverterStripes

CONVERTER_REGIONS

Number of critical sections into which the converter is divided

This division enables parallel writing to the converter.

The default value is calculated by the database system. You can change the value. Changes only apply after a restart of the database.

Glossary, Stripe

ConverterVolumeIdLayout

VOLUMENO_BIT_COUNT

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

We recommend that you do not change the value that was defined when the database was created. If you change the database parameter in the ONLINE or ADMIN operational state, you must restore the database afterwards to update the numbering of the data volume.

Database Administration, Planning the Database

DataCacheStripes

_DATA_CACHE_RGNS

Number of critical sections into which the data cache is divided

This division enables parallel writing to the data cache.

The default value is calculated by the database system. You can change the value. Changes only apply after a restart of the database.

DateTimeFormat

DATE_TIME_FORMAT

System default for the date and time format

You can override this date and time format by using database tools (such as the standard_date_mask in the Loader) or SQL statements (datetimeformat).

Can be changed in ONLINE or ADMIN operational state

-

DeadlockDetectionLevel

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

Can be changed in ONLINE or ADMIN operational state

Parameter RequestTimeout

DefaultCodePage

DEFAULT_CODE

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

Can be changed in ONLINE or ADMIN operational state

SQL Reference Manual, Code Attribute

DiagnoseHistoryCount

DIAG_HISTORY_NUM

Number of history versions in the directory in which the database system stores diagnosis files (backups of the most important log files and memory dumps) after a database error

Changes only apply after a restart of the database

Database Administration, Log Files

DiagnoseHistoryPath

DIAG_HISTORY_PATH

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

Changes only apply after a restart of the database

See above

EnableIndexOnlyStrategy

OPTIM_INV_ONLY

Defines whether the SQL optimizer uses the Index Only strategy for joins

Can be changed in ONLINE or ADMIN operational state

SQL Optimizer, Search Strategies

EnableLogBackupToPipe

LOG_BACKUP_TO_PIPE

NO: After a log backup has been exported to a pipe, the connection to the pipe is closed. The system does not receive any information on whether the log backup was successful. As a result, the log area is not released and cannot be overwritten. Therefore, it may become full. Consequently no changes to data can be made any more.

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 a restart of the database

Database Administration, Backing Up Log Entries

EnableQuery

-

Specifies whether the system is to perform an extended semantic analysis of SQL statements

Can be changed in ONLINE or ADMIN operational state

-

EnableQueryRewrite

OPTIMIZE_QUERYREWRITE

Prerequisite: EnableQuery must be set to EXTENDED.

Specifies whether the system tries to transform SQL statements so that the resulting statement is semantically identical but faster to process. The transformations are purely rule-based and do not take into account any statistical information. The main purpose of transformations is to avoid intermediate result sets.

In some cases, it may not be possible to display the statement that results from the transformation as an SQL statement.

After an SQL statement has been transformed, the SQL Optimizer determines the most cost-effective search strategy for accessing the data.

The system can transform the following types of SQL statements: SELECT, CREATE CURSOR FOR SELECT, EXPLAIN SELECT

After the system has transformed an SQL statement, the result of the EXPLAIN statement contains the text QUERY REWRITE DONE as well as a list indicating the rules executed and the number how often they were executed.

Can be changed in ONLINE or ADMIN operational state

-

ExclusiveLockRescheduleThreshold

MAXRGN_REQUEST

Maximum number of attempts a task should make to access a critical section

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 in ONLINE or ADMIN operational state

Glossary, Critical Section

FileDirectorySpinlockPoolSize

FILEDIR_SPINLOCKPOOL_SIZE

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

Changes only apply after a restart of the database

Glossary, Reader-Writer Lock

FormatDataVolume

FORMAT_DATAVOLUME

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

Changes only apply after a restart of the database

Glossary, Volume

HashJoinSingleTableMemorySize

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 in ONLINE or ADMIN operational state

See above

HashJoinTotalMemorySize

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 in ONLINE or ADMIN operational state

Glossary, Join

HighResolutionTimer

HIRES_TIMER_TYPE

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

Changes only apply after a restart of the database

-

HotStandbyDelayTime?

HS_DELAY_TIME_<NNN>

Only for hot standby systems

Delay in seconds that the standby instance uses for data changes in the master instance. A question mark (?) is the placeholder for the three-digit identification of the standby instance.

Changes only apply after a restart of the database

Database Administration, Hot Standby

HotStandbyNodeName?

HS_NODE_<NNN>

Only for hot standby systems

Host name or IP address of the hot standby instance

A question mark (?) is the placeholder for the three-digit identification of the standby instance.

The default value for the master instance is HotStandbyNodeName001.

Changes only apply after a restart of the database

See above

HotStandbyStorageDLLPath

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 a restart of the database

See above

HotStandbySyncInterval

HS_SYNC_INTERVAL

Only for hot standby systems

Minimum time span between two commands of the master instance to synchronize the standby instances (in s)

Changes only apply after a restart of the database

See above

IndexListsMergeThreshold

OPTIM_MAX_MERGE

Specifies the decision 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 IndexListsMergeThreshold, the SQL optimizer does not use this index for an Index Merge strategy.

Can be changed in ONLINE or ADMIN operational state

SQL Optimizer, Search Strategies

JoinSearchLevel

JOIN_SEARCH_LEVEL

Algorithm used by the SQL optimizer for the join sequence search

Can be changed in ONLINE or ADMIN operational state

SQL Optimizer,

Database Parameter JoinSearchLevel

JoinSearchTableThreshold4

JOIN_MAXTAB_LEVEL4

Parameter of the SQL optimizer

Can be changed in ONLINE or ADMIN operational state

JoinSearchLevel

JoinSearchTableThreshold9

JOIN_MAXTAB_LEVEL9

Parameter of the SQL optimizer

Can be changed in ONLINE or ADMIN operational state

JoinSearchLevel

JoinTableBufferSize

JOIN_TABLEBUFFER

Size of the buffer that the SQL optimizer can use for a read operation per join step

Can be changed in ONLINE or ADMIN operational state

SQL Optimizer, Joins

KernelMessageFileSize

KERNELDIAGSIZE

Size of the kernel log (in KB)

Changes only apply after a restart of the database

Database Administration, Log Files

KernelMessageHistoryCount

KNLMSG_HISTORY_NUM

Number of stored old kernel logs

The system moves the last kernel log to the log history directory at each database start

Changes only apply after a restart of the database

Database parameter DiagnoseHistoryPath

LoadBalancingCheckLevel

LOAD_BALANCING_CHK

Time interval in which the database system checks whether it should use load balancing (in s)

Changes only apply after a restart of the database

Concepts of the Database System, Load Balancing between User Kernel Threads

LocalRedoLogBufferSize

LOCAL_REDO_LOG_BUFFER_SIZE

If you enter a value > 0, then the system allocates a local buffer of this size (in bytes) to each transaction for saving redo log entries.

Changes only apply after a restart of the database

-

LogQueues

LOG_QUEUE_COUNT

Number of log queues

If this number is 0, the system calculates the number from the parameter MaxLogQueues

Changes only apply after a restart of the database

Glossary, Log Queue

LogQueueSize

LOG_IO_QUEUE

Size of the individual log queues (in pages)

Changes only apply after a restart of the database

See above

MaxEvents

_MAXEVENTS

Maximum number of events stored in the working memory by the kernel that can be processed by the system

Changes only apply after a restart of the database

Glossary, Event

MaxExclusiveLockCollisionLoops

MP_RGN_LOOP

Maximum number of times a task attempts 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 in ONLINE or ADMIN operational state

Glossary, Critical Section

MaxLogWriterTasks

MAX_BACKUP_TASKS

Maximum number of log writer tasks (defines the maximum number of log partitions)

Changes only apply after a restart of the database

Concepts of the Database System, Log Area and Tasks

MaxMemoryAllocationSize

MEMORY_ALLOCATION_LIMIT

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

Changes only apply after a restart of the database

-

MaxParallelLiveCacheTraceFiles

_MAX_MESSAGE_FILES

Maximum number of trace files that can be open simultaneously

Changes only apply after a restart of the database

-

MaxServerTasks

MAXSERVERTASKS

Number of server tasks

Changes only apply after a restart of the database

Concepts of the Database System, Tasks

OfficialNodeName

OFFICIAL_NODE

Only for hot standby systems

Virtual Server Name by which the cluster is addressed from the outside

The system administrator initially assigns the Virtual Server Name initial 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 a restart of the database

Glossary, Hot Standby

RequestTimeout

REQUEST_TIMEOUT

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

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 respective the transactions (ROLLBACK).

Changes only apply after a restart of the database

Concepts of the Database System, Locks

RequestUpdateStatisticsThreshold

-

If the system detects that a table's size has changed by more than the value specified in this parameter (in percent) since the last statistics update, it will mark the table as needing a statistics update.

Can be changed in ONLINE or ADMIN operational state

RowLockManagementStripes

_ROW_RGNS

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

This division enables parallel access to the lock list.

Changes only apply after a restart of the database

Glossary, Critical Section

RowLocksPerTransactionThreshold

-

Maximum percentage of all available locks that a transaction can hold before the system attempts a lock escalation.

Can be changed in ONLINE or ADMIN operational state

Glossary, Lock

SequenceCacheSize

SEQUENCE_CACHE

Size of the sequence cache (in pages)

Changes only apply after a restart of the database

Glossary, Sequence Cache

SessionTimeout

SESSION_TIMEOUT

Timeout value for database sessions (in s)

Can be changed in ONLINE or ADMIN operational state

Glossary, Database Session

SharedSQLCleanupThreshold

SHAREDSQL_EXPECTEDSTATEMENTCOUNT

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

Changes only apply after a restart of the database

-

SharedSQLCommandCacheSize

SHAREDSQL_COMMANDCACHESIZE

Maximum size of the Shared SQL cache (in KB)

Can be changed in ONLINE or ADMIN operational state

-

TableLockManagementStripes

_TAB_RGNS

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

This division enables parallel access to the lock list.

Changes only apply after a restart of the database

Glossary, Critical Section

TraceBufferSize<task_type>Task

TRACE_PAGES_<task_type>

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_type>:BUP (Backup Task, not yet implemented) |FS (Floating Service Task: either an event task or a Database Analyzer 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 a restart of the database

Concepts of the Database System, Tasks

TransactionHistorySize

TRANS_HISTORY_SIZE

Specifies for how many long-running transactions the system stores information

Changes only apply after a restart of the database

-

TransactionHistoryThreshold

TRANS_THRESHOLD_VALUE

Time (in s) after which a transaction is considered long-running by the system

Can be changed in ONLINE or ADMIN operational state

-

TransactionLockManagementStripes

_TRANS_RGNS

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

This division enables parallel access to the lock list.

Changes only apply after a restart of the database

Glossary, Critical Section

UpdateStatSampleAlgorithm

UPDATESTAT_SAMPLE_ALGO

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

In SAP systems we recommend the value 1 for this parameter.

Can be changed in ONLINE or ADMIN operational state

SQL Optimizer, SQL Optimizer Statistics

UseAutomaticBadIndexRecreation

AUTO_RECREATE_BAD_INDEXES

Indexes marked as BAD are recreated automatically after a restore.

Changes only apply after a restart of the database

Glossary, Index

UseBackupSecurityDescriptor

USE_BACKUP_SECURITY_DESCRIPTOR

Only for Microsoft Windows

Specifies whether the system uses the Microsoft Windows Security Descriptor for backups to data carriers of the file type

Changes only apply after a restart of the database

-

UseDataCacheScanOptimization

LRU_FOR_SCAN

Specifies whether the whole data cache is used for scans

Can be changed in ONLINE or ADMIN operational state

-

UseLobClustering

CLUSTERED_LOBS

Specifies whether the database system stores the LOB values as clusters in the data area

Can be changed in ONLINE or ADMIN operational state

-

UseSharedSQL

SHAREDSQL

Activates Shared SQL

Parameter can be activated in ONLINE or ADMIN operational state but not deactivated

Glossary, Shared SQL

UseUnicodeColumnCompression

-

Specifies whether application data in columns of type (VAR)CHAR UNICODE in newly created tables is stored as UTF8

Can be changed in ONLINE or ADMIN operational state

Glossary, Data Type

VolumeFormattingMode

FORMATTING_MODE

The database system formats the volumes when the database is created. With this parameter, you define the formatting type. Only use parallel formatting for data volumes of the FILE type that are located on different hard disks.

Changes only apply after a restart of the database

Glossary, Volume