You can use the Db2® export utility can be
used to move data out of typed tables for a later import. Export moves data from one hierarchical
structure of typed tables to another by following a specific order and creating an intermediate flat
file.
When working with typed tables, the export utility controls what
is placed in the output file; specify only the target table name and,
optionally, the WHERE clause. You can express subselect statements
only by specifying the target table name and the WHERE clause. You
cannot specify a fullselect or select-statement when exporting a hierarchy.
Preservation of hierarchies using traverse order
Typed tables can be in a hierarchy. There are several ways
you can move data across hierarchies:
Movement from one hierarchy to an identical hierarchy
Movement from one hierarchy to a subsection of a larger hierarchy
Movement from a subsection of a large hierarchy to a separate
hierarchy
Identification of types in a hierarchy is database dependent,
meaning that in different databases, the same type has a different
identifier. Therefore, when moving data between these databases, a
mapping of the same types must be done to ensure that the data is
moved correctly.
The mapping used for typed tables is known
as the traverse order, the order of proceeding top-to-bottom,
left-to-right through all of the supertables and subtables in the
hierarchy. Before each typed row is written out during an export operation,
an identifier is translated into an index value. This index value
can be any number from one to the number of relevant types in the
hierarchy. Index values are generated by numbering each type when
moving through the hierarchy in a specific order-the traverse order.
Figure 1 shows a hierarchy with four valid traverse orders:
Person, Employee, Manager, Architect, Student
Person, Student, Employee, Manager, Architect
Person, Employee, Architect, Manager, Student
Person, Student, Employee, Architect, Manager
Figure 1. An example of a hierarchy
The traverse order is important when moving data between table
hierarchies because it determines where the data is moved in relation
to other data. There are two types of traverse order: default and user
specified.
Default traverse order
With the default traverse order, all relevant types refer to
all reachable types in the hierarchy from a given starting point in
the hierarchy. The default order includes all tables in the hierarchy,
and each table is ordered by the scheme used in the OUTER order predicate.
For instance, the default traverse order of Figure 1, indicated by
the dotted line, would be Person, Student, Employee, Manager, Architect.
The
default traverse order behaves differently when used with different
file formats. Exporting data to the PC/IXF file format creates a record
of all relevant types, their definitions, and relevant tables. The
export utility also completes the mapping of an index value to each
table. When working with the PC/IXF file format, you should use the
default traverse order.
With
the ASC or DEL file format, the order in which the typed rows and
the typed tables are created could be different, even though the source
and target hierarchies might be structurally identical. This results
in time differences that the default traverse order identifies when
proceeding through the hierarchies. The creation time of each type
determines the order used to move through the hierarchy at both the
source and the target when using the default traverse order. Ensure
that the creation order of each type in both the source and the target
hierarchies is identical and that there is structural identity between
the source and the target. If these conditions cannot be met, select
a user-specified traverse order.
User-specified traverse order
With the user-specified traverse order, you define (in a traverse
order list) the relevant types to be used. This order outlines how
to traverse the hierarchy and what sub-tables to export, whereas with
the default traverse order, all tables in the hierarchy are exported.
Although
you determine the starting point and the path down the hierarchy when
defining the traverse order, remember that the subtables must be traversed
in pre-order fashion. Each branch in the hierarchy must be
traversed to the bottom before a new branch can be started. The export
utility looks for violations of this condition within the specified
traverse order. One method of ensuring that the condition is met is
to proceed from the top of the hierarchy (or the root table), down
the hierarchy (subtables) to the bottom subtable, then back up to
its supertable, down to the next "right-most" subtable, then back
up to next higher supertable, down to its subtables, and so on.
If
you want to control the traverse order through the hierarchies, ensure
that the same traverse order is used for both the export and the import
utilities.
Example 1
The following examples are based
on the hierarchical structure in Figure 1. To export the entire hierarchy,
enter the following commands:
DB2 CONNECT TO Source_db
DB2 EXPORT TO entire_hierarchy.ixf OF IXF HIERARCHY STARTING Person
Note that setting the parameter HIERARCHY STARTING to Person indicates
that the default traverse order starting from the table PERSON.
Example 2
To export the entire hierarchy,
but only the data for those people over the age of 20, you would enter
the following commands:
DB2 CONNECT TO Source_db
DB2 EXPORT TO entire_hierarchy.del OF DEL HIERARCHY (Person,
Employee, Manager, Architect, Student) WHERE Age>=20
Note that setting the parameter HIERARCHY to Person,
Employee, Manager, Architect, Student indicates a user-specified
traverse order.
