SET Statement for Schema

The SET statements identify and describe a set.
idms19
The SET statements identify and describe a set. Depending on the verb, the SET statements can add, modify, delete, display, or punch the set description.
The schema compiler applies SET statements to the current schema.
The parameters used in the following syntax diagrams are described in sections below the syntax diagrams.
This topic contains the following information:
Syntax
ADD/MODIFY SET statement
►►─┬─ ADD ────┬─ SET name is
set-name
────────────────────────────────────────► └─ MODify ─┘ ►─┬──────────────────────────────────────────────────────────────────────────►─ └─ SAMe AS SET base-set-name ────────────────────────────────────────────── ─►────────────────────────────────────────────────────────────┬───────────────► ─── of SCHema
base-schema-name
─┬─────────────────────────┬─┘ └─
version-specification
─┘ ►─┬─────────────────────────┬────────────────────────────────────────────────► └─ ORDer is ─┬─ FIRst ──┬─┘ ├─ LASt ───┤ ├─ NEXt ───┤ ├─ PRIor ──┤ └─ SORted ─┘ ►─┬──────────────────────────────────────────────────────┬───────────────────► └─ MODe is ─┬─ CHAin ─┬───────────────────┬──────────┬─┘ │ └─ LINked to PRIor ─┘ │ ├─ VSAm INDex ───────────────────────────┤ └─ INDex
indexed-set-mode-specification
─┘ ►─┬───────────────────────────────────────────────────┬──────────────────────► ├─ OWNer is
record-name
─┬────────────────────────┬─┤ │ └─
owner-record-options
─┘ │ └─ OWNer is SYStem ─┬──────────────────────┬────────┘ └─
area-specification
─┘ ►─┬────────────────────────────────────────────────────────────────────────┬─►◄ │ ┌────────────────────────────────────────────────────────────────────┐ │ └─▼─┬─────────────┬─ MEMber is
record-name
─┬─────────────────────────┬┴─┘ ├─ INClude ◄──┤ └─
member-record-options
─┘ └─ EXClude ───┘
indexed_set
►►─┬─ USIng
symbolic-index-name
────────────────────────────────────────────┬─►◄ └─ BLOck CONtains
key-count keys
─┬────────────────────────────────────┬─┘ └─ DISplacement is ─┬─ 0 ◄─────────┬─┘ └─
page-count
─┘
owner_record
►►─┬──────────────────────────────────────────────────────┬───────────────────► └─ NEXt dbkey POSition is ───┬─
next-dbkey-position
─┬─┘ └─ AUTo ────────────────┘ ►─┬──────────────────────────────────────────────────────┬───────────────────► └─ PRIor dbkey POSition is ─┬─
prior-dbkey-position
─┬─┘ └─ AUTo ─────────────────┘ ►─┬────────────────────────────────────────────────┬─────────────────────────►◄ └─ PRImary KEY is ─┬─
system-owned-index-name
─┬─┘ ├─ CALc ────────────────────┤ └─ NULl ────────────────────┘
area_specification
►►─── WIThin AREa
area-name
──────────────────────────────────────────────────► ►─┬─────────────────────────────────────────────────────────────────────┬────►◄ ├─ SUBarea
symbolic-subarea-name
─────────────────────────────────────┤ └─ OFFset ─┬─ 0 ◄──────────────────────┬── for ─┬─ 100 PERcent ◄─────┬┘ ├─
offset-page-count
PAGes ─┤ ├─
percent
PERcent ──┤ └─
offset-percent
PERcent ──┘ └─
page-count
PAGes ─┘
member_record
►►─┬────────────────────────────────────────────────────────┬─────────────────► └─ INDex dbkey POSition is ─┬─ OMItted ────────────────┬─┘ ├─
index-dbkey-position
───┤ └─ AUTo ───────────────────┘ ►─┬────────────────────────────────────────────────────┬─────────────────────► └─ NEXt dbkey POSition is ─┬─
next-dbkey-position
─┬─┘ └─ AUTo ────────────────┘ ►─┬──────────────────────────────────────────────────────┬───────────────────► └─ PRIor dbkey POSition is ─┬─
prior-dbkey-position
─┬─┘ └─ AUTo ─────────────────┘ ►─┬────────────────────────────────────────────────────────────────────────┬─► └─ LINked to OWNer ─┬──────────────────────────────────────────────────┬─┘ └─ OWNer dbkey POSition is ┬
owner-dbkey-position
┬┘ └ AUTo ────────────────┘ ►──┬───────────────────────────────────────────────────────────────────────┬─► └─ FOReign KEY is ─┬──────────────────────────────────────────────────┬─┘ ├─
element-name
──────┬────────────┬─────────────┬─┘ │ └─ NULlable ─┘ │ │ │ │ ┌────────────────────────────────┐ │ │ ▼ │ │ ├─ ( ──
element-name
──┬────────────┬──┴── ) ───┤ │ └─ NULlable ─┘ │ └─ NULl ─────────────────────────────────────────┘ ►─┬─ MANdatory ──┬──┬─ AUTomatic ──┬─────────────────────────────────────────► └─ OPTional ───┘ └─ MANual ─────┘ ►─┬──────────────────┬───────────────────────────────────────────────────────►◄ └─ key-expression ─┘
key_expression
►►─┬──────────────┬─ KEY is ──────────────────────────────────────────────────► ├─ ASCending ──┤ └─ DEScending ─┘ ►─┬─
sort-element-name
─┬───────────────┬───────────────┬────────────────────► │ ├─ ASCENDING ◄──┤ │ │ └─ DEScending ──┘ │ │ ┌─────────────────────────────────────────┐ │ ├─ ( ─▼─ (
sort-element-name
─┬───────────────┬─┴─ ) ─┤ │ ├─ ASCending ◄──┤ │ │ └─ DEScending ──┘ │ └─ DBKey ─┬───────────────┬───────────────────────────┘ ├─ ASCending ◄──┤ └─ DEScending ──┘ ►─┬────────────────────┬─┬────────────────┬──────────────────────────────────► └─ NATural sequence ─┘ ├─ COMpressed ───┤ └─ UNCOMpressed ─┘ ►─┬────────────────────────────────────┬─────────────────────────────────────►◄ └─ DUPlicates are ─┬─ FIRst ───────┬─┘ ├─ LASt ────────┤ ├─ UNORDered ───┤ ├─ NOT allowed ─┤ └─ by DBKey ────┘
DELETE SET statement
►►─── DELete SET name is
set-name
────────────────────────────────────────────►◄
DISPLAY/PUNCH SET statement
►►─┬─ DISplay ─┬─ SET name is
set-name
───────────────────────────────────────► └─ PUNch ───┘ ►─┬───────────────────────────────────────┬──────────────────────────────────► │ ┌───────────────────────────────────┐ │ │ │ ┌───────────────┐ │ │ └─▼─┬─ WITh ──────┬─▼─┬─ DETails ─┬─┴─┴─┘ ├─ ALSo WITh ─┤ ├─ ALL ─────┤ └─ WITHOut ───┘ └─ NONe ────┘ ►─┬──────────────────────┬───────────────────────────────────────────────────► └─ VERB ─┬─ ADD ─────┬─┘ ├─ MODify ──┤ ├─ DELete ──┤ ├─ DISplay ─┤ └─ PUNch ───┘ ►─┬─────────────────────┬────────────────────────────────────────────────────► └─ AS ─┬─ COMments ─┬─┘ └─ SYNtax ───┘ ►─┬─────────────────────────────────┬────────────────────────────────────────►◄ └─ TO ─┬─
module-specification
─┬─┘ └─ SYSpch ───────────────┘
Parameters
ADD/MODIFY SET Statement
  • SET name is
    set-name
    Identifies the database set description.
    Set-name
    must be a 1- to 16- character name. Apply the following considerations when selecting set names:
    • Set-name
      must not be the same as the schema name or the name of any other component (including synonyms) within the schema.
    • Because
      set-name
      is copied into DML programs, it must not be the name of a keyword that is known to either the DML precompiler or the host programming language.
  • SAMe AS SET
    base-set-name
    Copies the entire set description (order, mode, owner, and members) from
    base-set-name
    of another schema into the description
    set-name
    (the object of the ADD or MODIFY).
    Base-set-name
    must identify an existing set.
    • of SCHema
      base-schema-name
      Identifies the schema that contains
      base-set-name
      . The base schema must have a status of VALID.
    • version-specification
      Uniquely qualifies the schema with a version number. The default is the current session option for existing versions. If the schema version that corresponds to HIGHEST or LOWEST does not contain
      base-set-name
      , the schema compiler issues an error message.
  • ORDer is
    Specifies the logical order of adding new member record occurrences to a set occurrence at runtime.
    • FIRst
      Positions the new record immediately after the owner record, becoming the first member in the set (a LIFO stack).
    • LASt
      Positions the new record immediately before the owner record, becoming the last member in the set (a FIFO stack). If MODE IS CHAIN is also coded, include LINKED TO PRIOR in the MODE clause.
    • NEXt
      Positions the new record immediately after the current of set.
    • PRIor
      Positions the new record immediately before the current of set. If MODE IS CHAIN is also coded, include LINKED TO PRIOR in the MODE clause.
    • SORted
      Positions the new record according to the value of one or more of its data elements (called a sort control element) relative to the values of the same elements in other member records of the same type. ORDER IS SORTED must be specified for native VSAM sets.
  • MODe is
    Specifies the characteristic of the set that tells CA IDMS/DB how pointers are to be maintained at runtime.
    • CHAin
      Links each record in the set to the next record (establishes the NEXT pointer for the set) and is mandatory for all set types except indexed sets and native VSAM sets.
      • LINked to PRIor
        Specifies that each record in a chained set will be chained to the prior record (establishes the PRIOR pointer for the set) and to the next record. LINKED TO PRIOR is required if LAST or PRIOR was specified in the ORDER clause.
        When using LINKED TO PRIOR and assigning pointers manually (see the OWNER and MEMBER clauses, later), be sure to code the PRIOR DBKEY POSITION clause of the OWNER and MEMBER clauses.
    • VSAm INDex
      Identifies the set as a native VSAM set representing either a primary index on a KSDS file or an alternate index on an ESDS or KSDS file. Each VSAM set must be represented by a KSDS or PATH file in the physical database definition.
      VSAM sets can have, as members, only records whose location mode is VSAM OR VSAM CALC; owner records are not specified for VSAM sets.
    • INDex
      indexed-set-mode-specification
      Identifies the set as an indexed set. This option is not valid for multiple-member sets.
    Expansion of
    indexed-set-mode-specification
    • USIng
      symbolic-index-name
      Specifies the name of a symbol representing the index. The symbolic index is assigned values in a corresponding physical area definition that identify either:
      • The number of entries in each bottom-level index (SR8) record and, optionally, the displacement of the bottom-level index records from their owners
      • The values that are required by CA IDMS/DB to calculate the number of entries in each bottom-level (SR8) record and its displacement from its owner
    • BLOck contains
      key-count
      keys
    • Establishes the number of entries in each bottom-level index record (SR8 system record).
      Key-count
      must be an unsigned integer in the range 3 through 8180. For the rationale used in determining a value for
      key-count
      , see Allocating Space for Indexes.
      • DISplacement is
        page-count
        pages
        Indicates how far away from their owners the bottom level index records are to be stored.
        Default:
        0
        Limits:
        Unsigned integer 0 to 32,767
  • OWNer is
    record-name
    Identifies the record type that owns the set;
    record-name
    must name a record that is associated with the current schema. This format of the OWNER clause is required for:
    • Chained sets
    • Indexed sets in which the owner is a user-defined record (see also the OWNER IS SYSTEM clause)
    It is not allowed for native VSAM sets.
    • owner-record-options
      Identifies the positions within the owner record's prefix to be used for next and prior (if any) pointers of the set being described and optionally identifies the owner record's primary key.
      The defaults for next and prior pointer positions depend on the set's mode.
      Default: 
      Following are the defaults for each set mode:
    • MODE IS CHAIN causes a default of NEXT DBKEY POSITION IS AUTO; the LINKED TO PRIOR clause causes a default of PRIOR DBKEY POSITION IS AUTO.
    • MODE IS VSAM is not applicable to next and prior set pointers.
    • MODE IS INDEX causes defaults of NEXT DBKEY POSITION IS AUTO and PRIOR DBKEY POSITION IS AUTO, unless OWNER IS SYSTEM is also coded.
    Expansion of
    owner-record-options
    • next-dbkey-position
      Represents the sequential position of the NEXT set pointer within the owner record's prefix.
      Limits:
      Whole integer 1 to 8180
    • prior-dbkey-position
      Represents the sequential position of the PRIOR set pointer within the owner record's prefix; it must be a whole integer in the range 1 through 8180.
      When assigning pointer positions manually, remember to specify a prior db-key position if either of these conditions is true:
      • LINKED TO PRIOR is specified in the MODE clause.
      • INDEX is specified in the MODE clause and OWNER IS SYSTEM is not specified.
    • AUTo
      Causes the schema compiler to automatically assign a set pointer position within the owner record's prefix when the schema description is validated. Until the schema description is validated, a DISPLAY or PUNCH of the set indicates AUTO for pointer positions; after the schema description has been validated, DISPLAY or PUNCH indicates the sequential pointer positions that the validation resolved (see VALIDATE Statement).
    • PRImary KEY is
      For SQL access against a non-SQL defined database, defines a primary key field in the owner record.
      • system-owned-index-name
        Identifies a system-owned index as the primary key. To use this specification, the owner record must be a member of the named index and the named index must be a mandatory automatic set that is defined, as duplicates not allowed. No elements that are named as the keys for the system-owned index can be group elements.
      • CALc
        Identifies the primary key as the owner record's CALC key. To use this specification, the owner record must be stored with a location mode of CALC in which duplicates are not allowed. The CALC key must not contain a group element.
      • NULl
        Removes the primary key from the set and all foreign keys that are associated with the primary key.
  • OWNer is SYStem
    Specifies that the indexed set being described is owned by an internal owner record (SR7 system record). A single occurrence of the SR7 record type owns the set containing all member occurrences (identified in the MEMBER clause, shown next). OWNER IS SYSTEM establishes a relationship that is functionally, though not internally, the same as that of a one-of-a-kind (OOAK) record to its set members.
    OWNER IS SYSTEM is not valid in the following instances:
    • If the set mode is CHAIN
    • If the set mode is VSAM INDEX
    • area-specification
      Specifies the area in which the owner record (SR7) and the index structure is to reside. If this clause is not coded, the owner record and index structure is stored in the same area as the member record (specified in the MEMBER clause).
    Expansion of
    area-specification
    • WIThin AREa
      area-name
      Specifies the name of the area.
      Area-name
      must be the name of an area that is already defined as part of the current schema.
      Defaults for the WITHIN AREA clause are as follows:
      • If WITHIN AREA is coded without SUBAREA or OFFSET, the SR7 owner record is stored within the named area's page range.
      • If WITHIN AREA is not coded, CA IDMS/DB places the owner record in the same area and page range as the set member (in the MEMBER clause).
    • SUBarea
      symbolic-subarea-name
      Names a symbol representing a page range (or subarea). Within the physical area definition, the symbolic subarea is assigned the actual range of pages in which CA IDMS/DB stores the system-owned index structure.
    • OFFset
      Specifies a relative range of pages in the physical area, in terms of either a percentage of the area or a number of pages, in which CA IDMS/DB stores the owner record and the index structure.
      • offset-page-count
        PAGes
        Determines the first page in which CA IDMS/DB stores the owner record based on the lowest page number of the area:
        record lopage = (LPN + offset-page-count) where LPN = the lowest page number in the physical area
        Limits:
        An integer in the range 0 through the number of pages in
        physical-area-name
        minus 1.
      • offset-percent
        PERcent
        Determines the first page in which CA IDMS/DB stores the owner record based on the initial page range of the physical area:
        record's lopage = (LPN + (INP * offset-percent * .01)) where LPN = the lowest page number in the physical area and INP = the initial number of pages in the physical area
        Limits:
        0 to 100.
      • FOR
        page-count
        PAGes
        Determines the last page in which CA IDMS/DB stores the owner record based on the record's low page:
        record's hipage = (RLP + page-count - 1) where RLP = the first page in which the SR7 can be stored
        The calculated page must not exceed the highest page number in the physical area.
      • FOR
        percent
        PERcent
        Determines the last page in which CA IDMS/DB stores the owner record based on the record's low page and the total number of pages in the physical area:
        record's hipage = (RLP + (TNP * percent * .01) - 1) where RLP = the first page in which the SR7 can be stored and TNP = the total number of pages in the physical area
        If
        percent
        causes the calculated high page to be greater than the highest page number in the physical area, CA IDMS/DB ignores the excessive page numbers, and stores the record occurrences up to and including the last page in the physical area.
        Default:
        100
        Limits:
        1 to 100
  • INClude MEMber is
    record-name
    Identifies a record type that is to participate as a member of the set.
    Record-name
    must name a record that is associated with the current schema. Code as many MEMBER clauses as are necessary to declare all of the set's member record types (note that indexed sets and native VSAM sets must include only one member record type).
  • EXClude MEMber is
    record-name
    Identifies a record type that is no longer to participate as a member of the set.
    Record-name
    must name a record type that was previously included in the set definition. Additional options of the MEMBER clause are invalid.
    • member-record-options
      Specifies additional information about set members in order to maintain the set at runtime.
    Expansion of
    member-record-options
    • AUTo
      Causes the schema compiler to automatically assign a set pointer position within the member record's prefix when the schema description is validated. Until the schema description is validated, a DISPLAY or PUNCH of the set indicates AUTO for pointer positions; after the schema description is validated, DISPLAY or PUNCH indicates the pointer positions that the validation resolved.
      Default:
        Defaults assigned by the schema compiler depend on the set mode that is specified for the set as shown in the following table:
      Mode
      Defaults
      MODE IS CHAIN
      Causes a default of NEXT DBKEY POSITION IS AUTO; the LINKED TO PRIOR clause causes a default of PRIOR DBKEY POSITION IS AUTO.
      MODE IS INDEX
      Causes a default of INDEX DBKEY POSITION IS AUTO. (Note that if the DBA codes NEXT or PRIOR, the schema compiler accepts the statement, but changes the specification to INDEX.)
      MODE IS VSAM
      Is not applicable to next and prior set pointers.
    • OMItted
      Indicates no pointer is maintained in the member record for the index. For a system-owned index, this means that there are no index pointers in the member records. If you use this option for a system-owned index, you must also specify the MANDATORY AUTOMATIC set options.
    • index-dbkey-position
      Assigns the sequential position of the index set pointer within the member record's prefix.
      Index-dbkey-position
      must be an integer in the range 1 through 8180. The default for the index pointer position depends on the set mode as shown in the table under the "Usage" topic
      When assigning pointer positions manually, remember to specify this value if the set is an indexed set.
    • next-dbkey-position
      Assigns the sequential position of the next set pointer within the member record's prefix.
      Next-dbkey-position
      must be an integer in the range 1 through 8180. The default for the next pointer position depends on the set mode as shown in the table under the "Usage" topic.
    • prior-dbkey-position
      Assigns the sequential position of the prior set pointer within the member record's prefix.
      Prior-dbkey-position
      must be an integer in the range 1 through 8180. The default for the prior pointer position depends on the set mode as shown in the table under the "Usage" topic. Remember to specify this value if LINKED TO PRIOR is specified in the MODE clause.
    • LINked to OWNer
      Links each member record of the named type in the set to the owner record.
      • OWNer dbkey POSition is
        owner-dbkey-position
        Assigns the owner pointer position manually.
        Owner-dbkey-position
        represents a relative position in the member record's prefix to be used for storing the database key of the owner record of the set; it must be an unsigned integer in the range 1 through 8180. Do not specify this clause for:
      • Indexed sets whose owner is SYSTEM
      • Native VSAM sets
      • OWNer dbkey POSition is AUTo
        Causes the schema compiler to automatically assign the owner pointer position within the member record's prefix when the schema is validated. AUTO is the default.
        Until the schema description is validated, a DISPLAY or PUNCH of the set indicates AUTO for the pointer position; after validation, these statements indicate the actual sequential pointer position.
    • FOReign KEY is
      For SQL access against a non-SQL defined database, identifies or removes a foreign key in the member record.
      • NULl
        Removes a previously defined foreign key from the member record; if specified, the owner record must be defined without a primary key.
      • element-name
        Identifies an element or a list of elements enclosed in parentheses that identify the foreign key. The elements cannot be group elements and
        must
        match the data type and length of the corresponding element in the primary key.
      • NULlable
        Indicates that the foreign key element can contain NULL values. To use this specification, the following rules apply:
      • The membership option of the member record cannot be mandatory automatic
      • The foreign key element cannot be a control key or subordinate to a control key in any sorted set
      • The foreign key element cannot be a CALC key
      • The foreign key element must be defined as NULLABLE in all primary/foreign key sets in which it is named
    • MANdatory
      Specifies that occurrences of this record type cannot be disconnected from the set other than through an ERASE function. MANDATORY must be specified for native VSAM sets and index sets in which the index db-key position is omitted.
    • OPTional
      Specifies that occurrences of this record type can be disconnected from the set without being erased. Either MANDATORY or OPTIONAL must be specified when including a member into a set.
      AUTomatic
      Specifies that occurrences of this record type are connected implicitly to the set as part of the STORE function. AUTOMATIC must be specified for native VSAM sets and index sets in which the index db-key position is omitted.
    • MANual
      Specifies that occurrences of this record type are connected to the set only when the CONNECT function is issued. Either AUTOMATIC or MANUAL must be specified when including a member into a set.
    • key-expression
      Identifies a sorted set. This clause is required if SORTED is specified in the ORDER statement and is invalid for other set orders.
      In a multiple-member set, record occurrences are maintained in order within their record type, but are maintained in no predictable order with respect to records of other types within the set.
    Expansion of
    key-expression
    • sort-element-name
      Identifies the member record element(s) on whose values the set is to be sorted (that is, the sort control element).
      Sort-element-name
      specifies the name of a group or elementary data item that is defined in an element description statement for the named member record type, with the following restrictions:
      • No element that is named FILLER can be used in the sort control element.
      • No element that redefines another element or is subordinate to an element that redefines another element can be used in the sort control element.
      • No repeating element (that is, one defined with an OCCURS clause) and no element subordinate to a repeating element can be used in the sort control element.
      • No element exceeding 256 bytes can be used in the sort control element.
      Multiple
      sort-element-name
      values (each with its own order) can be coded, forming a compound sort control element and thereby allowing the member records to be sorted on more than one element within the record. The element names that make up the sort control element need not be contiguous within the member record. Note, however, that the combined lengths of the elements (as defined in the PICTURE and USAGE clauses of the ELEMENT substatement) must not exceed 256 bytes. Do not code multiple
      sort-element-name
      s for native VSAM sets.
    • DBKey
      For indexed sets only, specifies that the member record's database key is the set control element. Duplicates are not allowed.
      • ASCending
        Sorts the specified sort-element or database key in ascending order. ASCENDING is the default. ASCENDING must be specified for native VSAM sets.
        Note that if you specify ASCENDING before the KEY keyword, you cannot specify ASCENDING or DESCENDING anywhere else in
        key-expression
        .
      • DEScending
        Sorts the specified sort-element or database key in descending order.
        Note that if you specify DESCENDING before the KEY keyword, you cannot specify ASCENDING or DESCENDING anywhere else in
        key-expression
        .
    • NATural sequence
      Indicates that the values of the key fields are sorted and evaluated with negative values before positive values. By default, CA IDMS/DB sorts and evaluates the key fields using a standard collating sequence, which sorts information according to its hexadecimal representation.
      Even if NATURAL SEQUENCE is specified, the schema compiler may use a standard sort sequence if an element in the sort key is a group element. If the data types of the elements subordinate to the group do not affect the natural sort sequence, CA IDMS/DB uses the natural sequence. Otherwise, it uses the standard sort sequence and issues a warning message.
      If STANDARD SEQUENCE is assumed and the CONTROL FIELDS allow NATURAL SEQUENCE, NATURAL SEQUENCE will be selected. Control fields that are display will be set to NATURAL SEQUENCE.
      • UNCOMpressed
        Applies to sorted indexed sets only and specifies that similar index entries are maintained in their entirety.
      • COMpressed
        Applies to sorted indexed sets only and specifies that similar index entries are maintained in compressed form. COMPRESSED saves index space by compressing repeated characters and by causing like index entries to be stored in part: the initial like portion of the entry is stored once for all similar entries and only the different remaining portions are stored for each entry.
    • DUPlicates are
      Specifies how CA IDMS/DB handles a record occurrence whose sort key duplicates an existing occurrence's sort key.
      • FIRst
        Logically positions record occurrences before the occurrence(s) with the duplicated sort key. FIRST is not valid for native VSAM sets.
      • LASt
        Logically positions record occurrences after the occurrence(s) with the duplicated sort key. LAST is not valid for native VSAM sets.
      • NOT allowed
        Does not allow record occurrences with duplicate sort keys.
      • UNORDered
        For native VSAM only, retrieves record occurrences in the order in which they were stored, regardless of the direction in which the set is being searched.
      • by DBkey
        For MODE IS INDEX sets only, sorts record occurrences with duplicate key values by db-key.
DELETE SET Statement
  • SET name is
    set-name
    Identifies the database set description. Set-name must be a 1- to 16- character name.
DISPLAY/PUNCH SET Statement
  • DETails
    Displays or punches the entire set description.
  • ALL
    Displays or punches the entire set description.
  • NONe
    Displays or punches only the set name.
For more information about DISPLAY/PUNCH, see Generic DISPLAY/PUNCH Statement and Expansion of module specification.
Usage
Set Automatically Deleted if Owner Record is Deleted
If a set's
owner record
is deleted (by a DELETE RECORD statement), the set is automatically deleted. Additionally, the deleted record and set are deleted from all subschema descriptions that are associated with the current schema. But if a set's
member record
is deleted (by a DELETE RECORD statement), the set remains.
Explicitly deleting a set
To delete the set (if it has no other member records), use the DELETE SET statement. DELETE deletes the named set description from the data dictionary. Consequently, the set is removed not only from the current schema, but also from the descriptions of all subschemas associated with the current schema. No optional clauses are valid for DELETE operations.
Default automatic pointer assignments for owner records
A valid set description requires pointer positions for the owner record and for each member record.
The defaults for the owner pointer positions depend on the set's mode specification as shown in the following table. Positions for which "none" is indicated have no default and
must not
be specified; there is no such pointer position for these modes.
Set mode
NEXT
PRIOR
CHAIN (without LINKED TO PRIOR)
AUTO
none
CHAIN (with LINKED TO PRIOR)
AUTO
AUTO
VSAM
none
none
INDEX (with user-defined record type as owner)
AUTO
AUTO
INDEX (with SYSTEM as owner)
none
none
Default automatic pointer assignments for member records
A valid set description requires pointer positions for the owner record and for each member record. The defaults for the member record pointer positions depend on the set's mode specification as shown in the following table. Positions for which "none" is indicated have no default and
must not
be coded; these modes have no such pointer position.
Set mode
NEXT
PRIOR
INDEX
CHAIN
(without LINKED TO PRIOR)
AUTO
none
none
CHAIN
(with LINKED TO PRIOR)
AUTO
AUTO
none
VSAM
none
none
none
INDEX
none
none
AUTO
Unlinked indexes
An unlinked index is a system-owned index in which there are no index pointers in the member records. You specify an unlinked index by using the OMITTED option on the INDEX DBKEY POSITION clause of the MEMBER RECORD clause. Unlinked indexes provide the following advantages:
  • You can load and rebuild unlinked indexes faster
  • You can add or remove an unlinked index without restructuring the database, provided the control length of a compressed or variable length member record is not changed
However, unlinked indexes may increase processing overhead. For more considerations about unlinked indexes, see Non-SQL Tuning Options.
The set options for an unlinked index must be MANDATORY AUTOMATIC.
Pointer positions in a record
For a given record, each position must be assigned to only one set pointer, and the positions within the record must be contiguous.
SAME AS SET clause reduces coding
Because SAME AS SET copies an existing description, it can relieve the DBA of a considerable amount of coding. The DBA can create a base set description with SAME AS SET and code additional clauses to alter the description of the new set as desired.
Restrictions for SAME AS SET clause
SAME AS SET must not be specified for a set to which order, mode, owner, or member already is assigned. Consequently, placement of the SAME AS SET clause is restricted as follows:
  • ADD operation -- When used in an ADD operation, SAME AS SET must precede all other optional clauses.
  • MODIFY operation -- SAME AS SET cannot be used in a MODIFY operation unless the set was added with no optional clauses.
Don't change set pointers for existing databases
Do not change set pointers for existing databases. Use the NEXT DBKEY POSITION, PRIOR DBKEY POSITION, INDEX DBKEY POSITION only when adding new sets or when changing sets in a schema for which a database is not yet defined. If you must change set pointers, for example, because a set is deleted, you must restructure your database.
Determine pointer positions before assigning pointers
For a given record, each position must be assigned to only one set pointer, and the positions within the record must be contiguous. When assigning positions manually, determine the pointer positions for all sets in the schema before coding set descriptions. This avoids any conflicts (such as attempting to use the same position twice) and speeds up the mechanical process of adding set descriptions to the schema description.
Percentage offsets assist database maintenance
Of the page limiting options, OFFSET with percentage specifications is the most flexible. As a database grows and must eventually be expanded, the physical areas of the database must also be expanded. If the DBA originally expressed the owner record's page range as a percentage of an area, the range need not be respecified to fit the new physical area description; the runtime system automatically assigns the owner record to the same relative position in the new physical area.
Foreign keys and control length
The specification of a foreign key does not affect the control length of the member record. Foreign key elements may occur beyond the last control key even if the record is compressed or variable in length. However, if a foreign key element does begin after the control length and the record has a database procedure which changes the value of the foreign key field on a store or modify (for example, to convert it to upper case), then you should not use SQL INSERT statements to store new occurrences, nor SQL UPDATE statements to change the value of the foreign key. If you use these statements, the value of the foreign key field
before
the procedure is executed is used to validate the primary/foreign key relationship. This may cause the update to fail on a referential constraint violation or it may cause the member record to be associated with an incorrect owner.
Primary/Foreign key usage
Defining primary and foreign keys for network sets allows SQL to treat sets as referential constraints between network records. Incorporating a foreign key into the member record of a set and identifying the primary and foreign keys in the SET definition statement allows standard application development tools that use JDBC and ODBC metadata functions to discover the relationship between the network records in a set relationship. This also enables the use of standard SQL statements to INSERT, UPDATE, and DELETE rows in the owner and member records, and eliminates the need for SQL syntax extensions and table procedures.
Records which participate in a set that is defined with primary/foreign keys cannot be referenced in SQL statements which use a virtual key schema and if done, results in an error.
Mixed page groups
Chained sets may not cross page group boundaries regardless of the MIXED PAGE GROUP BINDS ALLOWED option setting.
Examples
Minimum SET statement
The following example supplies the minimum SET statement that is required for the set to be a
valid
schema component:
add set name is insplan-rider order is last mode is chain owner is insplan member is rider mandatory automatic.
Defining a chained set
The following example specifies that new records in the COVERAGE-CLAIMS set are added immediately before the owner record, and that both next linkages (required) and prior linkages (optional) are used:
add set name is coverage-claims order is last mode is chain linked to prior . . .
Defining an indexed set
The following example identifies INDEX-JOB-TITLE as an indexed set; each of the set's bottom-level internal index records will contain 50 entries.
add set name is index-job-title order is sorted mode is index block contains 50 keys . . .
Using SAME AS SET to reduce coding
As stated earlier, SAME AS SET copies all information from the copied set to the new set description; the schema compiler treats all subsequent clauses as MODIFY operations. In the following example, the MODE clause is treated as though the statement were a MODIFY SET statement; the statement creates the EMP-POSITION set, which is identical to EMP-POS set, except for its mode, and associates the new set with the current schema.
add set name is emp-position same as set emp-pos of schema testschm version is 1 mode is chain linked to prior.
Calculating the page range of owner records
In the following example, physical area EMP-DEMO-REGION contains 1000 pages, numbered from 1 through 1000. At runtime, CA IDMS/DB will use the offset specified for the system owner record and store the record on pages 51 ((1000 * 5 * .01) + 1) through 1000.
... owner is system within area emp-demo-region offset 5 percent for 95 percent.
In the following example, ORG-DEMO-REGION contains 240 pages, numbered from 2001 through 2240. At runtime, CA IDMS/DB will store the owner record on pages 2041 (2001 + 40) through 2240.
... owner is system within area org-demo-region offset 40 pages for 200 pages.
Manually setting pointer positions
The following MEMBER clause example establishes the EMPOSITION record as a member of the JOB-POSITION set. EMPOSITION has NEXT and PRIOR pointers for this set in positions 1 and 2 of the record prefix; owner linkage is maintained, with the OWNER pointer in position 3 of the record prefix. Runtime operations for EMPOSITION are governed by the OPTIONAL disconnect and MANUAL connect option.
add set name is job-position order is next mode is chain linked to prior owner is job next dbkey position is 1 prior dbkey position is 2 member is emposition next dbkey position is 1 prior dbkey position is 2 linked to owner owner dbkey position is 3 optional manual.
Examples of sorted sets
The following example illustrates two sorted sets:
add set name is ooak-skill order is sorted mode is chain linked to prior owner is ooak next dbkey position is 1 prior dbkey position is 2 member is skill next dbkey position is 1 prior dbkey position is 2 optional automatic key is skill-name ascending duplicates not allowed. add set name is emp-expertise order is sorted mode is chain linked to prior owner is employee next dbkey position is 10 prior dbkey position is 11 member is expertise next dbkey position is 4 prior dbkey position is 5 linked to owner owner dbkey position is 6 mandatory automatic key is emp-expertise ascending duplicates first.
Examples of indexed sets
The following example defines sets similar to those in the previous example. in this example the sets are implemented as indexed sets:
add set name is ooak-skill order is sorted mode is index block contains 70 keys owner is system member is skill index dbkey position is 1 optional automatic key is skill-name ascending compressed duplicates not allowed. add set name is emp-expertise order is sorted mode is index block contains 50 keys owner is employee next dbkey position is 10 prior dbkey position is 11 member is expertise index dbkey position is 4 linked to owner owner dbkey position is 5 mandatory automatic key is emp-expertise ascending duplicates first.
Example of a multiple-member set
The following example illustrates a set with three member record types; the db-key position specification defaults to AUTO:
add set name is coverage-claims order is last linked to prior mode is chain owner is coverage member is hospital-claim mandatory automatic member is non-hosp-claim mandatory automatic member is dental-claim mandatory automatic.
Primary/Foreign key usage:
Defining primary and foreign keys for network sets allows SQL to treat sets as referential constraints between network records. Incorporating a foreign key into the member record of a set and identifying the primary and foreign keys in the SET definition statement, allows standard application development tools that use JDBC and ODBC metadata functions, to discover the relationship between the network records in a set relationship. This also enables the use of standard SQL statements to INSERT, UPDATE, and DELETE rows in the owner and member records and eliminates the need for SQL syntax extensions and table procedures. If the department ID is defined in the EMPLOYEE record as DEPT-ID-4015, the following example shows how the DEPT-EMPLOYEE set can be defined as a referential set.
add set name is dept-employee . . . owner is department . . . primary key is calc member is employee . . . foreign key is dep-id-0415
  • The virtual foreign key feature is an alternative to defining a referential set. For more information, see "Accessing Network-Defined Databases" in the CA IDMS Reference documentation.
  • For more information about pointer positioning, system-owned index sets and system record types, and how CA IDMS/DB compresses index entries, see "Administrating Database Design."