If you dropped a table and you took imagecopy BEFORE dropping it, then you can recover the data to a newly recreated table if you know the DBID, PSID, OBID of the dropped table. To find these values, use DSN1COPY with the image copy dataset and use FULLCOPY parm and specify 0001 for the source DBID, PSID & OBID. When you run it first, it will give the correct DBID. Replace 0001 with this DBID, you'll get the PSID. Replace 0001 with this PSID and you'll get the OBID.
For an existing table, to obtain the DBID and PSID, run the following query:
SELECT DBNAME, DBID, NAME, PSID
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME = "database-name"
AND NAME = "table_space-name"
To obtain the table OBID, run this query:
SELECT NAME, OBID
FROM SYSIBM.SYSTABLES
WHERE TSNAME = "table_space-name"
AND CREATOR = "creator-name"
3 comments:
Anjil,
If you dropped the table dosen't the information in the catalog tables also get updated to show it no longer exist? sysibm.syscopy is purged, and related information is no longer available. So, how can the DBID, PSID and OBID be obtained from systablespace and systables? I wouldn't be surprised if I am wrong about this...
Thanks
Hi,
First of all, thanks for your comment.
"If you dropped the table dosen't the information in the catalog tables also get updated to show it no longer exist? sysibm.syscopy is purged, and related information is no longer available." It's a good question. If you drop a table/tablespace, the information related to the table/tablespace the catalog is updated to show this info, that's true. But it doesn't delete image copy info and it doesn't delete the image copy datasets.
This is from DB2 V8 SQL Reference guide (please note that it doesn't mention image copy):
"Whenever a table is directly or indirectly dropped, all privileges on the table, all referential constraints in which the table is a parent or dependent, and all synonyms, views, and indexes defined on the table are also dropped. If the table space for the table was implicitly created, it is also dropped.
Whenever a table space is directly or indirectly dropped, all the tables in the table space are also dropped. The name of a dropped table space cannot be reused until a commit operation is performed."
Forgot to answer this question " So, how can the DBID, PSID and OBID be obtained from systablespace and systables?".
In the original post, I've mentioned that you've to run DSN1COPY on the image copy dataset to get these info. Of course, in some shops, image copies are not taken on all tablespaces. In that case, it'll be a problem.
Post a Comment