.CA IDMS SQL statement to use an optional indexed or chained set?

book

Article ID: 15343

calendar_today

Updated On:

Products

CA IDMS CA IDMS - Database CA IDMS - ADS

Issue/Introduction

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

Environment

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

Resolution

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

IDMS 19.0 - Administrating - SET Statement for Schema

 

IDMS 19.0 - Administrating - Accessing Data Using SQL