ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

CA IDMS SQL option not using CALC keys


Article ID: 4836


Updated On:




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.


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.


CA-IDMS any supported release.


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

IDMS Reference - Using - Learning IDMS SQL - The WHERE Clause