DMT_SHOW and BAD_FILE_OPEN errors

[tandoori]

We've recreated a database on a test system but it has a corrupt table. The underlying file exists but ingres can't find it. I've seen the problem on other posts, but no solution yet that helps. I can't drop the table as it just gives a DMT_SHOW error. This is a 6.4 database, built in preparation for upgrade to 2006. The only place that I can look at the table info is in iirelation, any attempt to use iitables gives the table control block error.

Any ideas?

[51341 , 42ca2120]: Tue Dec 30 07:00:25 2008 E_RD0060_DMT_SHOW Cannot access table information due to a non-recoverable DMT_SHOW error
[51341 , 42ca2120]: Tue Dec 30 07:00:25 2008 E_PS0904_BAD_RDF_GETDESC RDF error occurred when getting description for an object.
[51341 , 42ca2120]: Tue Dec 30 07:00:25 2008 E_PS0007_INT_OTHER_FAC_ERR PSF detected an internal error when calling other facility.
[51341 , 42ca2120]: Tue Dec 30 07:00:25 2008 E_SC0215_PSF_ERROR Error returned by PSF.
[51341 , 42ca2120]: Tue Dec 30 07:00:26 2008 E_CL0611_DI_FILENOTFOUND The file specified could not be found open() failed with operating system error 2 (No such file or directory)
[51341 , 42ca2120]: Tue Dec 30 07:00:26 2008 E_DM9004_BAD_FILE_OPEN Disk file open error on database:db01a table:emp_name pathname:/ingdata1/ingres/data/default/db01a filename:aaaaaapg.t01
[51341 , 42ca2120]: Tue Dec 30 07:00:26 2008 E_DM9291_DM2F_FILENOTFOUND When attempting to open a file, the file did not exist.
[51341 , 42ca2120]: Tue Dec 30 07:00:26 2008 E_DM9C5B_DM2T_OPEN_TABIO Error occurred opening a Table Control I/O Block.
[51341 , 42ca2120]: Tue Dec 30 07:00:26 2008 E_DM9C8B_DM2T_TBL_INFO An error occurred while attempting to build the Table Control Block for table (246,0) in database db01a.

[kschendel]

The error message seems to indicate pretty clearly that the file isn't there, are you sure that it exists? Make sure that it's owned by user ingres.

If you just want to drop the table, there are a couple ways. Probably the best is to use verifydb:
verifydb -mrun -sdbname db01a -odrop tablename
(put in the table name on the command line)

If for some reason that doesn't work, you can make the iirelation entry disappear by hand, but I don't recommend that except as a last resort:
sql -u'$ingres' +U
delete from iirelation where reltid=246 and reltidx=0;
delete from iiattribute where attrelid=246 and attrelidx=0;
delete from iiprotect where protabbase=246 and protabidx=0;

which may leave orphan rows in other catalogs (e.g. iiindex) but will probably get you far enough to upgrade. Or you can run verifydb with the -mreport -sdbname db01a -odbms options to get a report of other catalogs that might be left with orphan rows.

After manually deleting rows from the core catalogs, it's safest to stop/restart Ingres to assure that all cached information is removed. (you can achieve the same effect with a bunch of tracepoints, but stop/restart is usually simplest.)

[tandoori]

Thanks. I have found the problem. The file reported as missing (aaaaaapg.t01 in ingdata1) is there, but the other file, aaaaaapg.t00 in ingdata is missing. I have restored it and it's now readable. The error message got confused because the table has two underlying files, and reported the wrong one.

Unfortunately this problem caused verifydb to file with DMT_SHOW errors, I had already tried and failed to drop the table that way, it needs underlying files even if they are empty. I'm now running a full verify to see if everything is OK.