DSN1COPY and Segmented Tablespaces

Brian F. Patella

DSN1COPY and Segmented Tablespaces
Hi!

I am working with PeopleSoft, which delivers several thousand of tables with hundreds of these tables in multiple segmented tablespaces. The delema I have is that if there is a table mod performed in the tablespace, the tablespace is no longer recoverable with the previous Image Copy. I was planning to use DSN1COPY for the recovery on those tablespaces until I read the following in section 4.6.6 Recovery of Dropped Objects in the DB2 V5 Administration Guide. Subtopic Limitations of the Procedures reads as follows: "These procedures do not reclaim a dropped table in a segmented table space. Because of the way space is reused for segmented table spaces, there is no simple, predictable procedure to recover these tables."

Can anyone elaborate on this issue?




andrea milligan

Re: DSN1COPY and Segmented Tablespaces
(in response to Brian F. Patella)
I, too, work with PeopleSoft. We developed scripts that use DSNTIAUL to
unload all tables that have data in the tablespace, then do the mod. This
provides two things. The ability to recover the data as it was since it is
in true flat record format and I have load control statements that I can use
if I have to un-mod the table.

Andrea Milligan
DataBase Administrator
BlueCross BlueShield of Tennessee

> -----Original Message-----
> From: Brian F. Patella [SMTP:[login to unmask email]
> Sent: Thursday, December 02, 1999 5:34 PM
> To: [login to unmask email]
> Subject: DSN1COPY and Segmented Tablespaces
>
> Hi!
>
> I am working with PeopleSoft, which delivers several thousand of tables
> with hundreds of these tables in multiple segmented tablespaces. The
> delema I have is that if there is a table mod performed in the tablespace,
> the tablespace is no longer recoverable with the previous Image Copy. I
> was planning to use DSN1COPY for the recovery on those tablespaces until I
> read the following in section 4.6.6 Recovery of Dropped Objects in the DB2
> V5 Administration Guide. Subtopic Limitations of the Procedures reads as
> follows: "These procedures do not reclaim a dropped table in a segmented
> table space. Because of the way space is reused for segmented table
> spaces, there is no simple, predictable procedure to recover these
> tables."
>
> Can anyone elaborate on this issue?
>
>
>
>

James Jones

Re: DSN1COPY and Segmented Tablespaces
(in response to andrea milligan)
We use DSNTIAUL to unload PeopleSoft as well. However, I have used
DSN1COPY to restore a segmented tablespace with multiple tables (yes, a
PeopleSoft TS before I started building unload scripts). As long as you
have the old object id's to do the translation it should work.

Good Luck,

"Milligan, Andrea" wrote:

>
>
> I, too, work with PeopleSoft. We developed scripts that use DSNTIAUL
> to unload all tables that have data in the tablespace, then do the
> mod. This provides two things. The ability to recover the data as it
> was since it is in true flat record format and I have load control
> statements that I can use if I have to un-mod the table.
>
> Andrea Milligan
> DataBase Administrator
> BlueCross BlueShield of Tennessee
>
> -----Original Message-----
> From: Brian F. Patella [SMTP:[login to unmask email]
> Sent: Thursday, December 02, 1999 5:34 PM
> To: [login to unmask email]
> Subject: DSN1COPY and Segmented Tablespaces
>
> Hi!
>
> I am working with PeopleSoft, which delivers several thousand of
> tables with hundreds of these tables in multiple segmented
> tablespaces. The delema I have is that if there is a table mod
> performed in the tablespace, the tablespace is no longer
> recoverable with the previous Image Copy. I was planning to use
> DSN1COPY for the recovery on those tablespaces until I read the
> following in section 4.6.6 Recovery of Dropped Objects in the DB2
> V5 Administration Guide. Subtopic Limitations of the Procedures
> reads as follows: "These procedures do not reclaim a dropped
> table in a segmented table space. Because of the way space is
> reused for segmented table spaces, there is no simple,
> predictable procedure to recover these tables."
>
>
> Can anyone elaborate on this issue?
>
>
>
>
>
Attachments

  • jim.jones.vcf (<1k)

Michael Murley

Re: DSN1COPY and Segmented Tablespaces
(in response to James Jones)
One thing I have always enjoyed about working with DB2 is that the product
documentation is generally quite clear compared with that of many other
products. But the section on 'Recovery of Dropped Objects' (DB2 V5 Admin
Guide p. 4-147) that Brian Patella cites below leaves me a little
bewildered.

This section seems to describe a procedure to recover a dropped table from a
previous image copy, though it never says that directly. Instead, it refers
in item 5 to a (theretofore unmentioned) work data set from which the user
is instructed to DSN1COPY the data with OBIDXLAT. I guess that means an
image copy, but it would be nice if the doc said so.

I don't understand the statement that this procedure will not work for
segmented table spaces. Yes, I do realize that segment entries are freed for
re-use when a table is dropped, but if you're copying the data from a
previous image copy, they will still be allocated to the table of interest.
DSN1COPY OBIDXLAT translates the OBIDs in the segment entries as well as the
data pages, so what's the problem?

There are references to segment entries in the DBD that may be inaccurate
after the DSN1COPY, but that could be true any time you use DSN1COPY to
restore or migrate a segmented table space, even if a table has not been
dropped. I have found that DB2 is generally tolerant of those discrepancies,
though, and adjusts the DBD values appropriately.

There should also probably be a warning that when this procedure is used,
updates to the table space since the image copy are lost.

The BMC R+/RESOURCE MAXIMIZER User Guide documents a procedure to recover
all data from a dropped table in a segmented table space while leaving other
tables in the space intact. It involves creating a shadow image of the table
space and using SQL to migrate data from the dropped table back to the real
table space. If anyone is interested in that procedure, please email me
directly.

If I have misunderstood the procedure docmented in the Admin Guide, I hope
someone will correct me.

Michael Murley
BMC Software


-----Original Message-----
From: Brian F. Patella [mailto:[login to unmask email]
Sent: Thursday, December 02, 1999 4:34 PM
To: [login to unmask email]
Subject: DSN1COPY and Segmented Tablespaces


Hi!

I am working with PeopleSoft, which delivers several thousand of tables with
hundreds of these tables in multiple segmented tablespaces. The delema I
have is that if there is a table mod performed in the tablespace, the
tablespace is no longer recoverable with the previous Image Copy. I was
planning to use DSN1COPY for the recovery on those tablespaces until I read
the following in section 4.6.6 Recovery of Dropped Objects in the DB2 V5
Administration Guide. Subtopic Limitations of the Procedures reads as
follows: "These procedures do not reclaim a dropped table in a segmented
table space. Because of the way space is reused for segmented table spaces,
there is no simple, predictable procedure to recover these tables."

Can anyone elaborate on this issue?

Michael Murley
BMC Software, Inc.
Backup and Recovery Products for DB2*

*DB2 is a registered trademark of International Business Machines Corp.
Michael Murley
BMC Software, Inc.
Backup and Recovery Products for DB2*

*DB2 is a registered trademark of International Business Machines Corp.