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:  TEC1650272
Last Modified Date:  08/03/2017
{{active ? 'Hide' : 'Show'}} Technical Document Details

Products

  • CA IDMS

Releases

  • CA IDMS:Release:18.5
  • CA IDMS:Release:19.0

Components

  • CA IDMS SQL:SQLOPT
  • CA IDMS Server:IDSERV
Introduction:

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.

Question:

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

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:

https://docops.ca.com/ca-idms/19/en/administrating/administrating-ca-idms-database/schema-statements/set-statement-for-schema

 

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

https://docops.ca.com/ca-idms-ref/19/en/sql-reference/accessing-network-defined-databases

Please help us improve!

Will this information enable you to resolve your issue?

Please tell us what we can do better.

{{feedbackText.length ? feedbackText.length : '0'}}/255

{{status}}

Not what you were looking for?

Search Again >

Product Information

Support by Product >

Communities

Join a Community >