Hi,

DB2 is that any way to restore by table?

I got the database backup image, I wish to only restore one of the table inside rather then need me to restore entired database. Is that possible? or any backup method/tool that can do this then later can restore by table? Is not, can restore by user? or container?

Please advise

Regards
SiKar

As far as I know, a restore per table is not possible.
You can do a tablespace backup/restore, so if you create a separate tablespace for critical tables you can get what you're looking for

Peter

Correct, restore at table level is not possible. But why not take the backup and create a temp database from the backup. Exports the table contents as IXF and import it into the regular databasetable.....

If you are on mainframe, DSN1COPY should help

Cheers
Sathyaram

Thank, platho, blom0344 & sathyaram_s,

Look like restoring by table is not possible, unless using Export & Import Database.

Platho - Tablespace Backup/Restore, do you mean issue the back by specificed the container ?

Blom0344 - Why need to create temp database, can't we just direct export the database online from production environment? Please explain in brief. Thank

Sathyaram_s - my environment is AIX environment.

So what you people do in case the developer always (half year) want to restore only one table from big DB2 database? Please advise.

Regards
SiKar

Hi,

Since a container is a part of a tablespace it's not possible to only restore one container, but only the whole DB. We also have a very large DB and sometimes a request comes in to restore 1 table. During time we gathered enough information to know what table restores are requested most. For those, we set-up separate tablespaces. In the other case a backup to a temporary database and export/import is necessary.

Peter

A backup file from DB2 database has little use in itself, if you want to extract data from it you will need to restore it somewhere else. I believe for other major databases there are third party tools that let you extract something from the backup file itself, do not think something like that exists for DB2

(But I hope to be corrected on this point )

You may be able to use the following technique

RECOVERY OF ACCIDENTALLY DROPPED TABLES




1. Setup the database for Table recovery

1) The archival logging should be tuned on.

2) The tablespace should be created with DROPPED TABLE RECOVERY ON.
If this was not done during creation of the tablespace it can be done with ALTER TABLESPACE Statement.
ALTER TABLESPACE <tablespace-name> DROPPED TABLE RECOVERY ON

2. Procedure

To recover an accidentally dropped table:

1) List the history file to get information about the dropped table id and the DDL to create the table.

DB2 LIST HISTORY DROPPED TABLE <qualified table-name> FOR <database name>

From the listing get the backup id and the DDL for table creation.

2) Restore the database from the backup image available using the RESTORE backup command. This can be either database level restore or a tablespace level restore.

3) Rollforward the database/tablespace to end of logs or point in time using RECOVER DROPPED TABLE option.
ROLLFORWARD DATABASE <db-name> TO END OF LOGS AND COMPLETE RECOVER DROPPED TABLE <backup-id> to <path>

To a Point-in-Time can be used instead of TO END OF LOGS.
Backup-id is the one obtained from the list history command.
Path is where the file containing the rows of the dropped table is to be put.

4) Create the table by using the DDL in the output of the LIST HISTORY command

5) Import the data from the file named ‘data’ in ‘path’

If more than one dropped table is the recovered the Rollforward process is to be repeated for each table. In this case, do not include ‘AND COMPLETE’ option of the ROLLFORWARD command except for the last table.

Restoring a table IS possible - only IF you store one table in one tablespace. Having 1:1 tables/tablespaces seems to have no performance issues.
PROS - now u can restore any single table.
CONS - slightly more DBA work to do.

Cheers, Bill

Other probelm with 1 table per tablespace is that often there is not very efficient space utilization for DMS tablespaces. That is, you will need a lot more empty space in the tablespaces to allow for growth when there is only one table per tablespace. For a small database this may not be a problem, but for a large database it can eat up a lot of disk space.

Thank everyone with your valuable comment.

Regards
SiKar

With "normal" DB2 tools the smallest restore object is a table space.
It's possible to restore a full backup into a new DB and then export the table data & DDL to recreate & load it in the original DB. The DB keeps available.

If you have HPU (DB2 Tool "High Performance Unload") (Try&Buy-Version?) you can extract the table data from a full backup of the DB or table space to load it in the (new created) table.