SET Statement for Schema
The SET statements identify and describe a set.
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:
ADD/MODIFY SET statement
►►─┬─ ADD ────┬─ SET name isset-name────────────────────────────────────────► └─ MODify ─┘ ►─┬──────────────────────────────────────────────────────────────────────────►─ └─ SAMe AS SET base-set-name ────────────────────────────────────────────── ─►────────────────────────────────────────────────────────────┬───────────────► ─── of SCHemabase-schema-name─┬─────────────────────────┬─┘ └─version-specification─┘ ►─┬─────────────────────────┬────────────────────────────────────────────────► └─ ORDer is ─┬─ FIRst ──┬─┘ ├─ LASt ───┤ ├─ NEXt ───┤ ├─ PRIor ──┤ └─ SORted ─┘ ►─┬──────────────────────────────────────────────────────┬───────────────────► └─ MODe is ─┬─ CHAin ─┬───────────────────┬──────────┬─┘ │ └─ LINked to PRIor ─┘ │ ├─ VSAm INDex ───────────────────────────┤ └─ INDexindexed-set-mode-specification─┘ ►─┬───────────────────────────────────────────────────┬──────────────────────► ├─ OWNer isrecord-name─┬────────────────────────┬─┤ │ └─owner-record-options─┘ │ └─ OWNer is SYStem ─┬──────────────────────┬────────┘ └─area-specification─┘ ►─┬────────────────────────────────────────────────────────────────────────┬─►◄ │ ┌────────────────────────────────────────────────────────────────────┐ │ └─▼─┬─────────────┬─ MEMber isrecord-name─┬─────────────────────────┬┴─┘ ├─ INClude ◄──┤ └─member-record-options─┘ └─ EXClude ───┘
►►─┬─ USIngsymbolic-index-name────────────────────────────────────────────┬─►◄ └─ BLOck CONtainskey-count keys─┬────────────────────────────────────┬─┘ └─ DISplacement is ─┬─ 0 ◄─────────┬─┘ └─page-count─┘
►►─┬──────────────────────────────────────────────────────┬───────────────────► └─ 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 ────────────────────┘
►►─── WIThin AREaarea-name──────────────────────────────────────────────────► ►─┬─────────────────────────────────────────────────────────────────────┬────►◄ ├─ SUBareasymbolic-subarea-name─────────────────────────────────────┤ └─ OFFset ─┬─ 0 ◄──────────────────────┬── for ─┬─ 100 PERcent ◄─────┬┘ ├─offset-page-countPAGes ─┤ ├─percentPERcent ──┤ └─offset-percentPERcent ──┘ └─page-countPAGes ─┘
►►─┬────────────────────────────────────────────────────────┬─────────────────► └─ 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 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 isset-name────────────────────────────────────────────►◄
DISPLAY/PUNCH SET statement
►►─┬─ DISplay ─┬─ SET name isset-name───────────────────────────────────────► └─ PUNch ───┘ ►─┬───────────────────────────────────────┬──────────────────────────────────► │ ┌───────────────────────────────────┐ │ │ │ ┌───────────────┐ │ │ └─▼─┬─ WITh ──────┬─▼─┬─ DETails ─┬─┴─┴─┘ ├─ ALSo WITh ─┤ ├─ ALL ─────┤ └─ WITHOut ───┘ └─ NONe ────┘ ►─┬──────────────────────┬───────────────────────────────────────────────────► └─ VERB ─┬─ ADD ─────┬─┘ ├─ MODify ──┤ ├─ DELete ──┤ ├─ DISplay ─┤ └─ PUNch ───┘ ►─┬─────────────────────┬────────────────────────────────────────────────────► └─ AS ─┬─ COMments ─┬─┘ └─ SYNtax ───┘ ►─┬─────────────────────────────────┬────────────────────────────────────────►◄ └─ TO ─┬─module-specification─┬─┘ └─ SYSpch ───────────────┘
ADD/MODIFY SET Statement
- SET name isset-nameIdentifies the database set description.Set-namemust be a 1- to 16- character name. Apply the following considerations when selecting set names:
- Set-namemust not be the same as the schema name or the name of any other component (including synonyms) within the schema.
- Becauseset-nameis 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 SETbase-set-nameCopies the entire set description (order, mode, owner, and members) frombase-set-nameof another schema into the descriptionset-name(the object of the ADD or MODIFY).Base-set-namemust identify an existing set.
- of SCHemabase-schema-nameIdentifies the schema that containsbase-set-name. The base schema must have a status of VALID.
- version-specificationUniquely 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 containbase-set-name, the schema compiler issues an error message.
- ORDer isSpecifies the logical order of adding new member record occurrences to a set occurrence at runtime.
- FIRstPositions the new record immediately after the owner record, becoming the first member in the set (a LIFO stack).
- LAStPositions 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.
- NEXtPositions the new record immediately after the current of set.
- PRIorPositions the new record immediately before the current of set. If MODE IS CHAIN is also coded, include LINKED TO PRIOR in the MODE clause.
- SORtedPositions 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 isSpecifies the characteristic of the set that tells CA IDMS/DB how pointers are to be maintained at runtime.
- CHAinLinks 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 PRIorSpecifies 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 INDexIdentifies 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.
- INDexindexed-set-mode-specificationIdentifies the set as an indexed set. This option is not valid for multiple-member sets.
- USIngsymbolic-index-nameSpecifies 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 containskey-countkeys
- Establishes the number of entries in each bottom-level index record (SR8 system record).Key-countmust be an unsigned integer in the range 3 through 8180. For the rationale used in determining a value forkey-count, see Allocating Space for Indexes.
- DISplacement ispage-countpagesIndicates how far away from their owners the bottom level index records are to be stored.Default:0Limits:Unsigned integer 0 to 32,767
- OWNer isrecord-nameIdentifies the record type that owns the set;record-namemust 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)
- owner-record-optionsIdentifies 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.
- next-dbkey-positionRepresents the sequential position of the NEXT set pointer within the owner record's prefix.Limits:Whole integer 1 to 8180
- prior-dbkey-positionRepresents 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.
- AUToCauses 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 isFor SQL access against a non-SQL defined database, defines a primary key field in the owner record.
- system-owned-index-nameIdentifies 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.
- CALcIdentifies 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.
- NULlRemoves the primary key from the set and all foreign keys that are associated with the primary key.
- OWNer is SYStemSpecifies 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-specificationSpecifies 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).
- WIThin AREaarea-nameSpecifies the name of the area.Area-namemust 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).
- SUBareasymbolic-subarea-nameNames 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.
- OFFsetSpecifies 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-countPAGesDetermines 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 areaLimits:An integer in the range 0 through the number of pages inphysical-area-nameminus 1.
- offset-percentPERcentDetermines 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 areaLimits:
- FORpage-countPAGesDetermines 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 storedThe calculated page must not exceed the highest page number in the physical area.
- FORpercentPERcentDetermines 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 areaIfpercentcauses 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:100Limits:1 to 100
- INClude MEMber isrecord-nameIdentifies a record type that is to participate as a member of the set.Record-namemust 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 isrecord-nameIdentifies a record type that is no longer to participate as a member of the set.Record-namemust name a record type that was previously included in the set definition. Additional options of the MEMBER clause are invalid.
- member-record-optionsSpecifies additional information about set members in order to maintain the set at runtime.
- AUToCauses 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:ModeDefaultsMODE IS CHAINCauses a default of NEXT DBKEY POSITION IS AUTO; the LINKED TO PRIOR clause causes a default of PRIOR DBKEY POSITION IS AUTO.MODE IS INDEXCauses 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 VSAMIs not applicable to next and prior set pointers.
- OMIttedIndicates 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-positionAssigns the sequential position of the index set pointer within the member record's prefix.Index-dbkey-positionmust 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" topicWhen assigning pointer positions manually, remember to specify this value if the set is an indexed set.
- next-dbkey-positionAssigns the sequential position of the next set pointer within the member record's prefix.Next-dbkey-positionmust 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-positionAssigns the sequential position of the prior set pointer within the member record's prefix.Prior-dbkey-positionmust 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 OWNerLinks each member record of the named type in the set to the owner record.
- OWNer dbkey POSition isowner-dbkey-positionAssigns the owner pointer position manually.Owner-dbkey-positionrepresents 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 AUToCauses 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 isFor SQL access against a non-SQL defined database, identifies or removes a foreign key in the member record.
- NULlRemoves a previously defined foreign key from the member record; if specified, the owner record must be defined without a primary key.
- element-nameIdentifies an element or a list of elements enclosed in parentheses that identify the foreign key. The elements cannot be group elements andmustmatch the data type and length of the corresponding element in the primary key.
- NULlableIndicates 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
- MANdatorySpecifies 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.
- OPTionalSpecifies 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.AUTomaticSpecifies 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.
- MANualSpecifies 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-expressionIdentifies 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.
- sort-element-nameIdentifies the member record element(s) on whose values the set is to be sorted (that is, the sort control element).Sort-element-namespecifies 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:
sort-element-namevalues (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 multiplesort-element-names for native VSAM sets.
- 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.
- DBKeyFor indexed sets only, specifies that the member record's database key is the set control element. Duplicates are not allowed.
- ASCendingSorts 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 inkey-expression.
- DEScendingSorts 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 inkey-expression.
- NATural sequenceIndicates 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.
- UNCOMpressedApplies to sorted indexed sets only and specifies that similar index entries are maintained in their entirety.
- COMpressedApplies 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 areSpecifies how CA IDMS/DB handles a record occurrence whose sort key duplicates an existing occurrence's sort key.
- FIRstLogically positions record occurrences before the occurrence(s) with the duplicated sort key. FIRST is not valid for native VSAM sets.
- LAStLogically positions record occurrences after the occurrence(s) with the duplicated sort key. LAST is not valid for native VSAM sets.
- NOT allowedDoes not allow record occurrences with duplicate sort keys.
- UNORDeredFor 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 DBkeyFor MODE IS INDEX sets only, sorts record occurrences with duplicate key values by db-key.
DELETE SET Statement
- SET name isset-nameIdentifies the database set description. Set-name must be a 1- to 16- character name.
DISPLAY/PUNCH SET Statement
- DETailsDisplays or punches the entire set description.
- ALLDisplays or punches the entire set description.
- NONeDisplays or punches only the set name.
Set Automatically Deleted if Owner Record is Deleted
If a set's
owner recordis 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 recordis 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 notbe specified; there is no such pointer position for these modes.
CHAIN (without LINKED TO PRIOR)
CHAIN (with LINKED TO PRIOR)
INDEX (with user-defined record type as owner)
INDEX (with SYSTEM as owner)
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 notbe coded; these modes have no such pointer position.
(without LINKED TO PRIOR)
(with LINKED TO PRIOR)
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
beforethe 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.
Minimum SET statement
The following example supplies the minimum SET statement that is required for the set to be a
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."