Problem:
In a specific case, the CA-IDMS SQL Optimizer is not using the CALC location mode when it logically has enough information to do so.
For example, assume a record or table with a concatenated CALC key consisting of fields C1 and C2.
A query with the following WHERE clause has enough information to retrieve the required records with three CALC accesses:-
WHERE C1=1 AND C2 IN (1, 2, 3)
However, such a query does not use the CALC location mode and instead resorts to an area sweep or any other indexes or indirect access methods that may exist for that record/table.
Cause:
The CA-IDMS SQL Option does in many cases try to use boolean algebra to transform factors to a more optimal form.
However in this case, the original WHERE clause is too oblique to allow the optimizer to recognize that the full value of three keys have been supplied.
Resolution:
Re-code the WHERE clause like this:
WHERE (C1=1 AND C2=1) OR (C1=1 AND C2=2) OR (C1=1 AND C2=3)
In that case, the optimizer has enough information to realize that it has the complete value of three CALC keys and will access the records using those keys.
Additional Information:
For more information, see the following CA IDMS DocOps page:
WHERE Clause - CA IDMS Reference - 19.0 - CA Technologies Documentation