Was this helpful?
Database Characteristics Affected by Alterdb
The alterdb command lets you disable journaling and change several database characteristics, including:
Change journal block settings
Delete oldest checkpoint
Set verbose mode
To perform this operation, you must be the owner of the database or have the operator privilege.
Journal File Size
Journal files are created by the archiver process by the first journal write after a checkpoint takes place. Additional journal files are created as prior files are filled.
By default, journal files are created with:
A target number of journal blocks of 512
A block size of 16384 bytes
An initial allocation of 4 blocks
This results in a target journal file size of 8 MB (16384 * 512 bytes). Although most users find these parameters satisfactory, all three can be modified by using the alterdb utility.
The alterdb command has the following syntax for altering block sizes:
alterdb dbname -target_jnl_blocks=n | -jnl_block_size=n | -init_jnl_blocks=n
Target Journal Size
The alterdb command specifies the target journal size in the following format:
alterdb dbname -target_jnl_blocks=n
where n is the number of blocks between 32 to 65536.
A journal file is closed and a new one is created when either a checkpoint is taken (actually, when the first write after a checkpoint is taken) or when the journal file fills.
The -target_jnl_blocks=n option of alterdb allows some control over when the logging system declares a journal file full. This parameter is known as the “target journal file size” because the exact size of a journal file cannot be easily predicted. The archiver closes off journal files, if they grow larger than the target number of blocks, only at the completion of an archive cycle. Longer archive cycles imply more variation in journal file sizes.
Upon successful completion of this command, a message is written to the errlog.log. The updated block value can be observed as the infodb parameter “Target journal size”.
The command takes effect immediately (or more accurately, the next time the archiver reads the configuration file).
The initial journal size (init_jnl_blocks) may be affected by this command.
Journal Block Size
The alterdb command specifies the journal block size in the format:
alterdb dbname -jnl_block_size=n
Only one database name is required. Valid journal block sizes are 4096, 8192, 16384, 32768, and 65536 bytes.
Archiver (dmfacp) performance is affected by the journal file block size. You normally change the block size (Size edit control) in conjunction with the number of target journal blocks (-target_jnl_blocks). Doing so allows you to target the creation of journal files of a given size. Changing the block size without also changing the number of blocks in a journal file changes the target size of the file.
You typically change the journal block size immediately after the database is created, before the initial checkpoint is taken with the journaling option. Thereafter, changing the journal block size is generally required only for installations with a relatively high volume of journaled data. You can only change the journal block size when journaling is not currently enabled.
To change the journal block size on a database that is currently journaled
1. Take a checkpoint and disable journaling:
ckpdb -j dbname
2. Set the journal block size:
alterdb dbname -jnl_block_size=n
3. Take a checkpoint and enable journaling:
ckpdb +j dbname
When this operation completes successfully, a message is written to the errlog.log. The updated journal file block size can be observed as the infodb “Journal block size” parameter.
Initial Journal Size
The alterdb command specifies the initial journal size in the format:
alterdb dbname -init_jnl_blocks=n
where n is a number of blocks from 0 to the current target journal size (which can be obtained using infodb). Only one database name is required. The -init_jnl_blocks=n option allows a measure of control over when journal file disk space allocation takes place, but only for the first journal file created after a ckpdb command.
This alterdb command can be issued at any time, and takes effect when the next database journal file is created. In the case of an offline checkpoint, this can be some time after the ckpdb command is issued. In the case of an online checkpoint, the file allocation occurs during execution of the checkpoint.
Upon successful completion of this command, a message is written to the errlog.log. The updated block value can be observed as the infodb “Initial journal size.”
Considerations When Resizing Journal Files
Preallocating space in journal files using alterdb can reduce the likelihood of running out of journal file disk space.
Filling a journal file causes the archiver to stop, and if left untreated, eventually causes the log file to fill, which brings the system to a halt.
With the alter database operation you can, for example, request creation of journal files of a given size and also request preallocation of the entire file. If the file is sufficiently large, this eliminates the possibility of running out of journal disk space during normal online processing.
This can, however, cause unused journal space to be wasted. If excessive space is allocated during journal file creation, that disk space can be made unavailable when a subsequent checkpoint operation takes place.
If it is necessary to control journal file size more accurately, the archiver must be awakened more frequently. This can be accomplished with smaller consistency point (CP) intervals, allowing more frequent archiver “wake-ups.” The consistency point interval can be configured using CBF (or the Configuration Manager, if available). Smaller CP intervals can affect system performance, although the processing involved is for a short interval of time.
Considerations When Resizing Journal Files on UNIX
On UNIX systems, disk space must be physically written when a journal file is extended. When a journal file is filled, a new one is created. It is undesirable for performance to be affected by file allocation that occurs at unplanned intervals.
You can use the alter database space preallocation features to manage when the allocation takes place, allowing control over when the allocation time delay occurs. A significant amount of journal file I/O can occur when the first journal file is created, with the archiver being unavailable during this time. This can be observed as an online checkpoint taking a long time to complete, or the archiver performing a large amount of work when the first journal write after an offline checkpoint takes place.
Last modified date: 11/28/2023