All Forums Database
tom.gnade 13 posts Joined 03/12
24 Sep 2012
Obtain the text of a join index from the data dictionary?

I created a test AJI at the end of last week, but I've since lost the text of the index. In SQL Assistant, I can see the index as part of the table in the tree view navigation pane, but I can't get to a "Generate SQL > Create" option in the right-click menu. The table DDL doesn't include the join indexes either.
Is there a DBC view, like all_source in Oracle, that will allow me to see the text of the join indexes I have defined?
Thanks in advance.

tom.gnade 13 posts Joined 03/12
24 Sep 2012

Nevermind. I figured it out.
show join index my_database.my_join_index_name;

RS186093 2 posts Joined 07/15
13 Feb 2016

I followed the below steps to get the text of Join Index. In This case, the database where join index resides & the database where the table resides (table that JI was pointing to ) was different:



SELECT  distinct indexname FROM dbc.indices 

WHERE indextype='J'

     AND databasename='<db_name>'

     AND tablename='<tablename>'

ORDER BY indexname,columnposition;




Select * from dbc.tables where tablename = '<index name from STEP 1>';




Show join index <databasename>.<indexname (tablename) from step 2>;


For dropping,

Drop join index  databasename.indexname;

dnoeth 4628 posts Joined 11/04
13 Feb 2016
SELECT * FROM dbc.JoinIndicesV

returns the DatabaseName/TableName for both the JI and it's base tables.



RS186093 2 posts Joined 07/15
14 Feb 2016

Thanks Dieter

You must sign in to leave a comment.