Aha!

Just thought of the solution...run verifydb -mreport -odbms_catalogs.
It then reports:
S_DU16C0_NONEXISTENT_DEP_OBJ The INDEX described in iidbdepends as
deid1 = 315, deid2 = 6527, dtype = 10, qid = 0
does not exist.
S_DU0322_DROP_IIDBDEPENDS_TUPLE The recommended action is to delete all tuples with
inid1 = 315, inid2 = 0, deid1 = 315 and deid2 = 6527
from iidbdepends.

Which sorta looks like what I'm after.

Marty

From: Martin Bowes
Sent: 14 April 2011 11:18
To: 'Ingres and related product discussion forum'
Subject: alter table drop constraint leads to E_US16D5 Table specified no longer exists.

Hi All,

II 10.0.0 (a64.lnx/132)NPTL + p13998

I have a table with many constraints, two of which share an index. One is aforeign key constraint and the other is a uniqueness constraint.

I needed to add an extra column to the unique set.

Trouble is that I forgot to include 'set trace point qe61' and so the firstalter table drop constraint also dropped the supporting index. The subsequent alter table drop constraint the failed with E_US16D5 Table specified nolonger exists. Sadly the job then committed and quit.

Fortunatly it was run on a test version of the live database!

Now I can't heal the problem on the test database.
I can recreate the index and the constraint and seemingly get things back to what they should be, but rerunning the corrected script still fails with E_US16D5. BTW. The corrected script works fine when run fresh on another copy of the live database and on the live database itself.

I'm thinking that somewhere in the bowels of the constraint definition it saved the reltid, reltidx of the original index and this of course won't match the new index. I've just looked through iiconstraint and can't see any obvious magic there.

Anyone got any idea?

Martin Bowes

Yep that did it.

The job to convert the index now runs clean.

Marty

From: Martin Bowes
Sent: 14 April 2011 11:21
To: 'Ingres and related product discussion forum'
Subject: RE: alter table drop constraint leads to E_US16D5 Table specified no longer exists.

Aha!

Just thought of the solution...run verifydb -mreport -odbms_catalogs.
It then reports:
S_DU16C0_NONEXISTENT_DEP_OBJ The INDEX described in iidbdepends as
deid1 = 315, deid2 = 6527, dtype = 10, qid = 0
does not exist.
S_DU0322_DROP_IIDBDEPENDS_TUPLE The recommended action is to delete all tuples with
inid1 = 315, inid2 = 0, deid1 = 315 and deid2 = 6527
from iidbdepends.

Which sorta looks like what I'm after.

Marty

From: Martin Bowes
Sent: 14 April 2011 11:18
To: 'Ingres and related product discussion forum'
Subject: alter table drop constraint leads to E_US16D5 Table specified no longer exists.

Hi All,

II 10.0.0 (a64.lnx/132)NPTL + p13998

I have a table with many constraints, two of which share an index. One is aforeign key constraint and the other is a uniqueness constraint.

I needed to add an extra column to the unique set.

Trouble is that I forgot to include 'set trace point qe61' and so the firstalter table drop constraint also dropped the supporting index. The subsequent alter table drop constraint the failed with E_US16D5 Table specified nolonger exists. Sadly the job then committed and quit.

Fortunatly it was run on a test version of the live database!

Now I can't heal the problem on the test database.
I can recreate the index and the constraint and seemingly get things back to what they should be, but rerunning the corrected script still fails with E_US16D5. BTW. The corrected script works fine when run fresh on another copy of the live database and on the live database itself.

I'm thinking that somewhere in the bowels of the constraint definition it saved the reltid, reltidx of the original index and this of course won't match the new index. I've just looked through iiconstraint and can't see any obvious magic there.

Anyone got any idea?

Martin Bowes