How BMC Helix Data Manager exports and imports data

Migrating data involves an export process and an import process.

Data export process

As part of the export process, BMC Helix Data Manager creates temporary tables in the source database. The behavior is different depending on the source database type.

BMC Helix Data Manager creates a temporary table that contains only the request ID and any fields that are referenced in the qualification to the child forms. The extraction to the file system of the data held in the underlying T, H, and B tables uses a join form to this temporary table. This significantly improves performance and storage requirements because the database needs to hold only the minimum table size. When exporting data for the child forms, the temporary table is also used, improving the performance of this data extraction.

The standard export process is as follows:

1. Create a temporary "key fields" table containing primary and foreign key data (that is, request IDs plus any "match on" data or data used by children).
   Indexes are automatically created on all fields.
2. Join this temporary table to the source T, H, and B tables on request ID to extract the data.
3. Process any child nodes joining back to this key fields table.
4. Repeat until you see no more child nodes.
5. Drop the temporary key fields table.
   For incremental or delta-data migration, additional steps are performed that include the export of Entry List tables that include the list of all request IDs and any fields used in child qualifications for each node in the tree.

Data import process

The standard import process is as follows:

1. Create staging tables for the import.
2. Create indexes on the staging tables.
3. Perform the merge into the target tables.
4. Process any child nodes.
5. Repeat until you see no more child nodes.
6. Drop the staging table.

For incremental or delta-data migration, additional steps are performed that include the import of Entry List tables that include the list of all request IDs for each node in the tree and data deletion, where this is configured.

Configuring multiple threads

BMC Helix Data Manager allows multiple threads to be configured for import and export operations. Multithreading can significantly improve performance, particularly with Oracle systems using out-of-row CLOBs.

Multithreading significantly increases the load on database resources including disk and memory.

The optimal number of threads for performance depends on many factors and can only be determined through benchmarking in your specific environment.