Obtain the text of a join index from the data dictionary?

Database

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.

4 REPLIES

Re: Obtain the text of a join index from the data dictionary?

Nevermind. I figured it out.

show join index my_database.my_join_index_name;

Teradata Employee

Re: Obtain the text of a join index from the data dictionary?

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:

STEP 1:

SELECT  distinct indexname FROM dbc.indices 

WHERE indextype='J'

     AND databasename='<db_name>'

     AND tablename='<tablename>'

ORDER BY indexname,columnposition;

STEP 2:

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

STEP 3:

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

For dropping,

Drop join index  databasename.indexname;

N/A

Re: Obtain the text of a join index from the data dictionary?

SELECT * FROM dbc.JoinIndicesV

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


Teradata Employee

Re: Obtain the text of a join index from the data dictionary?

Thanks Dieter