Recovering a dropped table Help!

Pierre Dagenais

Recovering a dropped table Help!
Hi

I have dropped a table without unloading the rows.
I have A full copy of the tablespace.
I have tried the procedure of the db2 admin guide v6. on recovering
a drop table. It fails. when I attempt to read the table a get this
message.
DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
REASON 00C90216
ERQUAL 5008
TYPE 00000302
NAME DSNDB04 .SZ2B003 .X'00000016'
CONNECTION-ID=UTILITY
CORRELATION-ID=PPD8CPLT
LUW-ID=*
DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
REASON 00C90216
ERQUAL 5008
TYPE 00000302
NAME DSNDB04 .SZ2B003 .X'00000001'
CONNECTION-ID=UTILITY
CORRELATION-ID=PPD8CPLT
LUW-ID=*


According to the book , the procedure should not be used for segmented
tablespaces.
Of course my tablespace is segmented. So now what do i do?.

I am stuck and I need your help.

Pierre



[login to unmask email]

Re: Recovering a dropped table Help!
(in response to Pierre Dagenais)
00C90216
Explanation: The data manager has detected an inconsistent data
condition. Either the length of a record in a table space is longer than
the maximum-defined record length as defined in the DB2 directory or a
variable-length column has a longer length than the length defined in the
DB2 directory.
This abend reason code is issued by the following CSECTs:
+------------------------------------------------------------------------+
¦ DSNIOSET ¦ DSNIRNXT ¦ DSNIRSET ¦ DSNISFWL ¦
¦ DSNISFX ¦ DSNISRID ¦ ¦ ¦
+------------------------------------------------------------------------+
System Action: A record is written to SYS1.LOGREC, and an SVC dump is
requested.
--- Type HELP on the command line to access MVS/QuickRef help information.
--]
User Response: Notify the system programmer.
System Programmer Response: If you suspect an error in DB2, refer to
Section 3 of Diagnosis Guide and Reference for information on identifying
and reporting the problem.
Problem Determination: Message DSNI013I is issued to identify the data
page that contains the record that is longer than allowed by its
corresponding record definition. This could occur if the DB2 directory was
recovered to a previous point, allowing new object creations (with a
shorter record length) to be created having record identifiers identical
to objects previously created in the table space. Also the table space may
have been improperly restored by using DSN1COPY or the wrong data set may
have been copied for the given table space.
Refer also to Table 4 in Appendix B, "Problem determination" in
topic APPENDIX1.2 for further information on DSN1COPY misuse.

+---------------+---------------------------------------+----------------¦
¦ 00000240 ¦ Database procedure ¦ DBP ¦
+---------------+---------------------------------------+----------------¦
¦ 00000300 ¦ Page ¦ DB.SP.PG ¦
+---------------+---------------------------------------+----------------¦
¦ 00000301 ¦ Index minipage ¦ DB.SP.PG.MP ¦
+---------------+---------------------------------------+----------------¦
¦ 00000302 ¦ Table space page ¦ DB.SP.PG ¦
+---------------+---------------------------------------+----------------¦
¦ 00000303 ¦ Index space page ¦ DB.SP.PG ¦
+---------------+---------------------------------------+----------------¦
¦ 00000304 ¦ Table space RID ¦ DB.SP.RID ¦
+---------------+---------------------------------------+----------------¦
¦ 00000305 ¦ Index access/table space RID ¦ DB.SP.RID ¦
+---------------+---------------------------------------+----------------¦
¦ 00000306 ¦ Index access/table space page ¦ DB.SP.PG ¦
+---------------+---------------------------------------+----------------¦
¦ 00000307 ¦ Index space EOF ¦ DB.SP.01 ¦

John C. Lendman
DBA
[login to unmask email]
(561) 694-5085
Beeper FPL 7413



"Pierre
Dagenais" To: [login to unmask email]
<[login to unmask email] cc:
TAWA.CA> Subject: Recovering a dropped table Help!
Sent by: "DB2
Data Base
Discussion
List"
<[login to unmask email]
OM>


12/17/01
08:41 AM
Please
respond to
"DB2 Data
Base
Discussion
List"






Hi

I have dropped a table without unloading the rows.
I have A full copy of the tablespace.
I have tried the procedure of the db2 admin guide v6. on recovering
a drop table. It fails. when I attempt to read the table a get this
message.
DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
REASON 00C90216
ERQUAL 5008
TYPE 00000302
NAME DSNDB04 .SZ2B003 .X'00000016'
CONNECTION-ID=UTILITY
CORRELATION-ID=PPD8CPLT
LUW-ID=*
DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
REASON 00C90216
ERQUAL 5008
TYPE 00000302
NAME DSNDB04 .SZ2B003 .X'00000001'
CONNECTION-ID=UTILITY
CORRELATION-ID=PPD8CPLT
LUW-ID=*


According to the book , the procedure should not be used for segmented
tablespaces.
Of course my tablespace is segmented. So now what do i do?.

I am stuck and I need your help.

Pierre


visit







Hanne Lyssand

Re: Recovering a dropped table Help!
(in response to John_Lendman@FPL.COM)
Hi!

It's a little difficult to help you when you havn't explain what you
already have done.
Some questions that may help you.

Is the tablespace an table definitions the same as they where when the
imagecopy was taken? The need to be. If not you can create a "temporary"
tabelspace and table whit those definitons and use that for the recovery
and then do an unload/reload.

Have you made an rebuild index after the DSN1COPY? Since DSN1COPY is a
stand alone utilitie just on the dataset it doesn't set the indexses right.

Best regards
Hanne Lyssand



**********************************************************************
For Your service, we have checked this message by the virus-programs
used by the VPS Virusadm.
http://www.vps.no
**********************************************************************



[login to unmask email]

Re: Recovering a dropped table Help!
(in response to Hanne Lyssand)
Hi,

Take the image copy and use DSN1COPY into a TEST table. Then UNLOAD the
data and LOAD back into your newly built empty PROD table.

When done, I'd recommend you do an ALTER TABLE ADD DROP RESTRICT on every
production table

R/..Ed




Pierre Dagenais <[login to unmask email]>@RYCI.COM> on 12/17/2001 08:41:28 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:

Subject: Recovering a dropped table Help!


Hi

I have dropped a table without unloading the rows.
I have A full copy of the tablespace.
I have tried the procedure of the db2 admin guide v6. on recovering
a drop table. It fails. when I attempt to read the table a get this
message.
DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
REASON 00C90216
ERQUAL 5008
TYPE 00000302
NAME DSNDB04 .SZ2B003 .X'00000016'
CONNECTION-ID=UTILITY
CORRELATION-ID=PPD8CPLT
LUW-ID=*
DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
REASON 00C90216
ERQUAL 5008
TYPE 00000302
NAME DSNDB04 .SZ2B003 .X'00000001'
CONNECTION-ID=UTILITY
CORRELATION-ID=PPD8CPLT
LUW-ID=*


According to the book , the procedure should not be used for segmented
tablespaces.
Of course my tablespace is segmented. So now what do i do?.

I am stuck and I need your help.

Pierre








Bob Jeandron

Re: Recovering a dropped table Help!
(in response to Glenn Mackey)
I can think of these things to try at this early Monday moment...
- If available- Use a utility, such as High Performance Unload, UNLOAD in V7
or other vendor products, that can unload data from an Image Copy.
- Call IBM and ask if you can recover the tablespace including segmented
tables, if not what can I do now!
- Recover the entire subsystem

No warrantee, either implied or explicit, included with this opinion.
>>> DB2 Data Base Discussion List 12/17/01 07:41AM >>>
Hi

I have dropped a table without unloading the rows.
I have A full copy of the tablespace.
I have tried the procedure of the db2 admin guide v6. on recovering
a drop table. It fails. when I attempt to read the table a get this
message.
DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
REASON 00C90216
ERQUAL 5008
TYPE 00000302
NAME DSNDB04 .SZ2B003 .X'00000016'
CONNECTION-ID=UTILITY
CORRELATION-ID=PPD8CPLT
LUW-ID=*
DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
REASON 00C90216
ERQUAL 5008
TYPE 00000302
NAME DSNDB04 .SZ2B003 .X'00000001'
CONNECTION-ID=UTILITY
CORRELATION-ID=PPD8CPLT
LUW-ID=*


According to the book , the procedure should not be used for segmented
tablespaces.
Of course my tablespace is segmented. So now what do i do?.

I am stuck and I need your help.

Pierre








Thomas Weber

AW: Recovering a dropped table Help!
(in response to Ed_Vetock@NAVYFEDERAL.ORG)
Hi Pierre,

try the following:

- Analyze your Full Copy with DSN1PRNT to get the desired information (DBID,
PSID, OBIDs)
- Create a tablespace with the same properties.
- Create the tables in the new TS with the same properties (like in the old TS)
- Load the Full Copy into the new TS with the appropriate XLAT parameters.

It should work, I tried it some time ago.

HTH

Thomas

> -----Ursprüngliche Nachricht-----
> Von: Pierre Dagenais [mailto:[login to unmask email]
> Gesendet am: Montag, 17. Dezember 2001 14:41
> An: [login to unmask email]
> Betreff: Recovering a dropped table Help!
>
> Hi
>
> I have dropped a table without unloading the rows.
> I have A full copy of the tablespace.
> I have tried the procedure of the db2 admin guide v6. on recovering
> a drop table. It fails. when I attempt to read the table a get this
> message.
> DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
> REASON 00C90216
> ERQUAL 5008
> TYPE 00000302
> NAME DSNDB04 .SZ2B003 .X'00000016'
> CONNECTION-ID=UTILITY
> CORRELATION-ID=PPD8CPLT
> LUW-ID=*
> DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
> REASON 00C90216
> ERQUAL 5008
> TYPE 00000302
> NAME DSNDB04 .SZ2B003 .X'00000001'
> CONNECTION-ID=UTILITY
> CORRELATION-ID=PPD8CPLT
> LUW-ID=*
>
>
> According to the book , the procedure should not be used for segmented
> tablespaces.
> Of course my tablespace is segmented. So now what do i do?.
>
> I am stuck and I need your help.
>
> Pierre
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]



Glenn Mackey

Re: Recovering a dropped table Help!
(in response to Thomas Weber)
Hi,

The book says you cannot reclaim a table dropped in a segmented tablespace
...

Could you ...

1. Try creating another tablespace/table and then follow the recover
tablespace procedures with appropriate obid etc. translations. The segsize
etc have to be the same as original. If you added columns via an alter, you
must do the same here as well - I assume you are familiar with these "side
issues".


Also, this might be a time to request that IBM made this recovery possible.
For all of DB2's smarts, it seems to fail here, imho. Just when a table has
been dropped, and you really, really, need to recover - you cannot.

One of a DBMS main tasks is to provide recoverability. I know all the
reasons why DB2 cannot do this, but that is more a DB2 design problem. I
hate to tell my boss that we have the smartest, bestest, fastest, and a very
expensive-est database on the planet, but it cannot recover a dropped table.

Glenn



-----Original Message-----
From: Pierre Dagenais [mailto:[login to unmask email]
Sent: Monday, December 17, 2001 7:41 AM
To: [login to unmask email]
Subject: Recovering a dropped table Help!


Hi

I have dropped a table without unloading the rows.
I have A full copy of the tablespace.
I have tried the procedure of the db2 admin guide v6. on recovering
a drop table. It fails. when I attempt to read the table a get this
message.
DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
REASON 00C90216
ERQUAL 5008
TYPE 00000302
NAME DSNDB04 .SZ2B003 .X'00000016'
CONNECTION-ID=UTILITY
CORRELATION-ID=PPD8CPLT
LUW-ID=*
DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
REASON 00C90216
ERQUAL 5008
TYPE 00000302
NAME DSNDB04 .SZ2B003 .X'00000001'
CONNECTION-ID=UTILITY
CORRELATION-ID=PPD8CPLT
LUW-ID=*


According to the book , the procedure should not be used for segmented
tablespaces.
Of course my tablespace is segmented. So now what do i do?.

I am stuck and I need your help.

Pierre








Troy Coleman

Re: Recovering a dropped table Help!
(in response to Bob Jeandron)
Hello,
Did you run the DDL to recreate the table?
The steps I would try are:
1) Create table in the same tablespace.
2) DSN1COPY the imagecopy to the target tablespace.
3) Rebuild all indexes for all tables in tablespace.
4) Select without a where clause against the table.
5) Select with a where clause on the primary index column. This
will verify that you did step 3 - Build index.
6) Imagecopy of recovered tablespace.

Good Luck

Troy Coleman
Coleman Consulting, Inc.
>>>> DB2 Data Base Discussion List 12/17/01 07:41AM >>>
>Hi
>
>I have dropped a table without unloading the rows.
>I have A full copy of the tablespace.
>I have tried the procedure of the db2 admin guide v6. on recovering
>a drop table. It fails. when I attempt to read the table a get
this
>message.
>DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
> REASON 00C90216
> ERQUAL 5008
> TYPE 00000302
> NAME DSNDB04 .SZ2B003 .X'00000016'
> CONNECTION-ID=UTILITY
> CORRELATION-ID=PPD8CPLT
> LUW-ID=*
>DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
> REASON 00C90216
> ERQUAL 5008
> TYPE 00000302
> NAME DSNDB04 .SZ2B003 .X'00000001'
> CONNECTION-ID=UTILITY
> CORRELATION-ID=PPD8CPLT
> LUW-ID=*
>
>
>According to the book , the procedure should not be used for
segmented
>tablespaces.
>Of course my tablespace is segmented. So now what do i do?.
>
>I am stuck and I need your help.
>
>Pierre
>
>
>
visit the
>DB2-L webpage at http://www.ryci.com/db2-l. The owners of the
list can be
>
>
>
>
visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners
of the list can
>



[login to unmask email]

Re: Recovering a dropped table Help!
(in response to Troy Coleman)
One other thing. Was the image copy you are working from done before a
reorg on the TS and after a table change (ie new columns or column
definitions). If yes, then when you recreate the table to reload it, you
have to make the same changes that were done at the time the IC was made.





Troy Coleman <[login to unmask email]>@RYCI.COM> on 2001/12/17
12:04:31 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: Recovering a dropped table Help!


Hello,
Did you run the DDL to recreate the table?
The steps I would try are:
1) Create table in the same tablespace.
2) DSN1COPY the imagecopy to the target tablespace.
3) Rebuild all indexes for all tables in tablespace.
4) Select without a where clause against the table.
5) Select with a where clause on the primary index column. This
will verify that you did step 3 - Build index.
6) Imagecopy of recovered tablespace.

Good Luck

Troy Coleman
Coleman Consulting, Inc.
>>>> DB2 Data Base Discussion List 12/17/01 07:41AM >>>
>Hi
>
>I have dropped a table without unloading the rows.
>I have A full copy of the tablespace.
>I have tried the procedure of the db2 admin guide v6. on recovering
>a drop table. It fails. when I attempt to read the table a get
this
>message.
>DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
> REASON 00C90216
> ERQUAL 5008
> TYPE 00000302
> NAME DSNDB04 .SZ2B003 .X'00000016'
> CONNECTION-ID=UTILITY
> CORRELATION-ID=PPD8CPLT
> LUW-ID=*
>DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
> REASON 00C90216
> ERQUAL 5008
> TYPE 00000302
> NAME DSNDB04 .SZ2B003 .X'00000001'
> CONNECTION-ID=UTILITY
> CORRELATION-ID=PPD8CPLT
> LUW-ID=*

>According to the book , the procedure should not be used for
segmented
>tablespaces.
>Of course my tablespace is segmented. So now what do i do?.
>
>I am stuck and I need your help.



PAUL CLARK

Re: Recovering a dropped table Help!
(in response to Rohn.Solecki@MTS.MB.CA)
I think that DSN1COPY is your best bet to recover the data. I have
found myself in the exact same situation and the DSN1COPY utility
managed to save the day for us. Once the job is set up properly, the
data actually recovers very quickly.


>>> [login to unmask email] 12/17/01 07:41AM >>>
Hi

I have dropped a table without unloading the rows.
I have A full copy of the tablespace.
I have tried the procedure of the db2 admin guide v6. on recovering
a drop table. It fails. when I attempt to read the table a get this
message.
DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
REASON 00C90216
ERQUAL 5008
TYPE 00000302
NAME DSNDB04 .SZ2B003 .X'00000016'
CONNECTION-ID=UTILITY
CORRELATION-ID=PPD8CPLT
LUW-ID=*
DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
REASON 00C90216
ERQUAL 5008
TYPE 00000302
NAME DSNDB04 .SZ2B003 .X'00000001'
CONNECTION-ID=UTILITY
CORRELATION-ID=PPD8CPLT
LUW-ID=*


According to the book , the procedure should not be used for segmented
tablespaces.
Of course my tablespace is segmented. So now what do i do?.

I am stuck and I need your help.

Pierre



visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the
list can



[login to unmask email]

Re: Recovering a dropped table Help!
(in response to PAUL CLARK)
Slight modification to Troy's list, which will cope with tables where the
variable record length bit is set due to performing a alter table without a
reorg.

1) Create table in the same tablespace, ensure this is the only table in the
tablespace.
2) DSN1COPY the imagecopy to the target tablespace.
3) Rebuild all indexes for all tables in tablespace.
4) Select without a where clause against the table.
5) Select with a where clause on the primary index column. This
will verify that you did step 3 - Build index.
6) If table access okay imagecopy tablespace - all done.

7) Drop table and recreate table with the last column missing.
8) Alter table to add the missing column
9) DSN1COPY the imagecopy to the target tablespace.
10) Rebuild all indexes for all tables in tablespace.
11) Select without a where clause against the table.
12) Select with a where clause on the primary index column. This
will verify that you did step 3 - Build index.
13) If table access okay imagecopy tablespace - all done.

-----Original Message-----
From: Troy Coleman [mailto:[login to unmask email]
Sent: 17 December 2001 18:05
To: [login to unmask email]
Subject: Re: Recovering a dropped table Help!


Hello,
Did you run the DDL to recreate the table?
The steps I would try are:
1) Create table in the same tablespace.
2) DSN1COPY the imagecopy to the target tablespace.
3) Rebuild all indexes for all tables in tablespace.
4) Select without a where clause against the table.
5) Select with a where clause on the primary index column. This
will verify that you did step 3 - Build index.
6) Imagecopy of recovered tablespace.

Good Luck

Troy Coleman
Coleman Consulting, Inc.
>>>> DB2 Data Base Discussion List 12/17/01 07:41AM >>>
>Hi
>
>I have dropped a table without unloading the rows.
>I have A full copy of the tablespace.
>I have tried the procedure of the db2 admin guide v6. on recovering
>a drop table. It fails. when I attempt to read the table a get
this
>message.
>DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
> REASON 00C90216
> ERQUAL 5008
> TYPE 00000302
> NAME DSNDB04 .SZ2B003 .X'00000016'
> CONNECTION-ID=UTILITY
> CORRELATION-ID=PPD8CPLT
> LUW-ID=*
>DSNI013I % DSNIRNXT POTENTIALLY INCONSISTENT DATA
> REASON 00C90216
> ERQUAL 5008
> TYPE 00000302
> NAME DSNDB04 .SZ2B003 .X'00000001'
> CONNECTION-ID=UTILITY
> CORRELATION-ID=PPD8CPLT
> LUW-ID=*
>
>
>According to the book , the procedure should not be used for
segmented
>tablespaces.
>Of course my tablespace is segmented. So now what do i do?.
>
>I am stuck and I need your help.
>
>Pierre
>
>
>
visit the
>DB2-L webpage at http://www.ryci.com/db2-l. The owners of the
list can be
>
>
>
>
visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners
of the list can
>







***************************************************************
The contents of this Email and any files transmitted with it
are confidential and intended solely for the use of the
individual or entity to whom it is addressed. The views stated
herein do not necessarily represent the view of the company.
If you are not the intended recipient of this Email you may not
copy, forward, disclose or otherwise use it or any part of it
in any form whatsoever. If you have received this mail in
error please Email the sender.
***************************************************************

RS Components Ltd.



Siva Arun

Re: AW: Recovering a dropped table Help!
(in response to Jim.Leask@RS-COMPONENTS.COM)
Hi ,

Try using DSNCOPY1 if the other one doesnot work.

siva.