KB10754: How to use the Very Large Database Settings to ensure correct generation of SQL when using indexing and volatile tables against a Teradata data warehouse

A user wishes to enable the creation of volatile tables in the Structured Query Language (SQL) generated by the execution of Strategy reports against a data warehouse held in a certified version of NCR Teradata. They also wish to enable indexing.
 
In order to achieve this, the user modifies the following Very Large Database (VLDB) properties at the Database Instance level:
 
Under Indexing, Intermediate Table Index is left at the default RDBMS-level value:

 
 
 
Under Tables, the Table Space property is modified to include the ON COMMIT PRESERVE ROWS which is necessary for the use of volatile tables:

 
When these properties are saved, the user finds that the generated SQL has the ON COMMIT PRESERVE ROWS before rather than the indexing statement, which is incorrect. The SQL is similar to:
 
create VOLATILE table ZZAM00 , NO LOG (
domain_id INTEGER,
internal_search_string_id INTEGER,
internal_search_engine_id INTEGER,
WJXBFS1 FLOAT)
ON COMMIT PRESERVE ROWS
primary index (domain_id, internal_search_string_id, internal_search_engine_id)
If the Intermediate Table Index property is modified to "Don't create an index," the SQL generation is correct but performance is affected by the absence of indexing. The desired SQL would be:
 
create VOLATILE table ZZAM00 , NO LOG (
domain_id INTEGER,
internal_search_string_id INTEGER,
internal_search_engine_id INTEGER,
WJXBFS1 FLOAT)
primary index (domain_id, internal_search_string_id, internal_search_engine_id)
ON COMMIT PRESERVE ROWS
In fact, the correct combination of VLDB properties to achieve this is as follows:
 

• Undo the modifications described above.
• Under Tables, set Temporary Table Type to True Temporary Table:
  

• Under Tables, ensure that Table Qualifier is blank, as shown below:
  

This should facilitate the correct generation of SQL.