database backup
Before the backup database, make sure that the DB2 has one of the following permissions:
Sysadm
Sysctrl
Sysmaint
For these examples, create a directory that stores database images, such as C: / Backup. There is no need to connect to a database for backup, because the backup command is automatically created. Also, make sure there is no other application to connect to a database when you perform an offline backup, otherwise you will receive a SQL1035N error message. DB2 "List Applications" and DB2 "Force Applications All" are useful commands that complete this task.
There are two ways to perform backups. The first is the offline database backup. This backup method requires exclusive connections to the database because all tablespaces in the backup database are backed up. The second is the online database backup. This backup method is particularly useful for users who run the production database and require continuous running of one or more tablespaces in a database. When using online backup, only the table space to be backed up is required to have exclusive connections. This allows other tablespaces in the database that do not require backups remain online for additional application access.
Note: If you do an online backup, make sure that the front rollback recovery parameter logretain = On or UseRexit is enabled in the Database Manager profile. Otherwise, a SQL2413N error will be received and the following command is required:
DB2 "Update DBM Configuration Using Logretain"> DB2Stop> DB2Start
After enabling the logretain and / or useexit parameters, a complete database offline backup. You can perform verification by performing the following commands to see if the database is in a backup state:
DB2 "Get Database Configuration for Sample"
Then, look for the "Backup Pending" value (it will be set to "yes" or "no"). After the full database backup, you will be able to perform online tablespace backup at any time.
Example 1 - Back up the database to the local directory
DB2 "Backup Database Sample User DB2Admin Using DB2ADMINTO C: / Backup with 3 Buffers Buffer 1000 WITHOUT" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "
This command will use the user DB2Admin and password DB2Admin (only the user and password only need to use the user and password only when the current user has the required database permissions) generates a backup image of the database "Sample". This image will be stored in C: / Backup using 3 buffers, each buffer size of 1000 pages. "WITHOUT Prompting" button Short language Specify backup will run in unattended way, and any operation that usually requires user intervention will return an error message.
Example 2 - Back up the tablespace level to tape devices
DB2 "Backup Database Sample TableSpace (Syscatspace, UserSpace1) Online to / dev / RMT0 without Prompting
This command will generate a backup image of the specified table space within the database. Syscatspace and UserSpace1 are tablespaces that reside in the Sample database. The tape drive is / DEV / RMT0. Because the number of buffers and buffers pages are defined in the command, the default value will be buffers = 2 and buffer (size) = 1024 pages.
The advantage of the online tablespace backup is that all other tablespaces in the database can be used for processing. The default is "offline", which requires disconnection of all applications and databases when performing backups.
After performing these commands, you should receive the following message on the DB2 command line: "Backup successful. The timestamp for this backup image is: timestamp" TimeStamp is the unique identifier for each database backup image, there are more in a single folder A backup image, you need this identity when you perform recovery. On UNIX systems, timestamps and backup image file name connections: "dbname.type.instance.nodexxx.catnxxx.yyyymmddhhmmms.seq" on the Windows NT system, backup image is stored in a 5-level directory tree This catalog also includes this timestamp information: "dbname.type / db2instance / nodexxx / catnxxxx / yyyymmdd / hhmms.seq"
For information on the database you need to back up, enter the following command:
DB2 "List History Backup All for Database Sample"
Database recovery
Before restoring the database, there is one of the following permissions to DB2:
Sysadm
Sysctrl
Sysmaint
If it is restored to a new database (not an existing database), SYSADM or SYSCTRL is required.
Offline database recovery will require an exclusive connection; so there is no application to connect to the database during this task. Moreover, make sure there is no other application to connect to the database when you perform an offline recovery, otherwise you will receive a SQL1035 error message.
Example 1 - Restore from the local directory to an existing database
DB2 "RESTORE DATABASE SAMPLE User DB2ADMIN Using DB2ADMINFROM C: / Backup Taken AT 20040222145404 with 3 Buffers Buffer1000 Without Rolling Forward without Prompting"
This command will save the saved database image with timestamp 20040222145404 to the existing Sample database (see how to get information about how to get the timestamp value mentioned earlier). This command will effectively overwrite the old Sample database file with the backup database image file. Key Shortey "without rolling forward" will prevent the database manager from being restored to the front roll state (if the database is enabled if the database is not enabled for the backup database, the phrase is not required). If you are interested in returning all database files to the last successful transaction, and when the backup database is enabled, it is enabled for the "WITHOUT ROLLING FORWARD" button short language.
If you do not use the keyword "from", the current directory will be the default location of the backup image. Moreover, if only one database backup image resides in the target folder, it is not required to "Taken At TimeStamp".
Example 2 - Restore the tablespace from the tape device to the existing database
DB2 "RESTORE DATABASE SAMPLE TABLESPACE (Syscatspace, UserSpace1) Online from / dev / rmt0 Taken AT 2001022151429 NewLogPath / Home / DB2V71 / SampleLogs without Prompting
This command restores the SyscatSpace and UserSpace1 tablespace to an existing Sample Database while allowing unlimited handling of other tablespaces that online recovery commands. Moreover, if there are multiple backup images in the same folder, the "Taken At TimeStamp" key short language must be declared. If the logPath in the backup image is not suitable after recovery, the "NewLogPath" keyword can be included. For example, when the path is no longer valid or it is currently being used by another database. Example 3 - Restoring to the new database
DB2 "Restore Database Sample From / Home / DB2V71 / Backup INTO NEWSAMP for PROMPTING"
This command will restore the Sample database backup image to the database newsamp. After performing the recovery command, DB2 will create a "newsamp" database immediately. Then, the backup image will be restored to the "Newsamp" database.
Note: If there is only one backup database image in the / home / db2v71 / backup directory, do not use the "Taken At TimeStamp" button short language.
When you create a copy of the existing database, the above command is useful. The Sample database will not be affected by the recovery process, so it is still available for future use. You can also use the same recovery command to restore to an existing database. For example, you may have created a newsamp database before performing the above command.
Note: If this feature is intended, the code page to be restored to it must match the code page of the backup database image.
Additional Note: If the platform, DB2 version number, and version sequence numbers are identical, you can return to an existing remote database. If you are authenticating the user ID, make sure that there is a previously mentioned permissions to perform remote recovery. In addition, the database to be recovered to the local machine to perform remote recovery (this is required) to connect to the database to be recovered.
