Skip to main content

Datometry Documentation

Enable or Disable Index Not Supported Error Messages

On databases that do not support indexes, you can specify if Hyper-Q should return an error when attempting to create or access an index, or ignore the error. For backend databases that support the use of indexes, if set to  true  Hyper-Q allows the use of index commands.

If enabled, Hyper-Q reports errors if it fails to create or locate an index. In the following example a CREATE INDEX statement and a DROP INDEX statement return the following errors when allow_index_commands, is set to true.

CREATE UNIQUE INDEX index_name on table_name;

*** Failure 3706 DTM9031: Creating index not supported by the backend system:  (xtra_sql_utils:2590)
Statement# 1, Info =0 

DROP INDEX index_name on table_name;

 *** Failure 3706 DTM8583: Index 'index_name' does not exist. ('teradata-140000-in-drop_index':156)
Statement# 1, Info =0

You can configure Hyper-Q to ignore these errors and not report them.

The syntax for allow_index_commands is:

allow_index_commands = true | false

where:

Parameter

Description

true

Hyper-Q reports errors when it encounters a database that does not support indexes and the index does not exist.

false

Hyper-Q ignores errors when it encounters a database that does not support indexes and the index does not exist.

Hint Scope

The allow_index_commands hint can be specified at the statement, request, session, and application level. To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.

Example 32. Specify that Hyper-Q Accept Index Commands as a Global Setting

You can specify allow_index_commands as a global setting in the Hyper-Q configuration file (dtm.ini). To enable allow_index_commands, set it to true. The default value is false.

"gateway".allow_index_commands=true

If set to true, Hyper-Q reports errors if it fails to create or locate an index. In the following example a CREATE INDEX statement and a DROP INDEX statement return the following errors when allow_index_commands, set it to true for a database that does not support indexes.

CREATE UNIQUE INDEX index_name on table_name;

*** Failure 3706 DTM9031: Creating index not supported by the backend system:  (xtra_sql_utils:2590)
Statement# 1, Info =0 



 *** Failure 3706 DTM8583: Index 'index_name' does not exist. ('teradata-140000-in-drop_index':156)
Statement# 1, Info =0

To learn more about specifying a hint as a gateway option in the Hyper-Q configuration file, see Gateway Configuration Parameters.



Example 33. Specify that Hyper-Q Accept Index Commands as a Query Hint

You can specify allow_index_commands as a query hint. This example specifies the hint within CREATE TABLE and a DROP INDEX statements, and disables error reporting for missing indexes.

CREATE 
--@dtm_allow_index_commands=false
UNIQUE INDEX index_name on table_name;

DROP 
--@dtm_allow_index_commands=false
INDEX index_name on table_name;


Example 34. Specify that Hyper-Q Accept Index Commands Interactively

You can enable and disable  the allow_index_commands  hint using the dtm set  command on a per session basis. When specifying the configuration using the  dtm set  command, use the options  on | off to enable and disable index commands.

dtm set allow_index_commands on;
CREATE UNIQUE INDEX index_name on table_name;
DROP INDEX index_name on table_name;