DB2 10.5 for Linux, UNIX, and Windows

Typed table export considerations

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

This graphic shows the hierarchy between the four valid traverse orders detailed above.

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.