Typed table import considerations

The import utility can be used to move data both from and into typed tables while preserving the data's preexisting hierarchy. If desired, import can also be used to create the table hierarchy and the type hierarchy.

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 during an export operation. In turn, the import utility controls the size and the placement of the hierarchy being moved, using the CREATE, INTO table-name, UNDER, and AS ROOT TABLE parameters. As well, import determines what is placed in the target database. For example, it 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.

Table re-creation

The type of import you are able to perform depends on the file format of the input file. When working with ASC or DEL data, the target table or hierarchy must exist before the data can be imported. However, data from a PC/IXF file can be imported even if the table or hierarchy does not already exist if you specify an import CREATE operation. It must be noted that if the CREATE option is specified, import cannot alter subtable definitions.

Traverse order

The traverse order contained in the input file enables the hierarchies in the data to be maintained. Therefore, the same traverse order must be used when invoking the export utility and the import utility.

For the PC/IXF file format, one need only specify the target subtable name, and use the default traverse order stored in the file.

When using options other than CREATE with typed tables, the traverse order list enables one to specify the traverse order. This user-specified traverse order must match the one used during the export operation. The import utility guarantees the accurate movement of data to the target database given the following:

• An identical definition of subtables in both the source and the target databases
• An identical hierarchical relationship among the subtables in both the source and target databases
• An identical traverse order

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 import utility looks for violations of this condition within the specified traverse order.

Examples

Examples in this section are based on the following hierarchical structure 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

Example 1
To re-create an entire hierarchy (contained in the data file entire_hierarchy.ixf created by a prior export operation) using import, you would enter the following commands:

   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 a new hierarchy is being created, 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
To re-create the entire hierarchy of the source database and import it to the target database, while only keeping selected data, you would enter the following commands:

   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, the following will be imported:

• All columns in PERSON into PERSON
• All columns in PERSON plus SALARY in EMPLOYEE into EMPLOYEE
• All columns in PERSON plus SALARY in EMPLOYEE, plus all columns in ARCHITECT into ARCHITECT

Columns SerialNum and REF(Employee_t) are not 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 is also 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
This example shows how to 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 file type 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.