Using the WITH OPTIONS clause of CREATE TABLE, you can
define that a relationship exists between a column in one table and
the objects in the same or another table.
About this task
The WITH OPTIONS clause of CREATE TABLE defines the column
properties for a column in a typed table. These definable table properties
include the relationship between a column in one table and the objects
in the same (or another) table. In the following example, the department
for each employee is actually a reference to an object in the BusinessUnit table.
To define the destination objects of a given reference column, use
the SCOPE keyword on the WITH OPTIONS clause. Figure 1. Dept attribute refers to a BusinessUnit
object
Self-Referencing Relationships
You can define scoped
references to objects in the same typed table as well. The statements
in the following example create one typed table for parts and one
typed table for suppliers. To show the reference type definitions,
the sample also includes the statements used to create the types:
CREATE TYPE Company_t AS
(name VARCHAR(30),
location VARCHAR(30))
MODE DB2SQL
CREATE TYPE Part_t AS
(Descript VARCHAR(20),
Supplied_by REF(Company_t),
Used_in REF(part_t))
MODE DB2SQL
CREATE TABLE Suppliers OF Company_t
(REF IS suppno USER GENERATED)
CREATE TABLE Parts OF Part_t
(REF IS Partno USER GENERATED,
Supplied_by WITH OPTIONS SCOPE Suppliers,
Used_in WITH OPTIONS SCOPE Parts)
Figure 2. Example of a
self-referencing scope
You can use scoped references to write queries that,
without scoped references, would have to be written as outer joins
or correlated subqueries. For example, the two following queries retrieve
the supplier of the part in which the part '1234' is being used:
SELECT Used_in->Supplied_by->Name
FROM Parts
WHERE Partno = Part_t('1234')
Without a scoped reference
the query looks like this:
SELECT S.Name
FROM (Parts AS P RIGHT OUTER JOIN Parts C ON P.Used_in = C.Partno)
RIGHT OUTER JOIN Suppliers S ON C.Supplied_by = S.Suppno
WHERE P.Partno = Part_t('1234')
