Unusual Recovery

Michael Bruzdzinski

Unusual Recovery

I would like to discuss options for performing an unusual data recovery
for the PERSUB database.

At this time we have tried several methods that should have worked but
have not.

I would like to explore other options that you may have used in the
past.

SCENARIO:

The PERSUB.W2 tablespace contains table W2.

The tablespace is backed up via tape IMAGECOPY at the tablespace level.

The table was unloaded (albeit incorrectly) by the application group
using DSNTIAUL. Some of the partitions were not fully unloaded.

The tablespace was DROPped and re-created using the LARGE parameter.

The data discrepancy was not discovered until after the LOAD had
occurred for the new tablespaces definition.

SOLUTIONS TRIED:

1.) DSN1COPY using the latest (pre-DROP) IMAGECOPY dataset. This works
but generates errors due to the new TABLSPACE using the LARGE parameter.
Partitions get broken pages.

2.) Drop current TABLESPACE and re-define with a previous (non- LARGE)
DDL and attempted to use DSN1COPY. Does not work due to VSAM PUT error.

3.) UNLOAD of the IMAGECOPY to get the data to re-load the table. It
completes but the row count is not even close for the tables.

Any ideas?


Thanks!

Michael Bruzdzinski
Social Security Administration
DCS/OESAE/DDBS/DBSB
410-966-7185


Michael Bruzdzinski
Social Security Administration
DCS/OESAE/DDBS/DBSB
410-966-7185

there is a fine line between sharing dreams and enabling delusions.




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Marcel Harleman

Re: Unusual Recovery
(in response to Michael Bruzdzinski)
Well, just a shot in the dark.

I assume you're getting the VSAM PUT error because the imagecopy was made
using sharelevel reference?

Maybe you can try this scenario:

1. Create the tablespace (with the partitions) like it was before
2. Run an imagecopy sharelevel change against it
3. Rename the imagecopy to .OLD or something like that and rename the
imagecopy you want to restore to the name of the dataset you created under
2
4. Recover the tablespace

And maybe now you could create a new imagecopy with sharelevel reference
and unload it. Or maybe you can run a DSN1COPY from the recovered
tablespace with OBIDXLAT into the tablespace you wanted to recover in the
first place. Or whatever you come up with.

Of course I'm also assuming the tablespace is in a consitent state after
recovery ...

Don't know if this works, but it might be worth a try ...

Regards,

Marcel.



>

> I would like to discuss options for performing an unusual data recovery
> for the PERSUB database.
>
> At this time we have tried several methods that should have worked but
> have not.
>
> I would like to explore other options that you may have used in the
> past.
>
> SCENARIO:
>
>
> The PERSUB.W2 tablespace contains table W2.
>
>
> The tablespace is backed up via tape IMAGECOPY at the tablespace level.
>
>
> The table was unloaded (albeit incorrectly) by the application group
> using DSNTIAUL. Some of the partitions were not fully unloaded.
>
> The tablespace was DROPped and re-created using the LARGE parameter.
>
>
> The data discrepancy was not discovered until after the LOAD had
> occurred for the new tablespaces definition.
>
> SOLUTIONS TRIED:
>
>
> 1.) DSN1COPY using the latest (pre-DROP) IMAGECOPY dataset. This works
> but generates errors due to the new TABLSPACE using the LARGE parameter.
> Partitions get broken pages.
>
>
> 2.) Drop current TABLESPACE and re-define with a previous (non- LARGE)
> DDL and attempted to use DSN1COPY. Does not work due to VSAM PUT error.
>
>
> 3.) UNLOAD of the IMAGECOPY to get the data to re-load the table. It
> completes but the row count is not even close for the tables.
>
> Any ideas?
>
>
>
> Thanks!
>
>
> Michael Bruzdzinski
> Social Security Administration
> DCS/OESAE/DDBS/DBSB
> 410-966-7185
>
>
>
> Michael Bruzdzinski
> Social Security Administration
> DCS/OESAE/DDBS/DBSB
> 410-966-7185
>
>
> there is a fine line between sharing dreams and enabling delusions.
>
>
>
>
> -------------------------------------------------------------------------
> --------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steen Rasmussen

Re: Unusual Recovery
(in response to Marcel Harleman)


Hello Mike,



I have done something similar in the past.



Is he image copy SHRLEVEL REFERENCE or CHANGE ? this can make a huge
difference.



In order not to touch the new (re-created) object, I created a new set
of objects (100% identical tablespace, table for how the previous
objects were created). Then generate an unload of the latest full image
copy which then can be loaded into the new tablespace (why your unload
doesn't work must be a bug which should be reported).

Now you have the table data as of the image copy, and only need the
log-records between the image copy point and the DROP RBA. I have used
CA Log Analyzer but any log tool should be able to generate SQL REDO
statements from the image copy PIT to the "DROP RBA-1".





Steen Rasmussen
CA
Senior Consultant DB2 tools

Tel : +1-630-505-6673 (US direct)
Tel : +1-815-274-9589 (US mobile)

Tel : +45-22 15 44 98 (Europe mobile)
[login to unmask email]
< http://www.ca.com >

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Bruzdzinski, Michael
Sent: Wednesday, January 10, 2007 1:42 PM
To: [login to unmask email]
Subject: [DB2-L] Unusual Recovery





I would like to discuss options for performing an unusual data recovery
for the PERSUB database.

At this time we have tried several methods that should have worked but
have not.

I would like to explore other options that you may have used in the
past.

SCENARIO:

The PERSUB.W2 tablespace contains table W2.

The tablespace is backed up via tape IMAGECOPY at the tablespace level.

The table was unloaded (albeit incorrectly) by the application group
using DSNTIAUL. Some of the partitions were not fully unloaded.

The tablespace was DROPped and re-created using the LARGE parameter.

The data discrepancy was not discovered until after the LOAD had
occurred for the new tablespaces definition.

SOLUTIONS TRIED:

1.) DSN1COPY using the latest (pre-DROP) IMAGECOPY dataset. This works
but generates errors due to the new TABLSPACE using the LARGE parameter.
Partitions get broken pages.

2.) Drop current TABLESPACE and re-define with a previous (non- LARGE)
DDL and attempted to use DSN1COPY. Does not work due to VSAM PUT error.

3.) UNLOAD of the IMAGECOPY to get the data to re-load the table. It
completes but the row count is not even close for the tables.

Any ideas?

.



------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Ebert

Re: Unusual Recovery
(in response to Steen Rasmussen)
If you use the DSN1COPY method, you do not need to drop the current TS.
Create another one (maybe even in another DB2, e.g. Test) and restore the
data to there using OBIDXLAT.

I've often done this Unload/Drop/Create/Load to increase DSSIZE, the table
column or clustering index definition or the number of partitions. How can
you get the Unload part wrong? By the way, use the UNLOAD utility, it is
much faster and you don't have to look up the partition boundaries. Also,
next time do a COUNT(*) before the Drop and validate against the numbers
reported by the DSNTIAUL/UNLOAD job(s).

Method #2 should work. The PUT error indicates one of two possible
mistakes that I can think of now:
1), you run out of disk space. If DSN1COPY has to extend the VSAM file to
another disk, it will again try to get the primary quantity. So make sure
the initial partition sizes are sufficiently large.
2), DSN1COPY tries to put all the data into the first partition due to a
coding mistake in the DSN1COPY parms or the JCL DD statements.

Can you post the exact full job, the error message and the
(allocated/used) VSAM sizes after the job run attempt?

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




"Bruzdzinski, Michael" <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
[DB2-L] Unusual Recovery





"Bruzdzinski, Michael" <[login to unmask email]>
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
10-01-07 20:41



I would like to discuss options for performing an unusual data recovery
for the PERSUB database.
At this time we have tried several methods that should have worked but
have not.
I would like to explore other options that you may have used in the past.
SCENARIO:
The PERSUB.W2 tablespace contains table W2.
The tablespace is backed up via tape IMAGECOPY at the tablespace level.
The table was unloaded (albeit incorrectly) by the application group using
DSNTIAUL. Some of the partitions were not fully unloaded.
The tablespace was DROPped and re-created using the LARGE parameter.
The data discrepancy was not discovered until after the LOAD had occurred
for the new tablespaces definition.
SOLUTIONS TRIED:
1.) DSN1COPY using the latest (pre-DROP) IMAGECOPY dataset. This works but
generates errors due to the new TABLSPACE using the LARGE parameter.
Partitions get broken pages.
2.) Drop current TABLESPACE and re-define with a previous (non- LARGE) DDL
and attempted to use DSN1COPY. Does not work due to VSAM PUT error.
3.) UNLOAD of the IMAGECOPY to get the data to re-load the table. It
completes but the row count is not even close for the tables.
Any ideas?

Thanks!
Michael Bruzdzinski
Social Security Administration
DCS/OESAE/DDBS/DBSB
410-966-7185
there is a fine line between sharing dreams and enabling delusions.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Richard Humphris

Re: Unusual Recovery
(in response to Michael Ebert)

Just a thought, maybe PCTFREE and FREEPAGE could be causing a problem
with option 2: "Drop current TABLESPACE and re-define with a previous
(non- LARGE) DDL and attempted to use DSN1COPY. Does not work due to
VSAM PUT error."

Try to alter (or define) the tablespace with: PCTFREE 0 FREEPAGE 0
before trying to reload the table with DSN1COPY.

Good luck,
Richard Humphris
Business: 312-822-5193
Text Pager: 877-814-2246
To send short email to pager... send email to:
[login to unmask email]



E-MAIL CONFIDENTIALITY NOTICE: The contents of this e-mail message and any attachments are intended solely for the
addressee(s) and may contain confidential and/or legally privileged information. If you are not the
intended recipient of this message or if this message has been addressed to you in error, please
immediately alert the sender by reply e-mail and then delete this message and any attachments. If you
are not the intended recipient, you are notified that any use, dissemination, distribution, copying, or
storage of this message or any attachment is strictly prohibited.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Bell

Re: Unusual Recovery
(in response to Richard Humphris)
DSN1COPY needs to know both the page size and the dataset type. And it
needs to match the last define cluster that was done by DB2.
If you define the tablespace as large, then the cluster is defined as SMS
large dataset and you have to have the SMS constructs defined for the define
cluster to work. If you use DSN1COPY with that as the target, then you need
to specify the DSN1COPY parameter LARGE and the image copy had to come from
a copy of the large tablespace. You can't use DSN1COPY to convert from
regular to LARGE.

If you want to recover to an image copy from before the large, you need to
drop/create the tablespace as the correct page size and not large to get the
cluster defined correctly and then specify the correct page size to
DSN1COPY.

The easiest way to match the create tablespace to the image copy is run
DSN1PRNT on the first 30-40 pages on the copy. DSN1PRNT will identify the
page types and contents.

One way to get broken pages is to not specify INLCOPY for an image copy that
was created by a reorg or load. You can identify the type of image copy by
the rows in SYSIBM.SYSCOPY if you can get the data back from before the drop
tablespace occured. The only other way to identify the inlcopy is finding
the space map pages out of sequence in the copy.

Mike
HLS Technologies



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Bruzdzinski, Michael
Sent: Wednesday, January 10, 2007 1:42 PM
To: [login to unmask email]
Subject: [DB2-L] Unusual Recovery


I would like to discuss options for performing an unusual data recovery for
the PERSUB database.

At this time we have tried several methods that should have worked but have
not.

I would like to explore other options that you may have used in the past.

SCENARIO:

The PERSUB.W2 tablespace contains table W2.

The tablespace is backed up via tape IMAGECOPY at the tablespace level.

The table was unloaded (albeit incorrectly) by the application group using
DSNTIAUL. Some of the partitions were not fully unloaded.

The tablespace was DROPped and re-created using the LARGE parameter.

The data discrepancy was not discovered until after the LOAD had occurred
for the new tablespaces definition.

SOLUTIONS TRIED:

1.) DSN1COPY using the latest (pre-DROP) IMAGECOPY dataset. This works but
generates errors due to the new TABLSPACE using the LARGE parameter.
Partitions get broken pages.

2.) Drop current TABLESPACE and re-define with a previous (non- LARGE) DDL
and attempted to use DSN1COPY. Does not work due to VSAM PUT error.

3.) UNLOAD of the IMAGECOPY to get the data to re-load the table. It
completes but the row count is not even close for the tables.

Any ideas?


Thanks!

Michael Bruzdzinski
Social Security Administration
DCS/OESAE/DDBS/DBSB
410-966-7185


Michael Bruzdzinski
Social Security Administration
DCS/OESAE/DDBS/DBSB
410-966-7185

there is a fine line between sharing dreams and enabling delusions.



----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Ebert

Re: Unusual Recovery
(in response to Mike Bell)
The Imagecopy info from SYSCOPY is removed when the TS is dropped. If he
could recover the whole subsystem back in time to get the SYSCOPY entry
back, he could do a simple RECOVER TS to get the data back.
LARGE and page size are unrelated. You probably mean DSSIZE. Note that the
specification of LARGE is discouraged (use DSSIZE); also in older
partitioned tablespaces, DSSIZE is related to the number of partitions:
1-16 parts: DSSIZE=4G; 17-32 parts: DSSIZE=2G; 33-64 parts: DSSIZE=1G; >64
parts: DSSIZE=4G (LARGE).

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Mike Bell <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
Re: [DB2-L] Unusual Recovery





Mike Bell <[login to unmask email]>
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
10-01-07 21:34
DSN1COPY needs to know both the page size and the dataset type. And it
needs to match the last define cluster that was done by DB2.
If you define the tablespace as large, then the cluster is defined as SMS
large dataset and you have to have the SMS constructs defined for the
define
cluster to work. If you use DSN1COPY with that as the target, then you
need
to specify the DSN1COPY parameter LARGE and the image copy had to come
from
a copy of the large tablespace. You can't use DSN1COPY to convert from
regular to LARGE.

If you want to recover to an image copy from before the large, you need to
drop/create the tablespace as the correct page size and not large to get
the
cluster defined correctly and then specify the correct page size to
DSN1COPY.

The easiest way to match the create tablespace to the image copy is run
DSN1PRNT on the first 30-40 pages on the copy. DSN1PRNT will identify the
page types and contents.

One way to get broken pages is to not specify INLCOPY for an image copy
that
was created by a reorg or load. You can identify the type of image copy
by
the rows in SYSIBM.SYSCOPY if you can get the data back from before the
drop
tablespace occured. The only other way to identify the inlcopy is finding
the space map pages out of sequence in the copy.

Mike
HLS Technologies

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Ebert

Re: Unusual Recovery
(in response to Michael Ebert)
These parms are only relevant for LOAD and REORG. DSN1COPY just does a
page-by-page-replace and does not change the internal structure at all.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




"Humphris,Richard P." <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
Re: [DB2-L] Unusual Recovery





"Humphris,Richard P." <[login to unmask email]>
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
10-01-07 21:28

Just a thought, maybe PCTFREE and FREEPAGE could be causing a problem with
option 2: "Drop current TABLESPACE and re-define with a previous (non-
LARGE) DDL and attempted to use DSN1COPY. Does not work due to VSAM PUT
error."

Try to alter (or define) the tablespace with: PCTFREE 0 FREEPAGE 0
before trying to reload the table with DSN1COPY.

Good luck,
Richard Humphris
Business: 312-822-5193
Text Pager: 877-814-2246
To send short email to pager... send email to:
[login to unmask email]


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Carol Anne Sutfin

Re: Unusual Recovery
(in response to Michael Ebert)
Michael,

Questions.

1. Did you try using the OBIDXLAT function in DSN1COPY?

2. What version of DB2?

3. Do you have any Recovery utilities other than IBM?

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
[login to unmask email]



"Bruzdzinski,
Michael"
<Michael.Bruzdzin To
[login to unmask email]> [login to unmask email]
Sent by: DB2 Data cc
Base Discussion
List Subject
<[login to unmask email] [DB2-L] Unusual Recovery
ORG>


01/10/2007 01:41
PM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-L.ORG>









I would like to discuss options for performing an unusual data recovery for
the PERSUB database.


At this time we have tried several methods that should have worked but have
not.


I would like to explore other options that you may have used in the past.


SCENARIO:


The PERSUB.W2 tablespace contains table W2.


The tablespace is backed up via tape IMAGECOPY at the tablespace level.


The table was unloaded (albeit incorrectly) by the application group using
DSNTIAUL. Some of the partitions were not fully unloaded.


The tablespace was DROPped and re-created using the LARGE parameter.


The data discrepancy was not discovered until after the LOAD had occurred
for the new tablespaces definition.


SOLUTIONS TRIED:


1.) DSN1COPY using the latest (pre-DROP) IMAGECOPY dataset. This works but
generates errors due to the new TABLSPACE using the LARGE parameter.
Partitions get broken pages.


2.) Drop current TABLESPACE and re-define with a previous (non- LARGE) DDL
and attempted to use DSN1COPY. Does not work due to VSAM PUT error.


3.) UNLOAD of the IMAGECOPY to get the data to re-load the table. It
completes but the row count is not even close for the tables.


Any ideas?





Thanks!


Michael Bruzdzinski
Social Security Administration
DCS/OESAE/DDBS/DBSB
410-966-7185





Michael Bruzdzinski
Social Security Administration
DCS/OESAE/DDBS/DBSB
410-966-7185


there is a fine line between sharing dreams and enabling delusions.



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

harby ariza

Re: Unusual Recovery
(in response to Carol Anne Sutfin)
_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________



Michael,

What sort of VSAM put error are you getting? I'm guessing 168 open
error . If so run a verify to the VSAM file before running the dsn1copy.
I suppose the way to go is re-creating the table/tablespace as it was
originally even under different name then after a successful recovery
unload the data and load it into the new table.

Good luck,

harby.


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Carol Sutfin
Sent: Thursday, 11 January 2007 9:19 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Unusual Recovery

Michael,

Questions.

1. Did you try using the OBIDXLAT function in DSN1COPY?

2. What version of DB2?

3. Do you have any Recovery utilities other than IBM?

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
[login to unmask email]




"Bruzdzinski,

Michael"

<Michael.Bruzdzin
To
[login to unmask email]> [login to unmask email]

Sent by: DB2 Data
cc
Base Discussion

List
Subject
<[login to unmask email] [DB2-L] Unusual Recovery

ORG>





01/10/2007 01:41

PM





Please respond to

DB2 Database

Discussion list

at IDUG

<[login to unmask email]

2-L.ORG>












I would like to discuss options for performing an unusual data recovery
for
the PERSUB database.


At this time we have tried several methods that should have worked but
have
not.


I would like to explore other options that you may have used in the
past.


SCENARIO:


The PERSUB.W2 tablespace contains table W2.


The tablespace is backed up via tape IMAGECOPY at the tablespace level.


The table was unloaded (albeit incorrectly) by the application group
using
DSNTIAUL. Some of the partitions were not fully unloaded.


The tablespace was DROPped and re-created using the LARGE parameter.


The data discrepancy was not discovered until after the LOAD had
occurred
for the new tablespaces definition.


SOLUTIONS TRIED:


1.) DSN1COPY using the latest (pre-DROP) IMAGECOPY dataset. This works
but
generates errors due to the new TABLSPACE using the LARGE parameter.
Partitions get broken pages.


2.) Drop current TABLESPACE and re-define with a previous (non- LARGE)
DDL
and attempted to use DSN1COPY. Does not work due to VSAM PUT error.


3.) UNLOAD of the IMAGECOPY to get the data to re-load the table. It
completes but the row count is not even close for the tables.


Any ideas?





Thanks!


Michael Bruzdzinski
Social Security Administration
DCS/OESAE/DDBS/DBSB
410-966-7185





Michael Bruzdzinski
Social Security Administration
DCS/OESAE/DDBS/DBSB
410-966-7185


there is a fine line between sharing dreams and enabling delusions.



------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at
http://conferences.idug.org/index.cfm

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of any action in reliance
upon this information, by persons or entities other than the intended recipient is
prohibited.

If you have received this in error, please contact the sender and delete this e-mail
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute
the information contained in this e-mail and any attached files, with the permission
of the sender.

This message has been scanned for viruses with Symantec Scan Engine and cleared by
MailMarshal.
_______________________________________________________________________________________

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Max Scarpa

Re: Unusual Recovery
(in response to harby ariza)
If it is partitioned did you use NUMPART (if memory is correct) parameter
in DSN1COPY ?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jeff Todd

Re: Unusual Recovery
(in response to Max Scarpa)
Unusual RecoveryMichael,
I suspect the reason for problem #1 is that different internal page numbers are generated for partitioned table spaces if they are defined as LARGE (as opposed to not being defined as LARGE). So an imagecopy taken of the non-LARGE TS, could not be used to populate a TS defined as LARGE.
For problem #2, what is the VSAM PUT error you are receiving using DSN1COPY? Knowing what the actual error code is would help identify what the DSN1COPY problem is.
Was the pre-unload/pre-LARGE imagecopy done with the DB2 COPY utility? Was the copy a DSNUM ALL copy? It should not matter to DSN1COPY if the COPY utility was run as either SHRLEVEL REFERENCE or SHRLEVEL CHANGE. If you have the syntax of the how the image copy was made, that might also shed some light as to what is now happening.

Jeff
----- Original Message -----
From: Bruzdzinski, Michael
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Wednesday, January 10, 2007 11:41 AM
Subject: [DB2-L] Unusual Recovery




I would like to discuss options for performing an unusual data recovery for the PERSUB database.

At this time we have tried several methods that should have worked but have not.

I would like to explore other options that you may have used in the past.

SCENARIO:

The PERSUB.W2 tablespace contains table W2.

The tablespace is backed up via tape IMAGECOPY at the tablespace level.

The table was unloaded (albeit incorrectly) by the application group using DSNTIAUL. Some of the partitions were not fully unloaded.

The tablespace was DROPped and re-created using the LARGE parameter.

The data discrepancy was not discovered until after the LOAD had occurred for the new tablespaces definition.

SOLUTIONS TRIED:

1.) DSN1COPY using the latest (pre-DROP) IMAGECOPY dataset. This works but generates errors due to the new TABLSPACE using the LARGE parameter. Partitions get broken pages.

2.) Drop current TABLESPACE and re-define with a previous (non- LARGE) DDL and attempted to use DSN1COPY. Does not work due to VSAM PUT error.

3.) UNLOAD of the IMAGECOPY to get the data to re-load the table. It completes but the row count is not even close for the tables.

Any ideas?



Thanks!

Michael Bruzdzinski
Social Security Administration
DCS/OESAE/DDBS/DBSB
410-966-7185



Michael Bruzdzinski
Social Security Administration
DCS/OESAE/DDBS/DBSB
410-966-7185

there is a fine line between sharing dreams and enabling delusions.



--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm