Setting up the TBC_MD OLAP Metadata Catalog

The OLAP Metadata Catalog for the standard sample application is TBC_MD. For more information on the OLAP Metadata Catalog, see Creating, upgrading, and deleting OLAP Metadata Catalogs.

To set up the TBC_MD OLAP Metadata Catalog:

  1. Create a TBC_MD database using an RDBMS.

    For more information, see Creating the TBC_MD database.

  2. Create tables for the TBC_MD database by running SQL scripts.

    For instructions, see Creating Tables for the TBC_MD Database.

  3. Load data into the TBC_MD database using the XML import utility.

    For instructions, see Using the XML Import utility to load data into the TBC_MD tables.

Note:
If you have a previous release of DB2 OLAP Integration Server and are upgrading your OLAP Metadata Catalog, you cannot roll back to the previous version. The new version of the OLAP Metadata Catalog is not compatible with earlier releases of DB2 OLAP Integration Server.

Creating the TBC_MD database

Create the TBC_MD database in the same way that you create any database using an RDBMS:

CAUTION:
You must create the tables for the TBC_MD database as user TBC or portions of the standard sample application will not work.

Creating Tables for the TBC_MD Database

Create tables for the TBC_MD database with the same utility program that you typically use.

The standard sample application SQL scripts used to create tables for the TBC_MD database are in the ocscript directory where you installed DB2 OLAP Integration Server.

The SQL scripts in the ocscript directory are the same scripts that you use to create any OLAP Metadata Catalog. For information on OLAP Metadata Catalogs, see Creating, upgrading, and deleting OLAP Metadata Catalogs.

The utilities listed in Table 25 have been tested to work with the SQL scripts:

Table 25. Tested Utilities for Creating TBC_MD Tables

Database

SQL Script

Utility Program

DB2

oc_create_db2.sql oc_drop_db2.sql oc_upgrade651_db2.sql

DB2 Command Center or
>DB2 -tvf

Informix

oc_create_informix.sql oc_drop_informix.sql oc_upgrade651_informix.sql

DBAccess

MS SQL Server

oc_create_sqlsrv.sql oc_drop_sqlsrv.sql oc_upgrade651_sqlsrv.sql

Query Analyzer (MS SQL Server 7.0 and 2000)

Oracle

oc_create_oracle.sql
oc_drop_oracle.sql
oc_upgrade651_oracle.sql
oc_create_oracle_unicode.sql
oc_create_oracle9i_unicode.sql

SQL*Plus

Sybase

oc_create_sybase.sql oc_drop_sybase.sql oc_upgrade651_sybase.sql

ISQL

Teradata

oc_create_teradata.sql oc_drop_teradata.sql oc_upgrade651_teradata.sql

BTEQ

DB2 OLAP Integration Server provides the following SQL scripts for each RDBMS (with the exception of Teradata):

If you need to rebuild tables, you must first drop the tables before you build them again.

CAUTION:
You must create the tables for the TBC_MD database as user TBC or portions of the standard sample application will not work.

To create tables for the TBC_MD database:

  1. Start the utility program.
  2. Connect to the TBC_MD database as user TBC.
  3. In the ocscript directory, open the appropriate SQL script file.
  4. Run the SQL script.

    MS-SQL Server: You receive a message that you did not create data or rows. This message is normal because you created only tables and columns.

  5. Verify that you have created the TBC_MD tables; for example, type the following command:
    SELECT * FROM TBC.MS_INFO 

    Alternatively, start the RDBMS and verify that the TBC_MD database has the new tables.

  6. Close the utility program.

Using the XML Import utility to load data into the TBC_MD tables

Use the XML Import utility to load OLAP model and metaoutline data into the TBC_MD tables.

The XML files listed in Table 26 have been tested to work with their respective RDBMS. These files are located in the samples\tbcmodel directory.

Table 26. XML Files for Loading Data into TBC_MD Tables
Database XML File in sample\tbcmodel
  • DB2
  • Oracle
  • MS SQL Server
  • Sybase
  • Teradata
sample_model.xml 
sample_metaoutline.xml
Informix
sample_model_informix.xml
sample_metaoutline.xml

Using XML Import to load data into TBC_MD

To complete the procedure below, you must start Integration Services Console and connect to Integration Server.

To start Integration Services Console and connect to Integration Server:

  1. On the Windows desktop, click Start > Programs > IBM DB2 OLAP Server 8.2 > IBM DB2 OLAP Integration Server > Integration Server.
  2. On the Windows desktop, click Start > Programs > IBM DB2 OLAP Server 8.2 > IBM DB2 OLAP Integration Server > Desktop.
  3. If necessary, click Close to clear the OLAP Metadata Catalog Setup dialog box.

    Integration Services Console automatically displays the Login dialog box.

    1. In the Server text box, under DB2 OLAP Integration Server, select or type the name of a server computer; for example, cypress.

      If the port number on which DB2 OLAP Integration Server communicates with the console has been set to a value other than the default, you must type the server name or IP address and type the non-standard port number, separated by a colon; for example:

      cypress:3390
    2. In the OLAP Metadata Catalog text box, select or type the name of the OLAP Metadata Catalog, TBC_MD, for the sample TBC (The Beverage Company) database.

      Oracle OCI: If you are using Oracle OCI direct connection, be sure to prefix the Net Service Name or the Schema Name with "Oracle" by typing Oracle: and then the data source name.

    3. In the User Name and Password text boxes, type the user name and password for the standard sample application user, TBC, and click OK.

To load sample OLAP model data into the TBC_MD tables using XML Import:

  1. In Integration Services Console, select File > XML Import/Export.
  2. Select the Import tab.
  3. In the XML File Path text box, enter the file path for the sample OLAP model or click the Open XML File button and navigate to the following file:
    is\Samples\tbcmodel
  4. Select the XML file to import for the sample OLAP model data, sample_model.xml.

    Informix: Select the XML file to import for the sample OLAP model for Informix, sample_model_informix.xml.

  5. Click OK.

    Integration Services Console displays the XML Import/Export dialog box with the XML file path and name in the XML File Path text box. The name of the sample OLAP model is displayed in the OLAP Model Name text box.

  6. Click the Import to Catalog button to load the selected XML file into the sample OLAP Metadata Catalog.

To load sample OLAP metaoutline data into the TBC_MD tables using XML Import:

  1. In Integration Services Console, select File > XML Import/Export.
  2. Select the Import tab.
  3. In the XML File Path text box, enter the file path for the sample OLAP metaoutline or click the Open XML File button and navigate to the following file:
    is\Samples\tbcmodel
  4. Select the XML file to import for the sample OLAP metaoutline data, sample_metaoutline.xml.
  5. Click OK.

    Integration Services Console displays the XML Import/Export dialog box with the XML file path and name in the XML File Path text box, the name of the sample OLAP metaoutline in the OLAP Metaoutline Name text box, and the name of the OLAP model upon which this metaoutline is based in the Based on OLAP Model text box.

  6. Click the Import to Catalog button to load the selected XML file into the sample OLAP Metadata Catalog.