This example provides an example of the output for the following statement. (The index is on the Block File System.)
SHOW IN XML JOIN INDEX ord_cust_idx;
The XML document generated by SHOW IN XML JOIN INDEX does not contain the parsed query for the join index, only the column definitions of the index and referenced database objects. Therefore, this XML document cannot be used to recreate the join index. However, this XML document can be used to create a table having the same column definition of the original join index. The statement returns the following report:
<?xml version="1.0" encoding="UTF-8" standalone="no" ?><TeradataDBObjectSet version="1.0" xmlns="http://schemas.teradata.com/dbobject" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.teradata.com/dbobject http://schemas.teradata.com/dbobject/DBObject.xsd"><JoinIndex checkSumLevel="Default" colocateName="YourDB_ord_cust_idx" dbName="YourDB" fallback="false" map="SmallTableMap" map_kind="sparse" name="ord_cust_idx" objId="0:3652" objVer="1"> <RepeatComlumnList><Column format="X(1)" name="o_status" nullable="true" order="1"><DataType><Char casespecific="true" charset="LATIN" length="1" uppercase="false" varying="false"/></DataType></Column> <Column format="yyyy-mm-dd" name="o_date" nullable="true" order="2"><DataType><Date/></DataType></Column> <Column format="X(79)" name="o_comment" nullable="true" order="3"><DataType><Char casespecific="true" charset="LATIN" length="79" uppercase="false" varying="true"/></DataType></Column></RepeatComlumnList> <FixedColumn List><Column format="-(10)9" name="o_custkey" nullable="true" order="1"><DataType><Integer/></DataType></Column><Column format="X(26)" name="c_name" nullable="false" order="2"><DataType><Char casespecific="true" charset="LATIN" length="26" uppercase="false" varying="false"/></DataType></Column></FixedColumnList> <RefList><Ref dbName="YourDB" name="orders" type="Table"/><Ref dbName="YourDB" name="customer" type="Table"/></RefList> <SQLText><![CDATA[CREATE JOIN INDEX YourDB.ord_cust_idx ,NO FALLBACK ,CHECKSUM = DEFAULT, MAP = SmallTableMap COLOCATE USING YourDB_ord_cust_idx AS SELECT (YourDB.orders.o_custkey ,YourDB.customer.c_name ) ,(YourDB.orders.o_status , YourDB.orders.o_date ,YourDB.orders.o_comment ) FROM YourDB.orders ,YourDB.customer WHERE YourDB.orders.o_custkey = YourDB.customer.c_custkey PRIMARY INDEX ( o_custkey ) INDEX ( o_custkey ,c_name ) INDEX ( o_status ,o_date ,o_comment );]]></SQLText></JoinIndex><Environment><Server dbRelease="16.10" dbVersion="16.10" hostName="localhost"/><User userId="00000704" userName="YourDB"/><Session charset="UTF8" dateTime="2017-03-24T16:54:56"/></Environment></TeradataDBObjectSet>