DB2 Backup Recovery

Backup and recovery

Backup and recovery are the basic requirements for ensuring business continuity when the media fails. The MySQL backup and recovery options depend heavily on the underlying table type. For example, both MyISAM and InnoDB tables allow cold database backup. However, since MySQL 4.0InnoDB Hot Backup

(InnoDB is an Oracle Corp. Product), but MySQL 5.1 still does not support MyISAM hot backup.

DB2 Express
Cold backup and hot backup are provided from the very beginning. Cold backup means that when the user is offline, the user does not access the database being backed up. This means that when using cold backup, if the media fails,
Data after the last backup will be lost. Hot Backup allows transactions to continue writing logs without shutting down the database being backed up. With proper planning, hot backup ensures that data is not lost when the media fails.
This backup is required in 24x7 environments.
DB2 Express has two record Methods: cyclic record method and archive record method. You can perform cold backup by using the cyclic record method (which is written cyclically and overwritten). The Archive record method (archived content already submitted) supports hot backup.
Selecting cold backup or hot backup depends on the parameters set in dB config:

Logfilsiz -- log file size. The total number is 4 kb (250 by default)
Logprimary -- number of Master logs (mod 3)
Logsecond-Number of secondary logs that can be allocated when the primary log is filled.
Newlogpath -- change the location of subsequent log file storage. It takes effect only when the database is reactivated.
Mirrorlogpath -- the backup path for writing logs to avoid single point of failure.
Overflowlogpath -- specifies the log location in the rollback operation to access logs in multiple locations.

Failarchpath -- failarchpath)
If the specified log archiving method fails, specify a backup directory for the archived log file. Before the failed log archiving method is available again, this directory is a temporary storage area for log files.
To the log archiving method. By moving the log file to this temporary location, you can avoid the log directory being full. This parameter must be a fully qualified existing Directory.
Cold backup is simple. First shut down the database and then issue a Backup command, as shown in figureDB2 backup database to C:/backup
.
Hot Backup is also simple. First open the Log Retention parameter and then issue the command, as shown in figureDB2 backup database online to C:/backup
.
Both cold backup and hot backup can be completed by clicking a few clicks in the DB2 express control center. In addition, DB2 Express provides incremental backup and Delta backup.

Incremental backup-backup all changes after the last successful full backup.
Delta backup-all changes after the last successful full backup, Incremental backup, or Delta backup
For backup and recovery issuesArticleFor more details.

 

Before Version 9, DB2 has three compression methods: Space Value compression (value compress clause), index compression (MDC technology), and database backup compression ｡

When Space Value compression (value compress clause) is used, variable-length data types (varchar, vargraphics, long
Varchar, long vargraphic, blob, clob, and dbclob)
Null and zero-length data are stored on the disk. Only overhead values associated with these data types occupy disk space.
Compress, you can also use the optional compress System
The default option is used to further reduce disk space usage. If the inserted or updated value is equal to the system default value of the column's data type, the disk space is used at least. The default value is not stored on the disk.
Supported compress system default data types include all numeric type columns, fixed length characters, and fixed length graphics string data types ｡

Specify organize by when creating a table
The dimensions option enables MDC for a table. One or more keys can be specified for latitude. MDC uses a block-based index,
A block index points to a record block or record group, instead of a single record. Data in the MDC table is physically organized into Blocks Based on the cluster value, and then block indexes are used to access these blocks, can significantly improve performance.
In MDC, cluster indexes are block-based. These indexes are much smaller than conventional record-based indexes. Therefore, they consume less disk space and scan faster ｡

If the data volume in the database is large, you can use the backup compression function by specifying the compress option when backing up the database, instead, the minimum storage space required for backing up the image file ｡

DB2
V9 adds the dictionary-based row compression function that can be used to compress data objects. When compressing data, the same data is represented by a small number of database pages, to save disk storage space.
Rows that contain large tables in the repeat mode will benefit from this function. The compress clause can be used to compress data rows with existing space values (Value
Compress clause). For tables that use row compression, the query performance may be improved. Less I/O is required.
Operation to access compressed data, and after compression, more data can be cached in the buffer pool at high speed. Because user data is compressed in the log records, the log records may become smaller. For update
This may not happen if logs are recorded. The cost associated with row compression depends on the additional CPU required to compress and decompress the data.
Cycle. compression and decompression are performed in the unit of action when accessing data in the row. To evaluate the savings in memory after row compression, you can use DB2 inspect for online practical use.ProgramOf
Rowcompestimate option. The row can be compressed only after the compress attribute of the table is enabled and the compression dictionary is created.
You can use the reorg table command to create a compression dictionary.

Command, all existing table rows must be compressed. Data row compression is not applicable to indexes, lob, lf, or XML objects ｡

When you store a table on a disk, if you use features such as compression for data rows, null values, and system defaults, the table may occupy less space, A small number of database pages can be used for storage.
Data to save disk storage space. Because more logical data can be stored on each page, therefore, the number of pages to be read when accessing the same amount of Logical Data is smaller, which means compression can save disk I/O ｡
I/O speed is also accelerated because more logical data can be cached in the buffer pool ｡