Minimum SET statement
The following example supplies the minimum SET statement 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
Note: For more information about pointer positioning, system-owned index sets and system record types, and how CA IDMS/DB compresses index entries, see the CA IDMS Database Design Guide.
|
Copyright © 2014 CA.
All rights reserved.
|
|