Database server maintenance tasks

In addition to monitoring the database server for potential problems, regularly perform routine maintenance tasks to keep the server running smoothly and with optimum performance.

You can use the Informix® command-line utilities to perform the following tasks. Not all of the following tasks are appropriate for every installation.

Backup data and logical log files
To ensure that you can recover your databases in the event of a failure, make frequent backups of your storage spaces and logical logs. You can create backups with the ON-Bar utility or the ontape utility.
Check data for consistency
To ensure that data is consistent, perform occasional checks.
Manage logical logs
To ensure database server performance, perform logical-log administration tasks, such as, backing up logical-log files, adding, freeing, and resizing logical-log files, and specifying high-watermarks. The database server dynamically allocates logical-log files while online to prevent long transactions from blocking user transactions.
Manage the physical log
To ensure database server performance, make sure that you allocate enough space for the physical log. You can change the size and location of the physical log. When the database server starts, it checks whether the physical log is empty because that implies that the server shut down in a controlled fashion. If the physical log is not empty, the database server automatically performs a fast recovery. Fast recovery automatically restores the databases to a state of physical and logical consistency after a system failure that might have left one or more transactions uncommitted.
Manage shared memory
To ensure that the database server has the appropriate amount of shared memory to maintain performance goals, perform the following tasks:
  • Changing the size or number of buffers (by changing the size of the logical-log or physical-log buffer, or changing the number of buffers in the shared-memory buffer pool)
  • Changing shared-memory parameter values
  • Changing forced residency (on or off, temporarily or for a session)
  • Tuning checkpoint intervals
  • Adding segments to the virtual portion of shared memory
  • Configuring the SQL statement cache to reduce memory usage and preparation time for queries
Manage virtual processors
To ensure database server performance, configure enough virtual processors (VPs). The configuration and management of VPs has a direct affect on the performance of a database server. The optimal number and mix of VPs for your database server depends on your hardware and on the types of applications that your database server supports.
Manage the database server message log
To ensure that message log space does not fill, monitor the size of the database server message log. The database server appends new entries to this file. You can enable the automatic rotating of the database server message log to limit the total size of the log files.

Copyright© 2019 HCL Technologies Limited