DB2 Backup Recovery

backup and restore
Backup and Recovery is the basic requirement to ensure business continuity in the event of media failure. The MySQL backup and recovery option relies heavily on the underlying table type. For example, MyISAM and InnoDB table types allow for a database cold backup. However, starting with MySQL version 4.0, InnoDB hot Backup (InnoDB is Oracle Corp. 's product) is also provided as a commercial attachment, but MySQL 5.1 still does not support hot backup of MyISAM.


DB2 Express provides cold and hot backups from the outset. Cold backup is when the user is offline and does not access the database being backed up. This means that when a cold backup is used, the data after the last backup is lost if media failure occurs. Hot backup allows the transaction to continue writing to the log without shutting down the database that is being backed up. With proper planning, hot backups can ensure that no data is lost without the media being invalidated. This backup is required on a 24x7 environment. The
DB2 Express has two kinds of recording methods, namely, circular recording method and archival recording method. The circular recording method (circular write, overwriting after full) can be used for cold backup, and the archival recording method (archived already submitted content) supports hot backup. The
choose whether a cold or hot backup depends on the parameters set in db Config:

logfilsiz--log file size. Total 4KB (default)
logprimary--number of primary logs (modulo 3)
logsecond--the number of secondary logs that can be allocated when the primary log fills up. The
newlogpath--changes the location of subsequent log file storage. Takes effect only when the database is reactivated. The
mirrorlogpath--writes a fallback path to the log to avoid a single point of failure. The
overflowlogpath--specifies the log location in the roll forward operation to access the log in multiple locations.

failarchpath--Failover Archive Path (Failarchpath)
If the specified log archive method fails, an alternate directory is specified for the archive log file. This directory is a temporary storage area for log files until the failed log archive method is available again, and the log files are moved from this directory to the log archive method. By moving the log file to the temporary location, you can avoid the log directory from being full. This parameter must be a fully qualified existing directory.
Cold backup is simple. Close the database first, and then issue a backup command, such as DB2 backup DB to C:/backup.
Hot backups are also simple. First open the log retention parameters and then issue a command, such as DB2 backup database online to C:/backup.
Both cold and hot backups can be done by clicking a few mouse clicks in DB2 Express control Center. In addition, DB2 Express also offers incremental and delta backups.

Incremental backup--all changes since the backup of the last successful full backup.
Delta Backup--all changes after the last successful full backup, incremental backup, or delta Backup
Questions about backup and recovery will be discussed in more detail in future articles.

Prior to version 9, DB2 had three compression methods, namely space value compression (value compress clause) ､ index compression (MDC technology) and database backup compression ｡
When using space value compression (value compress clause), NULL values for variable-length data types (Varchar､vargraphics､long Varchar､long Vargraphic､blob､clob and Dbclob) are not used (NULL) and 0 length data stored on disk ｡ Only the cost values associated with these data types consume disk space ｡ If you use the value COMPRESS, you can also use the optional COMPRESS SYSTEM default option to further reduce disk space consumption ｡ If the inserted or updated value equals the system defaults for the data type of the column, then the minimum disk space is used ｡ The default value will not be stored on disk ｡ Data types that support compress SYSTEM default include all numeric type columns ､ fixed-length characters and fixed-length graphic string data types ｡ This means that 0 and spaces can compress ｡

By specifying the Organize by Dimensions option when creating a table, the MDC feature for the table is enabled, and one or more keys can be specified for weft ｡ The MDC uses a block based index that points to a record block or group of records, rather than to a single record ｡ You can significantly improve performance by organizing data from the MDC table physically into blocks based on the cluster values, and then using block indexes to access these blocks ｡ In the MDC, clustered indexes are based on blocks of ｡ These indexes are much smaller than regular file-based indexes, so they take less disk space and are faster to scan ｡

If there is a large amount of data in the database, you can use backup compression when backing up the database by specifying the Compress option, which is the minimum storage space required for the backup image file ｡

DB2 V9 adds a dictionary-based row compression feature that can be used to compress data Objects ｡ When compressing data, you can save disk storage space by using fewer database pages to represent the same data ｡ Large tables that contain duplicate patterns in those rows will benefit from this functionality ｡ Data row compression (compress clause) can be used in conjunction with existing space value compression (value compress clause) ｡ Query performance may be improved for tables that use row compression ｡ Less I/O operations may be required to access compressed data, and more data can be cached in the buffer pool after compression ｡ Because user data is compressed into the log record, logging may become smaller ｡ This may not happen for UPDATE logging ｡ The cost associated with row compression depends on the additional CPU cycles required to compress and decompress the data ｡ When you access data in a row, compression and decompression is performed in the behavior unit ｡ To evaluate the savings after using row compression, you can use the rowcompestimate option of the DB2 INSPECT online utility ｡ You can compress rows after the Compress property of the table is enabled and the compression dictionary is created ｡ You can set the COMPRESS property by using the CREATE or ALTER TABLE statement ｡ You can use the REORG TABLE command to create a compressed dictionary ｡ In the process of REORG
command, all existing table rows are compressed ｡ Data row compression does not apply to index ､LOB､LF or XML objects ｡

When you store a table on disk, a table may take up less space if you use features such as compression for ､ null values and system defaults on data rows ｡ With data compression, you can save disk storage space by using fewer database pages to store data ｡ Since more logical data can be stored per page, the number of pages that need to be read is less ｡ to access the same amount of logical data This means that compression can also save disk I/o｡ I/O speed also accelerates because more logical data can be cached in the buffer pool ｡