Extended Functions of ASF › Deriving Views from Various Sources › Deriving Views from Non-SQL Defined Records

Deriving Views from Non-SQL Defined Records

Non-SQL defined records are defined by the DBA using schema DDL and the schema compiler.

Extended Derivation Required

You must use the Extended Table Derivation screen to derive views from non-SQL defined records. The derivation portion of the Table Definition screen cannot be used for this type of derivation. The tables from which you can derive must be made available to ASF in the following ways:

Define Records in IDMSR Schema

Their record descriptions must be included in the system-supplied schema, IDMSR, along with the appropriate related structures, such as area and set descriptions (see CA IDMS Database Administration Guide for details).

Note: It is recommended that any non-SQL defined records that are to be used in ASF be added to the schema IDMSR immediately after ASF system initialization (see the chapter "Administrative Functions of ASF") to prevent problems with duplicate record element names.

Include Records in Subschema of IDMSR

They must be included in a subschema generated from the schema IDMSR that is accessible to you through dictionary security.

How To Do It

To derive views from non-SQL defined records:

1. Enter the record name, as defined in the schema, in the Source Name field in the upper portion of the screen. Specify the subschema name in which the record is defined in the Subschema field. Page forward as necessary to identify additional source records; a maximum of six source records can be identified. Each source record is numbered consecutively. The derivation source number is used to specify join information.

   Note: All records used to define a single view must be defined in the same subschema. The subschema name must be specified for each source record identified.

2. Join the records on owner-member set relationships by entering the following join information:
   • Type the set name under Column #1/Set.
   • Type the derivation source number of the set owner under Of Der.
   • Enter the operator ST to indicate a set join.
   • Leave the Column #2 field blank.
   • Type the derivation source number of the set member under Of Der.

The sample Extended Table Derivation screen shown below illustrates the information entered to join the EMPLOYEE and DEPARTMENT tables on the DEPT-EMPLOYEE set.

                                                    CA
  XDER   CA - Automatic System Facility           *Extended Table Derivation*
  DC560802 ADD/MODIFY DERIVATIONS
  Table Name: EMPLOYEES AND DEPARTMENTS
                                                  _ PF1 - Help   Page:  1  of  1

                           Derivation Source 01            1
  Source Name.: EMPLOYEE                                   
  Source Owner:                                            
                                                           
  Subschema...: EMPSS01     Schema: IDMSR       Version: 1 
                                                           
                           Derivation Source 02            
  Source Name.: DEPARTMENT                                 
  Source Owner:                                            
                                                           
  Subschema...: EMPSS01     Schema: IDMSR       Version: 1 


           Column #1/Set       Of Der   Oper               Column #2       Of Der1
  DEPT-EMPLOYEE2                   023    ST4                                  05

• 1 Identifying the source tables

Joining the tables on an owner-member set relationship

• 2 Set name
• 3 Number of set owner
• 4 Join operator
• 5 Number of set member

Considerations

1. For ASF to find the non-SQL defined records, you must:
   • Include the ASF dictionary segment in the DBNAME that holds the records.
   • Issue a DCUF SET DBNAME command before entering ASF to set the session default database to the one that holds the records.
2. Deriving from non-SQL defined records requires a knowledge of database navigation. The order in which you specify source record and join information affects the data that is returned. The following considerations apply when deriving from non-SQL defined records:
   • The first record identified is obtained through an area sweep. All other records must be obtainable through set relationships so that one logical retrieval path can be built. If ASF finds that it will perform two area sweeps to obtain source records, then an error is issued for the join information because all the records are not connected in one logical path.
   • Since the first record is obtained through an area sweep, the order in which the records are identified determines how the data will be obtained.
   • The order in which the set joins are specified must coincide with the order in which the source records are identified.
   • A set join is disallowed if the owner and member derivation sources are already directly or indirectly connected. Before accepting a set join as valid, ASF analyzes previously defined set joins to determine if the owner or member derivation records are already directly or indirectly connected. If the records are already connected, the join is not allowed.

The following examples illustrate the above.