SQL DML Statements Operating on Network-defined Records

The result of SQL DML statements against network-defined records may depend on set membership options, set primary/foreign key definitions, schema virtual keys option, other definition options, and the current state of the database.
idmscu19
The result of SQL DML statements against network-defined records may depend on set membership options, set primary/foreign key definitions, schema virtual keys option, other definition options, and the current state of the database.
Referential sets can exist for schemas with or without virtual keys, as follows:
  • Schemas with virtual keys
    Without additional definitions, sets defined in schemas with virtual keys have a referential set between the ROWID and virtual foreign keys. See "Referential Set Relationship Using ROWID and Virtual Foreign Keys" at SQL Virtual Keys.
  • Schemas without virtual keys
    Sets defined in schemas without virtual keys in the schema SET statement.
The following sections provide an overview of the SELECT, INSERT, UPDATE and DELETE statements, as they apply to SQL against network-defined data. For detailed information go to the page for the particular statement, by clicking the statement name.
2
2
SELECT statements where one or more tables that are named in the FROM parameter represent network-defined records are always allowed.
Column values are established as follows:
  • If the column represents a nullable foreign key field, its value is:
    • If the record occurrence is a member of at least one set where the field is a foreign key field, the value of the field in the record occurrence, 
    • Null if the record occurrence is not a member of any set where the field is a foreign key field
  • Otherwise, the value of the column is the value of the record element it represents
    An exception is raised if a value in the result table is null and an indicator variable is not specified for the host-variable to which the value is to be returned.
  • The default SELECT column list, used when SELECT * is specified, consists of all columns of the table. 
    • Virtual keys
      follow the defined columns and are in the order of ROWID followed by virtual foreign keys in alphabetical order.
CA IDMS allows INSERT statements where the target table represents a network-defined record.
Effect of INSERT on a Record
  • Causes an occurrence of the record that is represented by the table that is named in the INTO parameter to be stored on the database
  • Columns, whose values are not supplied on the insert, are given standard default values according to their data types (0 for numeric, spaces for character and binary zeros for binary)
  • Has the following effect on system indexes that are defined on the record:
    • Connects the record into every such index that is defined as AUTOMATIC
    • Does not connect the record into any index defined as MANUAL
  • Connects the record into every set for which the values of all columns representing foreign key fields of the set relationship are not null.  The set occurrence to which the record is connected is that owned by the occurrence of the owner record whose primary key value matches the foreign key value of the member.
  • The default INSERT column list is used when no columns are specified, and consists of all columns of the table. 
  • Returns an error if:
    • In attempting to connect the record into a set, no owner record occurrence can be found with a matching primary key value
    • A null value is specified for any column other than one representing a nullable foreign key of some set where the record participates as a member
    • An invalid data value is detected during the operation
    • The operation attempts to store a duplicate row when duplicates are not allowed
Effect of INSERT on a Record for Schemas with Virtual Keys
In addition to the effects of INSERT on a record that is listed above, the following effects of INSERT on a record apply to schemas with virtual keys:
  • A member row ID may be specified for ordering in a set that is defined as NEXT or PRIOR.
  • If the location mode of the record to be inserted is DIRECT, a row ID can be specified as part of the column list. The row ID is used on the store of the record as the suggested dbkey. If the specified row ID is null, a value of -1 is used as the suggested dbkey.
  • In an INSERT column list, when no columns are specified, virtual keys follow the defined columns, and virtual keys are in the order of ROWID followed by virtual foreign keys in alphabetical order.
  • If a null value is specified for a virtual foreign key where the membership in the network-defined set is not optional, an error is returned.  
Limitations
Be aware of the following limitations for INSERT:
  • The target record of the INSERT cannot be named in the FROM parameter of any query specification in the same statement, or in the FROM parameter of any sub-query within the query specification. You cannot insert into a table from which you are selecting directly or through a view.
  • The control field of an OCCURS DEPENDING ON structure cannot be included in the insert column list. On an INSERT, its value is automatically set to 0.
  • For a schema without virtual keys, CA IDMS allows INSERT statements only when  all sets with a membership option of AUTOMATIC, in which the record participates as a member, are defined with a primary/foreign key declaration.
For more information about primary and foreign keys in set definitions, see SET Statement for Schema.
Successful execution of an update depends on both the definition options that are selected and the current state of the database.
Effect of UPDATE on a Record
  • Causes one or more occurrences of the record that is represented by the table to be modified
  • Only fields that are represented by columns that are named in the SET parameter of the UPDATE statement are changed
  • Has the following effect on
    system indexes that are defined on the record
    :
    • If a record occurrence being updated is connected to the index, the index is updated when one or more index key fields are changed.
    • If a record occurrence being updated is not connected to the index, the index is not updated.
  • Has the following effect
    on sets where the record is a member
    :
    • If one or more foreign key fields of the set are changed or are set to NULL, the following operations are performed:
      • If it participates as a member of the set, the record occurrence is disconnected from its current owner.
      • If the value of all foreign key fields is not null and an occurrence of the owner record can be found with a matching primary key, the record occurrence is connected to a (new) owner.
      • The set membership of a record, with owner records whose keys are defined in the member record as foreign keys, are affected regardless of the membership options of the set.
    • If one or more sort key fields of the set are changed, and the record occurrence is a member of the set, the set occurrence is updated to maintain correct ordering.
  • If the following occurs, an error is returned: 
    • There is no owner record, when attempting to connect the record into a set
    • A matching primary key value
    • The record being modified is an owner of a non-empty set that was defined with a primary/foreign key declaration, and one or more of the primary key fields are changed
    • A null value is specified for a column other than one representing a nullable foreign key field of some set where the record participates as a member
    • An invalid data value is detected during the operation
    • The operation attempts to store a duplicate row when duplicates are not allowed
Effect of UPDATE on a Record for Schemas with Virtual Keys
In addition to the effects of UPDATE on a record that is listed above, the following effects of UPDATE on a record apply to schemas with virtual keys:
  • If one or more virtual foreign key fields are set to a non-null value, it must be the ROWID of either the owner or any member of the set to which the updated row is a member, or is to become a member.   A member row ID may be specified for ordering in a set that is defined as NEXT or PRIOR.
  • If the following occurs, an error is returned: 
    • A null value is specified for a virtual foreign key where the network-defined set disallows the disconnection of the record with the set.
    • A ROWID value is specified for a virtual foreign key where the network-defined set disallows the connection or disconnection of the record with the set.
    • A ROWID value is specified for a virtual foreign key that cannot be used as a primary key or another virtual foreign key. Common reasons are the ROWID is non-existent or the record associated with a ROWID is not a member of the set.
Limitations
You need to be aware of the following limitations for UPDATE:
  • The control field of an OCCURS DEPENDING ON structure cannot be included in the insert column list.
  • For a schema without virtual keys, CA IDMS allows UPDATE statements only when all sets with a membership option of AUTOMATIC, in which the record participates as a member, are defined with a primary/foreign key declaration.
DELETE statements where the target table represents a network-defined record, are allowed. Successful execution of such a statement depends both on the definition options that are selected and the current state of the database.
Effect of DELETE on a Record
  • Causes one or more occurrences of the record that is represented by the table to be erased from the database
  • Disconnects a record occurrence from all indexes where it participates
  • Disconnects a record occurrence from all sets where it participates as a member
  • If the record occurrence being erased participates as an owner in one or more non-empty sets, an error is returned.