Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Location
    Kuching/Malaysia
    Posts
    17

    Question Unanswered: DB2 Restore Per Table?

    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

  2. #2
    Join Date
    Dec 2002
    Posts
    58
    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

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    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.....
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you are on mainframe, DSN1COPY should help

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Dec 2003
    Location
    Kuching/Malaysia
    Posts
    17
    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

  6. #6
    Join Date
    Dec 2002
    Posts
    58
    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

  7. #7
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    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 )
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    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

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Dec 2003
    Location
    Kuching/Malaysia
    Posts
    17
    Thank everyone with your valuable comment.

    Regards
    SiKar

  12. #12
    Join Date
    Jun 2004
    Posts
    1

    Lightbulb

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •