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?
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.
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: