DB2: not able to restore from backup

Question

I am using command

db2 restore db S18 from /users/intadm/s18backup/ taken at 20110913113341 on /users/db2inst1/ dbpath on /users/db2inst1/ redirect without rolling forward
to restore database from backup file located in /users/intadm/s18backup/ .

Command execution gives such output:

SQL1277W  A redirected restore operation is being performed.  Table space
configuration can now be viewed and table spaces that do not use automatic
storage can have their containers reconfigured.
DB20000I  The RESTORE DATABASE command completed successfully.

When I'm trying to connect to restored DB (by executing 'db2 connect to S18'), I'm getting this message:

SQL0752N  Connecting to a database is not permitted within a logical unit of
work when the CONNECT type 1 setting is in use.  SQLSTATE=0A001

When I'm trying to connect to db with db viewer like SQuireL, the error is like:

DB2 SQL Error: SQLCODE=-1119, SQLSTATE=57019, SQLERRMC=S18, DRIVER=3.57.82

which means that 'error occurred during a restore function or a restore is still in progress' (from IBM DB2 manuals)

How can I resolve this and connect to restored database? UPD: I've executed db2ckbkp on backup file and it did not identified any issues with backup file itself.

Answer 1

What I did and what has worked:

Executed:

db2 restore db S18 from /users/intadm/s18backup/ taken at 20110913113341 on /<path with sufficient disk space> dbpath on /<path with sufficient disk space>

I got some warnings before, that some table spaces are not moved. When I specified dbpath to partition with sufficient disk space - warning has disappeared.

After that, as I have an online backup, I issued:

db2 rollforward db S18 to end of logs and complete

That's it! Now I'm able to connect.

Answer 2

When you do a redirected restore, you are telling DB2 that you want to change the locations of the data files in the database you are restoring.

The first step you show above will execute very quickly.

Normally, after you execute this statement, you would have one or more SET TABLESPACE CONTAINERS to set the new locations of each data file. It's not mandatory to issue these statements, but there's no point in specifying the redirect option in your RESTORE DATABASE command if you're not changing anything.

Then, you would issue the RESTORE DATABASE S18 COMPLETE command, which would actually read the data from the backup image, writing it to the data files.

If you did not execute the RESTORE DATABASE S18 COMPLETE, then your restore process is incomplete and it makes sense that you can't connect to the database.

Answer 3

without rolling forward can only be used when restoring from an offline backup. Was your backup taken offline? If not, you'll need to use roll forward.