Offline backup steps for DB2 Databases

Steps for backing up a DB2 database:

1. Create a new database

D: \ ProgramFiles \ IBM \ sqllib \ bin> DB2

(C) Copyright IBM Corporation 1993,2007

DB2 client 9.7.4 command line Processor

 

You can issue Database Manager commands and SQL statements from a command prompt. For example:

DB2 => connect to sample

DB2 => bind sample. Bnd

 

For general help, enter :?.

For Command help, enter :? Command, where command can be

The first few keywords of the Database Manager Command. For example:

? Catalog Database is used to help with the catalogdatabase command

? Catalog is used to help all catalog commands.

 

To exit the DB2 interaction mode, enter

Quit. In non-interactive mode, all commands must be prefixed with "DB2.

To list the settings of the current Command Options, enter LIST Command Options.

 

For more help, see online reference manual.

 

DB2 => drop database mydb

The sql1035n database is currently in use. Sqlstate = 57019

DB2 => connect to Henry

 

Database connection information

 

Database Server = DB2/nt64 9.7.4

SQL authorization id = db2admin

Local Database alias = Henry

 

DB2 => drop database mydb

The db20000i drop database Command is successfully completed.

DB2 => Create Database mydb

The sql1047n application has been connected to another database.

DB2 => connect Reset

Sql1224n failed to accept new requests due to an error in the Database Manager or forced interruption. The request has been terminated.

 

All the requests being processed or the specified request has been terminated. Sqlstate = 55032

DB2 => Create Database mydb

The db20000i create database Command is successfully completed.

DB2 =>

2. Create a table to simulate restoration when data is lost in the table.

DB2 => connect to mydb

 

Database connection information

 

Database Server = DB2/nt64 9.7.4

SQL authorization id = db2admin

Local Database alias = mydb

 

DB2 => Create Table Test (ID char (10 ))

The db20000i SQL command is successfully completed.

DB2 => insert into test values ('1 ');

Db21034e this command is processed as an SQL statement because it is an invalid "command line processor. In

During SQL processing, it returns:

Sql00000n finds the exception mark after "ntotest values ('1 ')"

";". The expected tags may include: "end-of-statement ". Sqlstate = 42601

DB2 => insert into test values ('1 ')

The db20000i SQL command is successfully completed.

DB2 => commit work

The db20000i SQL command is successfully completed.

DB2 => select * from test

 

ID

----------

1

 

1 record selected.

 

3. Make an offline backup.

DB2 => List applications # display the database application status

DB2 => Force applications all # Stop database applications.

The db20000i Force Application Command is successfully completed.

Db21024i this command is asynchronous and may not take effect immediately.

 

DB2 => List applications

Sql1611w "database system monitor" does not return any data.

DB2 =>

DB2 => backup database mydb to D: \ backup # perform full offline backup.

 

The backup is successful. The time stamp of this backup image is: 20110925085106

 

DB2 => List history backup all for mydb # view backup history

 

List history files of mydb

 

Number of matched file entries = 1

 

 

Opobj time stamp + sequence type device earliest log current log backup ID

--------------------------------------------------------------------

B D 20110925085106001 F D s0000000.log s0000000.log

----------------------------------------------------------------------------

Contains 3 tablespaces:

 

00001 syscatspace

00002 userspace1

00003 define oolspace

----------------------------------------------------------------------------

Note: DB2 backup mydb offline

Start Time: 20110925085106

End Time: 20110925085109

Status:

----------------------------------------------------------------------------

Eid: 2 Location: D: \ backup