How to drop a join index 1

[Lysen]

I have multiple join indexes on a temporary table. I want to get rid of the table, but I can't because of the JI.

With this query:
select IndexNumber
, IndexType
, UniqueFlag as Flag
, ColumnName as ColName
, ColumnPosition as Pos
, Indexname as IdxName
from dbc.indices
where databasename = 'plato'
and tablename = 'content_final'
and indextype <> 'p'
order by indexnumber

...this is returned:

IndexNumber IndexType Flag ColName Pos IdxName
36 J N box1 2 ji_box1
40 J N box3 2 ji_box3
44 J N box4 2 ji_box4
48 J N box5 2 ji_box5
52 J N box6 2 ji_box6
56 J N box7 2 ji_box7
60 J N box8 2 ji_box8
64 J N box9 2 ji_box9

I checked the NCR manuals on how to drop a JI, but when I try

Drop join index plato.ji_box1;

this is the result:
3807: Table/view/trigger/procedure 'plato.ji_box1' does not exist.

I couldn't find anything else in the manual (I checked the Data dictionary, SQL ref prt 4 and the DB admin manuals), so I think i'm missing something...

How can I get rid of the table?

Thanks...

 

[rrrkrishnan]

You get this error msg because the index is not created in the database 'PLATO'. To get the database in which the index is created, try this.

select databasename, tablename from dbc.tables where tablename in ('ji_box1','ji_box2','ji_box3','ji_box4','ji_box5','ji_box6','ji_box7','ji_box8','ji_box9')
and tablekind ='I'.

Then use that databasename to drop the join index.

 

[Lysen]

Thank you rrrkrishnan!
It was indeed located in a different database...

But now it's gone! Finally!