An SQL SELECT query operating on a nonSQL defined database refuses to use an index.

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

We have a nonSQL defined database record with an index on it, and an SQL schema defined for the nonSQL schema. When we run a simple SQL SELECT against the record, with the exact symbolic key field of the index in the WHERE clause, the query does an area sweep - it refuses to use the index. Why is this?

Environment

IDMS - all supported Releases

Resolution

The most common cause of this problem is that the nonSQL index is defined as something other than MANDATORY AUTOMATIC. Only MANDATORY AUTOMATIC indexes can be considered for use with SQL because for any other type of index, there is no guarantee that all occurrences of that record type are included in the index.