can I restore only a particular table

Anne Stout

can I restore only a particular table
On DB2 UDB database, if I take a full online backup everyday, can I restore only a particular table from the whole backup image rather than restoring full db/full tablespace?

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

Ian Bjorhovde

Re: can I restore only a particular table
(in response to Anne Stout)
On Tue, Jan 19, 2010 at 12:42 PM, anne Stout <[login to unmask email]> wrote:
>
> On DB2 UDB database, if I take a full online backup everyday, can I restore
> only a particular table from the whole backup image rather than restoring full db/full
> tablespace?

Nope - tablespace-level is the most granular that you can get (without
resorting to another tool like Recovery Expert).

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Philip Gunning

Re: can I restore only a particular table
(in response to Ian Bjorhovde)
As Ian said, only can do at tablespace level. A workaround you could use is to only create one table per tablespace, then you could backup and restore the tablespace, which would in effect give you table level restore. Just do the same to the index tablespace if the indexes are in a separate tablespace. Of course you can also export the table data as IXF and load or import it as needed. PG



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of anne Stout
Sent: Tuesday, January 19, 2010 2:42 PM
To: [login to unmask email]
Subject: [DB2-L] can I restore only a particular table



On DB2 UDB database, if I take a full online backup everyday, can I restore only a particular table from the whole backup image rather than restoring full db/full tablespace?

_____

< http://www.idug.org/db2-north-america-conference/index.html > IDUG - The Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please < http://www.idug.org/register > register here.


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Mark Horrocks

Re: can I restore only a particular table
(in response to Philip Gunning)

Another manageble solution depending on the size of the table is to backup the table to a federated database and then restore from the federated database.



This can be applied to hot tables etc.



Many Thanks,



Mark Horrocks.



Date: Wed, 20 Jan 2010 01:06:02 -0500
From: [login to unmask email]
Subject: Re: [DB2-L] can I restore only a particular table
To: [login to unmask email]







As Ian said, only can do at tablespace level. A workaround you could use is to only create one table per tablespace, then you could backup and restore the tablespace, which would in effect give you table level restore. Just do the same to the index tablespace if the indexes are in a separate tablespace. Of course you can also export the table data as IXF and load or import it as needed. PG


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of anne Stout
Sent: Tuesday, January 19, 2010 2:42 PM
To: [login to unmask email]
Subject: [DB2-L] can I restore only a particular table

On DB2 UDB database, if I take a full online backup everyday, can I restore only a particular table from the whole backup image rather than restoring full db/full tablespace?




The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_________________________________________________________________
Tell us your greatest, weirdest and funniest Hotmail stories
http://clk.atdmt.com/UKM/go/195013117/direct/01/
_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

tim malamphy

Re: can I restore only a particular table
(in response to Mark Horrocks)
Yes, as Phil indicates,  it's much better if you can plan ahead and keep 1 table per tablespace.  But if you didn't implement that ahead of time, and are stuck with a broken table,  you can restore the whole database elsewhere,   do an export for that table, and then import/load that data back into a production table.  The indexes will be rebuilt.

--- On Tue, 1/19/10, Phil Gunning <[login to unmask email]> wrote:

From: Phil Gunning <[login to unmask email]>
Subject: Re: [DB2-L] can I restore only a particular table
To: [login to unmask email]
Date: Tuesday, January 19, 2010, 10:06 PM













As Ian said, only can do at tablespace level. A workaround you
could use is to only create one table per tablespace, then you could backup and
restore the tablespace, which would in effect give you table level restore. Just
do the same to the index tablespace if the indexes are in a separate tablespace.
Of course you can also export the table data as IXF and load or import it as
needed. PG

 



From: IDUG DB2-L
[mailto:[login to unmask email] On Behalf Of anne Stout

Sent: Tuesday, January 19, 2010 2:42 PM

To: [login to unmask email]

Subject: [DB2-L] can I restore only a particular table



 

On DB2 UDB database, if I take a full online backup
everyday, can I restore only a particular table from the whole backup image
rather than restoring full db/full tablespace?









The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please
register here.











The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.






_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Peter Vanroose

Re: can I restore only a particular table
(in response to tim malamphy)
Btw, the same holds when you need to restore only a selected number of rows
of a table, e.g. after an unwanted DELETE ... WHERE ...

-- Peter Vanroose
ABIS Training & Consulting.

On Fri, 22 Jan 2010 21:11:36 -0800, tim malamphy <[login to unmask email]> wrote:
>Yes, as Phil indicates, it's much better if you can plan ahead and keep 1
table per tablespace.
> But if you didn't implement that ahead of time, and are stuck with a
broken table, you can restore the whole database elsewhere, do an export for
that table, and then import/load that data back into a production table. The
indexes will be rebuilt.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L