Extensions for Accessing Network-Defined Records

This article describes the following information:
idmscu19
This article describes the following information:
2
2
Expansion of Extended-Search Condition
Expansion of Extended-search Condition specifies criteria used to select rows from tables. 
Syntax
Expansion of extended-search-condition 
The following example shows the syntax for expansion of extended-search-condition:
  ►►─┬─ 
search-condition
 ───┬─┬──────────────────────────────────────┬──────────►◄    └─ 
set-specification
 ──┘ │ ┌──────────────────────────────────┐ │                             └─▼── AND ──┬─ 
search-condition
 ───┬─┴─┘                                         └─ 
set-specification
 ──┘  
Parameters
The following list are the parameters for expansion of extended-search-condition:
  • search-condition
    Specifies a search condition whose value must be true for the row or rows to be included in the result table.
  • set-specification
    Specifies that only rows participating as owner and member in the named set be included in the result table. For expanded
    set-specification
    syntax, see Expansion of Set-specification Statement.
Usage
Evaluation
The full search condition is satisfied when the value of all its operands are true. It is not satisfied when the value of any of its operands is either false or unknown.
Order of Evaluation
CA IDMS effectively evaluates from left to right after first evaluating each operand individually.
You can use parentheses to override the default order of evaluation. Operands in parentheses are evaluated first.
Expansion of Set-Specification Statement
Expansion of the set-specification statement specifies join criteria for tables representing owner and member records of a network-defined set.
Syntax
Expansion of set-specification
The following example shows the syntax for expansion of set-specification:
  ►►─┬─────────┬─ 
set-name
 ──┬─────────────────────────────────┬────────────────►◄    ├─ 
FIRST
 ─┤             │ ┌─────────────────────────────┐ │    └─ 
LAST
 ──┘             └─▼── . ─┬─ 
table-identifier
 ─┬─┴─┘                                     └─ 
alias
 ────────────┘  
Parameters
The following list are the parameters for expansion of set-specification:
  • set-name
    Specifies the name of the set to be used as the test criteria.
    Set-name must follow the rules for identifiers. If hyphens appear in the name, it must be enclosed in double quotes.
  • table-identifier
    Specifies the name of a table representing either the owner or member of the set.
    Table-identifier
    must appear in the FROM parameter of the containing query specification or SELECT statement.
    At most, two table names or aliases can qualify the set name and if both appear, one must identify the owner and the other must identify a member of the set.
  • alias
    Specifies the alias assigned to the table representing the owner or member of the set.
    If the table has been assigned an alias in the FROM parameter of the query specification or SELECT statement where
    set-specification
    appears, the alias and not the original table name must be used to qualify the set.
  • FIRST
    Specifies only the first member record occurrence from each occurrence of
    set-name
    is returned in the join.
  • LAST
    Specifies only the last member record occurrence from each occurrence of
    set-name
    is returned in the join. For chained sets, this command is only valid when the set linkage includes prior pointers.
    For more information about coding considerations and set linkage, see Set.
Usage
Members without Foreign Keys
Joining rows from different tables specified in a SELECT statement is usually done with comparison operations on column values. The most typical approach for SQL-defined tables is to use equal comparisons of the matching primary/foreign key columns of a referential constraint definition.
However, in a network-defined database, member records may not contain the key values of their owner records. For example, it is not necessary for the EMPLOYEE record to contain the department ID of its associated DEPARTMENT record if the relationship between the EMPLOYEE and DEPARTMENT records is represented by a set.
In such cases, column-based comparison cannot be used to process a join; instead, the SELECT statement must identify the set in the WHERE parameter using
set-specification
.
A system-owned index is not a set joining two records; therefore, it cannot be used in the WHERE clause.
Evaluation
Two table rows satisfy the
set-specification
criteria if one is a member of the other in the named set. The value of the set-specification is considered
true
when this condition is satisfied and
false
otherwise.
The tables representing the owner and member records must appear in the FROM parameter of the containing query specification or SELECT statement.
Inclusion of the
FIRST
and
LAST
keywords renders
set-specification
false for all member occurrences except for the first or last, respectively. This additional syntax is included for use with sets with an inherent first-in-first-out or last-in-first-out organization.
Qualification Requirements
Set-name
must be qualified under the following conditions:
  • More than one member of a multi-member set has been named as a table in the preceding FROM parameter
  • A table representing either the owner or a member has been assigned an alias in the preceding FROM parameter
Improved Efficiency of Join Operations
A network-defined member record can contain the value of its owner's key. However, unless the set definition in the network-defined schema identifies this as a foreign key, CA IDMS will not use the set in its access strategy when performing join operations. This may result in the choice of a less-than-optimal access strategy.
This can be overcome by using
set-specification
as part of the selection criteria.
For information about table procedures, see Defining and Using Table Procedures.