When using a CA IDMS SQL statement to access a network database, how can we get the statement to use an optional indexed or chained set?

Document ID : KB000015343
Last Modified Date : 14/02/2018
Show Technical Document Details

CA IDMS SQL statements are processed through the optimizer, which determines the physical path that will be used to satisfy the logical SQL request. Clients may want the optimizer to choose a set that is defined as optional; but the optimizer does not consider optional sets as a viable option.


When using a CA IDMS SQL statement to access a network database, how can a user get the statement to use an optional chained set or an optional index to access the data?

This question can occur in any CA IDMS environment that uses SQL syntax to access a network database.

There are some network database constructs that have no direct parallel in a relational database environment.   Optional sets are one of these.  To make an optional set (indexed or chained) 'visible' to the optimizer, you must do these steps:

 1- Create a network schema that defines the set as MA (Mandatory Automatic).

 2- Create an SQL schema that points to this new network schema.

 3- Reference this SQL schema in your SQL DML statements.   

 4- If the optimizer chooses an optional set as the access route, the statement will access only those record occurrences that are members of the set. This is true regardless of whether the statement is doing retrieval or update.  

Additional Information:

Syntax for set definitions can be found in the "Administering CA IDMS Databases" manual, which can be found here:



Details on using CA IDMS SQL to access a network database can be found here: