This chapter explains concepts and procedures that are associated with maintaining the Data Warehouse Center. This chapter covers the following topics:
Back up your warehouse control database, as well as all your warehouse databases, at regular intervals.
To back up the warehouse control database, use the standard procedures for DB2 backup and recovery.
You need to stop the Data Warehouse Center before backing up the control database. To shut down the Data Warehouse Center:
The warehouse logger and server are linked; if you stop the warehouse logger, the server also stops. However, if you stop the warehouse server, the logger does not stop.
To back up your warehouse databases, use the backup and recovery procedures provided by your database manager.
With the Data Warehouse Center, you can expand your network configuration as your warehouse grows. You can add new workstations with new warehouse agents and administrative interfaces, or delete agents and administrative interfaces from your existing configuration.
You can also create a warehouse control database. If you create a warehouse control database, you must run the initialization process again. For more information, see Initializing a second warehouse control database.
To add administrative interfaces or warehouse agents to your Data Warehouse Center configuration, follow the installation process for those components. To delete administrative interfaces or warehouse agents from your Data Warehouse Center configuration, remove those components.
For more information about installing and removing Data Warehouse Center components, see the DB2 Universal Database Quick Beginnings for your operating system and the DB2 Warehouse Manager Installation Guide.
You can use the Data Warehouse Center export and import capabilities to export and import object definitions within the following operating systems:
Export processes and import processes use a large amount of system resources. You might want to limit the use of other programs while you are exporting and importing object definitions.
Because the import and export formats are release-dependent, you cannot use exported files from a previous release to migrate from one release of the Data Warehouse Center to another. If you want to migrate the Data Warehouse Center, see the DB2 Universal Database Quick Beginnings for your operating system.
Alternatively, if you want to make a copy of your warehouse control database (such as when you want separate test and production systems), you can use the DB2 Universal Database export and load utilities to copy the data. However, both databases must have a user ID and password that matches the default warehouse user, so that users can log on to the Data Warehouse Center.
When you export metadata to a tag language file, the Data Warehouse Center finds the objects that you want to export and produces tag language statements to represent the objects. It then places the tag language statements into files that you can import into another Data Warehouse Center.
If you export a process, you might export a large volume of metadata. The following objects are exported with a process:
To export a tag language file:
The Export Metadata window opens.
On Windows NT systems, the file is written to the path and directory from which you start the DB2 Control Center. If you do not have write access to that directory (for example, if the Control Center is installed on a network drive), the file is written to the home path and directory specified by your Windows NT environment variable, usually the C drive and root directory.
On AIX systems, the file is written to the path and directory from which you invoke the Data Warehouse Center. If you do not have write access to that directory, the file is written to your home directory.
If you want to export all available objects in a tree, click >>. The objects move to the Selected objects list. If a node on the tree does not contain objects within it, it is not moved.
The Export Information window opens and displays a message that tells you whether the export process completed successfully. If errors occurred, the Export Metadata window remains open so that you can try the export procedure again. You can find more information about a particular error message in the IBM DB2 Universal Database Message Reference.
Several files can be created during a single export process. For example, when you export metadata definitions for BLOB data, multiple tag language files are created. The first file created in the export process has an extension of .tag. If multiple files are created, the file name that is generated for each supplementary file has the same name as the tag language file with a numeric extension.
For example, if the tag language file name you specified is e:\tag\steps.tag, the supplementary tag language files are named e:\tag\steps.1, e:\tag\steps.2, and so on. Only the file extension is used to identify the supplementary files within the base tag language file, so you can move the files to another directory. However, you should not rename the files. You must always keep the files in the same directory, otherwise you will not be able to import the files successfully.
You can import object definitions for use in your Data Warehouse Center system. You might want to import sample data into a warehouse or you might want to import data if you are creating a prototype for a new warehouse.
When you import a tag language file, the metadata that it contains is stored in the warehouse control database. The Data Warehouse Center will accept a tag language file that is not generated by another Data Warehouse Center system. For example, you can import a tag language file that is generated by an ETI product.
Any object with an unknown OBJTYPE in a non-Data Warehouse Center tag language file will be ignored, and processing will continue. If an object is of a known OBJTYPE, but does not contain all the properties required by the Data Warehouse Center, an error will occur.
If you are using the import utility to establish a new Data Warehouse Center, you must initialize a new warehouse control database in the target system. After you complete this task, you can import as many tag language files as you want.
For more information about how to initialize a new control database, see Initializing a second warehouse control database.
After you complete the export process, and a tag language file is created, the file is ready to import to a target system.
Before you the import the file, consider the following issues:
If you are using the import utility to move a warehouse source from a test environment to a production environment, make sure that the production environment does not already have a warehouse source with the same warehouse source name unless you want to over write the definition of the warehouse source.
If you import a step into a system that contains a step with the same name, then you must either delete the step that you want to overwrite, or change the step to development mode. Otherwise, the step cannot be updated, and an error will occur.
You use a logical order to import objects. An object that is referred to but not defined in a tag language file must be defined in the destination warehouse control database. You can do this by first importing the object definition that is referred to in a separate tag language file, or you can define the object in the Data Warehouse Center tree view.
For example, you can import warehouse source definitions first. When the warehouse source definitions are ready for use, you can import subject areas, warehouse target definitions, and any corresponding steps individually.
Do not use the import function to migrate from a previous version of the Data Warehouse Center. You must always import and export tag language files using the same version of the Data Warehouse Center. For information about migrating from a previous version of the Data Warehouse Center, see the DB2 Universal Database Quick Beginnings for your operating system.
To import the metadata into a Data Warehouse Center:
The Import Metadata window opens.
After a successful import procedure, you receive a message with return code 0 or a warning return code, and the Import Metadata window closes. No changes are committed to the control database when an error occurs.
If an error occurs during the import process, you receive an error message with an error return code and the Import Metadata window remains open.
Common errors include:
Check the log file to determine what object or relationship was being imported when the error occurred. If the object is not listed in the message, it should be the last object that is listed before the return code and error message information at the end of the log file. Fix the error, then import the object definitions again. If you cannot determine the error or you cannot fix it, contact IBM Software Support or the vendor who provided the tag language file.
After a successful import, you receive a message with a return code of 0, and the Import Metadata window closes.
An imported subject area, warehouse source, or warehouse target will not be displayed in the tree view unless you belong to a warehouse group that has access to the object. A warehouse group that is associated with exported warehouse sources or warehouse targets must be related to a process that uses those objects; some warehouse groups might not be exported. If the warehouse groups that you want your warehouse sources and warehouse targets to be related to are not imported, you need to add the warehouse groups to the Data Warehouse Center system. Metadata for individual users is not exported. You must add users to the Data Warehouse Center.
The Data Warehouse Center assigns development mode to all imported steps so that you can verify that the steps are complete and accurate before you promote them to test or production mode.
Before you use imported objects: Update database information for warehouse sources and warehouse targets (database name, user ID, and password), if necessary. If any new agent site definitions were imported, their passwords also need to be updated. If you imported any programs that have password type parameters, you must reset those values.
Add your user ID to a warehouse group that has access to the imported processes and warehouse targets.
Before you run imported steps:
After you import a tag language file that includes steps that include joins, you might notice the tables are not placed as you expected in the Process Model or Warehouse Schema Model. Some information cannot be imported from one system to another. You will need to reposition your steps and star schemas using the Process or Warehouse Schema Model window.
Importing tag language files again If you import an existing step again, it must be in development mode, or an error will occur. You must demote steps to development mode before importing them again.
Most tag language files can be imported more than once without errors. If a tag language file includes primary and foreign keys, the keys must be deleted before the file can be imported again.
If the names or order of columns in a table or file have changed (for example, a warehouse source or warehouse target table is redefined), delete the warehouse source table, file, or step definition (including the target table in the warehouse target) from the target system before you import the same table or step again. At a minimum, verify that the column or field information is imported as you expected.
Publishing metadata is the process of transferring metadata from the Data Warehouse Center to the Information Catalog Manager. An information catalog is the set of tables managed by the Information Catalog Manager that contains business metadata that helps users identify and locate data and information available to them in the organization. When you publish metadata to the information catalog, you provide end users and system analysts with a fast path to seeing what is in the warehouse.
From the Data Warehouse Center, you can publish:
This chapter discusses publishing Data Warehouse Center metadata only. For information about publishing OLAP server metadata, see the Information Catalog Manager Administration Guide.
To publish metadata, you must install the DB2 Warehouse Manager package with the Information Catalog Manager. The Information Catalog Manager runs on Windows 32-bit operating systems. For information about the DB2 Universal Database platforms on which an information catalog can reside, see the Information Catalog Manager Administration Guide.
Before you publish metadata, you need to ensure that the correct environment is established.
The Information Catalog Manager administrator function must be installed both on the warehouse server site and on the Data Warehouse Center administrative interface component if they are on different workstations.
The Data Warehouse Center administrative interface must have DB2 connectivity to the information catalog APIs.
Table 18 provides the mapping between object types in the Data
Warehouse Center and information catalog. The Data Warehouse Center
uses this mapping when you export the metadata to the information
catalog. See Appendix B, Metadata mappings for a detailed mapping of object types and object
type properties.
Table 18. Mapping between Data Warehouse Center and information catalog object types
| Data Warehouse Center object type | Information catalog object type |
|---|---|
| Process | DWC Process |
| Step | Transformation (at the table or column level) |
| Column or field | Columns or fields |
| Warehouse source or warehouse target | Databases, IMS database definitions |
| Subject area | Business subject areas |
| Table or file | Relational tables and views, Files, IMS segments |
| Warehouse schema | Star Schema |
When you publish a process, the following objects are published with it:
You can limit the number of underlying processes that are published by selecting the Limit the levels of objects in the tree option in the Publish Data Warehouse Center Metadata notebook.
Requirement: Before you publish metadata, make sure that you are not logged on to the Information Catalog Manager as an administrator.
To select the metadata that you want to publish:
The object moves to the Selected objects list. Repeat this step for all the objects that you want to publish.
If you want to publish all available objects, click >>. If a node on the tree does not contain objects within it, it is not moved.
You can map processes and their source tables at the column level to get a more detailed view of the data.
If you set a limit, the number of levels of objects that are published and displayed in the tree view is limited to the number of levels that you specify.
The Publish Information window opens and displays a message that tells you whether the publish process completed successfully. The Publication Information window displays the contents of a log file that lists which metadata objects were published successfully. If errors occurred, the notebook remains open when you close the Publication Information window so that you can try to publish objects again. You can find more information about a particular error message in the IBM DB2 Universal Database Message Reference.
If the publication is successful, the notebook closes when you close the Publication Information window, and two publication objects are added to the list of publications in the Publish Metadata window. One publication updates control metadata, the other updates definition metadata. Control metadata is metadata that describes changes to data in the warehouse. Examples of control metadata are the date and time that a table is updated by the processing of a step. Definition metadata is metadata that describes the objects in the warehouse, the sources of data, and the transformations that are applied to the data. Examples of definition metadata are column names, table names, and database names. The first time that you publish metadata, both control and definition metadata is registered in the information catalog. When you update metadata, you can choose to update the control metadata, the definition metadata, or both types.
After you publish metadata, you can use the publication objects (created during the publish process) periodically to propagate to the information catalog the changes that are made in the Data Warehouse Center. This process is called metadata synchronization.
When you synchronize metadata, the metadata for an object that is registered in the information catalog is updated either when you run the publication or based on a schedule that you create for the publication. Metadata is not updated in the information catalog in the following situations:
Requirement: If you plan to synchronize metadata, you must use the Data Warehouse Center warehouse control database as your information catalog database.
To update published metadata:
You can automate updates to previously published metadata by creating a schedule.
To schedule regular updates of metadata to the information catalog:
See Scheduling warehouse processes for information about using the Schedule notebook.
You can view the log files that show the result of the update to the
publication. The files are located in the directory that is specified
by the VWS_LOGGING environment variable. The default value of the
VWS_LOGGING variable for Windows NT is \sqllib\logging, where
x is the drive where the DB2 Universal Database is
installed. The following log file names are used:
| Type of metadata | Purpose | Log file name |
|---|---|---|
| Update of Data Warehouse Center control metadata | Describes changes to objects in the warehouse | ICMDWCD.OUT |
| Update of Data Warehouse Center definition metadata | Describes objects in the warehouse, the sources of data, and the transformations that are applied to the data | ICMXCHG.OUT |
When an object is deleted in Data Warehouse Center, information about the deleted object is stored in the warehouse control database. When the metadata is updated during the synchronization process, the Data Warehouse Center propagates these deletions to the information catalog before importing other changes into the information catalog. When metadata synchronization completes successfully, the Data Warehouse Center removes the entries in the warehouse control database. Because the Data Warehouse Center removes the entries, deletions can be propagated to only one information catalog. If you need to make the deletions to a second information catalog, you must delete those items manually.
If you change the name of a warehouse object that you previously published to the information catalog, you must publish the object again to update the information catalog. The object with the old name is not overwritten, so both objects exist in the information catalog after metadata synchronization; you must delete the old object manually.
When you install the warehouse server, the warehouse control database that you specify during installation is initialized. Initialization is the process in which the Data Warehouse Center creates the control tables that are required to store Data Warehouse Center metadata. If you have more than one warehouse control database, you can use the Data Warehouse Center--Control Database Management window to initialize the second warehouse control database. However, only one warehouse control database can be active at a time.
The Data Warehouse Center will create the database that you specify on the warehouse server workstation if the database does not already exist on the workstation. If you want to use a remote database, create the database on the remote system and catalog it on the warehouse server workstation.
To initialize a second warehouse control database:
The window remains open. The Messages field displays messages that indicate the status of the creation and migration process.
You can use the Data Warehouse Center Properties notebook to change global settings for your Data Warehouse Center installation. You can override many global settings in the objects that use them. For example, you can use the Properties notebook to specify the default behavior of a processing step when the warehouse agent finds no rows in the source table or file. You can override this global setting in a particular step.
You can use the configuration tool only if the Data Warehouse Center server is installed on the workstation (as well as the administrative client).
Important: Some of the fields in the Properties notebook should be changed only on the recommendation of IBM Software Support. For more information, see the online help for the Data Warehouse Center Properties notebook.
Use the Data Warehouse Center Properties notebook to change the Data Warehouse Center configuration parameters. For example, you can change the parameters for the default notification messages to suit your particular business situation.
You must have administrative privilege to change Data Warehouse Center properties.
Open the Data Warehouse Center Properties notebook:
See the online help for the Data Warehouse Center Properties notebook for detailed descriptions of the settings in the notebook.
You can also use the Data Warehouse Center Properties notebook to run a Data Warehouse Center component trace. For more information, see Component trace data.