Data Movement Utilities Guide and Reference

Using the Data Warehouse Center to Move Data

You can use the Data Warehouse Center (DWC) to move data from operational databases to a warehouse database, which users can query for decision support. You can also use the DWC to define the structure of the operational databases, called sources. You can then specify how the operational data is to be moved and transformed for the warehouse. You can model the structure of the tables in the warehouse database, called targets, or build the tables automatically as part of the process of defining the data movement operations.

The Data Warehouse Center uses the following DB2 functions to move and transform data:

• SQL

  You can use SQL to select data from sources and insert the data into targets. You also can use SQL to transform the data into its warehouse format. You can use the Data Warehouse Center to generate the SQL, or you can write your own SQL.

• Load and export utilities

  You can use these DB2 utilities to export data from a source, and then load the data into a target. These utilities are useful if you need to move large quantities of data. The Data Warehouse Center supports the following types of load and export operations:

  DB2 data export

  Exports data from a local DB2 database into a delimited file.

  ODBC data export

  Selects data from a table in a database that is registered to ODBC, and then writes the data to a delimited file.

  DB2 load

  Loads data from a delimited file into a DB2 table.

  DB2 load into a DB2 UDB EEE database (AIX only)

  Loads data from a delimited file into a DB2 Universal Database Enterprise - Extended Edition database, replacing existing data in a table with new data. This operation acquires the target partitioning map for the database, splits the input file so that each file can be loaded on a database partition, and then runs a remote load operation on all partitions.

• Replication

  You also can use replication to copy large quantities of data from warehouse sources into a warehouse target, and then capture any subsequent changes to the source data. The Data Warehouse Center supports the following types of replication:

  Base aggregate

  Creates a target table that contains aggregated data for a user table, and that is appended at specified intervals.

  Change aggregate

  Creates a target table that contains aggregated data, and that is based on changes that are recorded for a source table.

  Point-in-time

  Creates a target table that matches the source table, and adds a time stamp column to the target table.

  Staging table

  Creates a "consistent-change-data" table that can be used as the source for updated data to multiple target tables.

  User copy

  Creates a target table that matches the source table at the time that the copy is made.

  These operations are supported on all of the DB2 Universal Database workstation operating environments, DB2 Universal Database for OS/390, DB2 for AS/400, and DataJoiner.

• Transformer stored procedures

  You can use the Data Warehouse Center to move data into an OLAP (Online Analytical Processing) database. After the data is in the warehouse, you can use transformer stored procedures to clean up the data and then aggregate it into fact and dimension tables. You can also use the transformers to generate statistical data. Once the data is cleaned up and transformed, you can load it into OLAP cubes, or replicate it to departmental servers, which are sometimes called datamarts. The transformers are included in only some of the DB2 offerings. See your IBM representative for more information.

For more information about the Data Warehouse Center, refer to the Data Warehouse Center Administration Guide.