The DB2 export and import utilities can be used to move data out of, and into, typed tables. Typed tables may be in a hierarchy. Data movement across hierarchies can include:
The IMPORT CREATE option allows you to create both the table hierarchy and the type hierarchy.
Identification of types in a hierarchy is database dependent. This means 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.
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. This order is called the traverse order. It is the order of proceeding top-to-bottom, left-to-right through all of the supertables and subtables in the 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.
One method 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(s), then back up to next higher supertable, down to its subtables, and so on.
The following figure shows a hierarchy with four valid traverse orders:
There is a default traverse order, in which 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. There is also a user-specified traverse order, in which the user defines (in a traverse order list) the relevant types to be used. The same traverse order must be used when invoking the export utility and the import utility.
If you are specifying the traverse order, remember that the subtables must be traversed in PRE-ORDER fashion (that is, each branch in the hierarchy must be traversed to the bottom before a new branch is started).
The default traverse order behaves differently when used with different file formats. Assume identical table hierarchy and type relationships in the following:
Exporting data to the PC/IXF file format creates a record of all relevant types, their definitions, and relevant tables. Export also completes the mapping of an index value to each table. During import, this mapping is used to ensure accurate movement of the data to the target database. When working with the PC/IXF file format, you should use the default traverse order.
With the ASC, DEL, or WSF file format, the order in which the typed rows and the typed tables were created could be different, even though the source and target hierarchies may be structurally identical. This results in time differences that the default traverse order will identify when proceeding through the hierarchies. The creation time of each type determines the order taken 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.
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. Given:
the import utility guarantees the accurate movement of data to the target database.
Although you determine the starting point and the path down the hierarchy when defining the traverse order, each branch must be traversed to the end before the next branch in the hierarchy can be started. The export and import utilities look for violations of this condition within the specified traverse order.
The movement of data from one hierarchical structure of typed tables to another is done through a specific traverse order and the creation of an intermediate flat file. The export utility (in conjunction with the traverse order) controls what is placed in that file. You only need to specify the target table name and the WHERE clause. The export utility uses these selection criteria to create an appropriate intermediate file.
The import utility controls what is placed in the target database. You can specify an attributes list at the end of each subtable name to restrict the attributes that are moved to the target database. If no attributes list is used, all of the columns in each subtable are moved.
The import utility controls the size and the placement of the hierarchy being moved through the CREATE, INTO table-name, UNDER, and AS ROOT TABLE parameters. For detailed information about IMPORT command parameters, see IMPORT Command.
Examples in this section are based on the following hierarchical structure:
Example 1
To export an entire hierarchy and then recreate it through an import operation:
DB2 CONNECT TO Source_db
DB2 EXPORT TO entire_hierarchy.ixf OF IXF HIERARCHY STARTING Person
DB2 CONNECT TO Target_db
DB2 IMPORT FROM entire_hierarchy.ixf OF IXF CREATE INTO
HIERARCHY STARTING Person AS ROOT TABLE
Each type in the hierarchy is created if it does not exist. If these types already exist, they must have the same definition in the target database as in the source database. An SQL error (SQL20013N) is returned if they are not the same. Since we are creating a new hierarchy, none of the subtables defined in the data file being moved to the target database (Target_db) can exist. Each of the tables in the source database hierarchy is created. Data from the source database is imported into the correct subtables of the target database.
Example 2
In a more complex example, we would like to export the entire hierarchy of the source database and import it to the target database. Although we will export all of the data for those people over the age of 20, we will only import selected data to the target database:
DB2 CONNECT TO Source_db
DB2 EXPORT TO entire_hierarchy.del OF DEL HIERARCHY (Person,
Employee, Manager, Architect, Student) WHERE Age>=20
DB2 CONNECT TO Target_db
DB2 IMPORT FROM entire_hierarchy.del OF DEL INSERT INTO (Person,
Employee(Salary), Architect) IN HIERARCHY (Person, Employee,
Manager, Architect, Student)
The target tables Person, Employee, and Architect must all exist. Data is imported into the Person, Employee, and Architect subtables. That is, we will import:
Columns SerialNum and REF(Employee_t) will not be imported into Employee or its subtables (that is, Architect, which is the only subtable having data imported into it).
| Note: | Because Architect is a subtable of Employee, and the only import column specified for Employee is Salary, Salary will also be the only Employee-specific column imported into Architect. That is, neither SerialNum nor REF(Employee_t) columns are imported into either Employee or Architect rows. |
Data for the Manager and the Student tables is not imported.
Example 3
In this example, we export from a regular table, and import as a single subtable in a hierarchy. The EXPORT command operates on regular (non-typed) tables, so there is no Type_id column in the data file. The modifier no_type_id is used to indicate this, so that the import utility does not expect the first column to be the Type_id column.
DB2 CONNECT TO Source_db
DB2 EXPORT TO Student_sub_table.del OF DEL SELECT * FROM
Regular_Student
DB2 CONNECT TO Target_db
DB2 IMPORT FROM Student_sub_table.del OF DEL METHOD P(1,2,3,5,4)
MODIFIED BY NO_TYPE_ID INSERT INTO HIERARCHY (Student)
In this example, the target table Student must exist. Since Student is a subtable, the modifier no_type_id is used to indicate that there is no Type_id in the first column. However, you must ensure that there is an existing Object_id column, in addition to all of the other attributes that exist in the Student table. Object-id is expected to be the first column in each row imported into the Student table. The METHOD clause reverses the order of the last two attributes.