Creating or upgrading OLAP Metadata Catalogs

To create an OLAP Metadata Catalog, you must have permission to create tables in the database or have whatever similar access privileges are required by the RDBMS that you are using.

Note:
On MS SQL Server, the tables in a catalog are not accessible if they are created by a user that does not have db_owner privileges.

To create an OLAP Metadata Catalog:

  1. Create a database for OLAP Metadata Catalog tables using the applicable RDBMS utility.

    See Creating databases for OLAP Metadata Catalog tables.

  2. Take one of the following actions:

Creating databases for OLAP Metadata Catalog tables

Create a database for the OLAP Metadata Catalog tables by using the applicable RDBMS utility, in the same way that you create any database.

One OLAP Metadata Catalog can store all OLAP models and metaoutlines from numerous relational data sources. You can also create more that one OLAP Metadata Catalog to store additional OLAP models and metaoutlines for different projects.

Creating or upgrading the OLAP Metadata Catalog automatically

When you complete installation of Integration Services and start Integration Services Console, the program automatically displays the OLAP Metadata Catalog Setup dialog box. This dialog box enables you to create a standard OLAP Metadata Catalog automatically, using Integration Services Console. If you have an older version of the OLAP Metadata Catalog, you also use this dialog box to upgrade the older version to the current version.

If you prefer to create the OLAP Metadata Catalog manually, see Creating the OLAP Metadata Catalog manually.

To create an OLAP Metadata Catalog automatically:

  1. From the Server Name drop-down list in the OLAP Metadata Catalog Setup dialog box, type or select the server computer on which you have installed DB2 OLAP Integration Server.

    It is necessary to type a server name the first time it is used. After the OLAP Metadata Catalog is successfully created, the server name is then displayed in the Server Name drop-down list box.

  2. From the Catalog ODBC DSN drop-down list, select the Data Source Name for the OLAP Metadata Catalog that you are creating.

    Oracle: If you are using 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.

    Note:
    You must create the database in which you will store the OLAP Metadata Catalog, assign the appropriate user permissions, and configure the ODBC connection before you can create the catalog.
  3. In the Code Page drop-down list box, select the code page that corresponds to the code page of the relational database that will be used as the OLAP Metadata Catalog.

    The code page is a portion of the locale which identifies the local language and cultural conventions such as the formatting of currency and dates and the sort order of data.

  4. In the User Name drop-down list, select or type the user name to which you have assigned permission to access the database in which the OLAP Metadata Catalog will be stored.
  5. In the Password text box, type the password for the user name to which you have assigned permission to access the database in which the OLAP Metadata Catalog will be stored.
  6. If you want the OLAP Metadata Catalog Setup dialog box to be displayed automatically each time you start Integration Services Console, leave the Show this dialog at Startup check box selected.

    If you do not select the Show this dialog at Startup check box, you can access the OLAP Metadata Catalog Setup dialog box from the console by selecting Tools > Create Catalog.

    If an OLAP model or metaoutline is open when you select Tools > Create Catalog, you are prompted to disconnect from the current catalog (Connections > OLAP Metadata Catalog > Disconnect). Note that disconnecting from an OLAP Metadata Catalog causes the open OLAP model or metaoutline to close.

  7. Click Create.

    Integration Services Console creates the OLAP Metadata Catalog automatically.

Note:
If you are migrating from a previous release of DB2 OLAP Integration Server, the OLAP Metadata Catalog auto-creation process upgrades the existing OLAP Metadata Catalog.

Creating the OLAP Metadata Catalog manually

If you choose to create the OLAP Metadata Catalog manually, you must run SQL scripts by using the same utility program that you normally use to create tables.

The scripts that you use to create the tables for a non-Unicode OLAP Metadata Catalog are named:

 oc_create_database_name.sql 

The scripts that you use to create the tables for a Unicode OLAP Metadata Catalog are named:

 oc_create_database_name_unicode.sql 

These scripts are listed in Table 21, along with the utility programs with which they have been tested.

Note:
IBM strongly recommends that you use the automatic installation process to create the OLAP Metadata Catalog (see Creating or upgrading the OLAP Metadata Catalog automatically).

To create tables for the OLAP Metadata Catalog database manually:

  1. Start the utility program.
  2. Connect to the database that you created for the OLAP Metadata Catalog.
  3. Open the appropriate SQL script file in the ocscript directory.
  4. Run the SQL script to build the tables.

    On 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 OLAP Metadata Catalog tables.

    For example, type a command such as:

    SELECT * FROM JOIN_HINTS 

    Alternatively, you can start the applicable RDBMS utility program and verify that the OLAP Metadata Catalog has the new tables.

  6. Close the utility program.

Upgrading the OLAP Metadata Catalog manually

If you have OLAP Metadata Catalogs from a release earlier than Version 8.2 and you choose not to use the automatic creation-upgrade process, you must upgrade the existing OLAP Metadata Catalogs manually. You cannot use the existing catalogs with Version 8.2 until they are upgraded.

Note:
If you use Integration Services Console to create an OLAP Metadata Catalog automatically, the system upgrades existing OLAP Metadata Catalogs and you do not need to upgrade them manually (see Creating or upgrading the OLAP Metadata Catalog automatically). IBM recommends that you use the automatic process to create the OLAP Metadata Catalog.

You manually upgrade the tables of an OLAP Metadata Catalog by running the SQL scripts that use the same database utility program that you typically use to create tables. The SQL scripts to upgrade tables for the OLAP Metadata Catalog are in the ocscript directory where you installed DB2 OLAP Integration Server. The upgrade scripts are named oc_upgrade*_database_name.sql and are listed in Table 21, along with the utility programs with which they have been tested.

Note:
If you manually rebuild an OLAP Metadata Catalog, you must drop (delete) the catalog tables by using oc_drop*_database_name.sql. Then you create the OLAP Metadata Catalog tables by using oc_create*_database_name.sql. If you choose to rebuild a catalog manually, it is not necessary to run any upgrade scripts.
CAUTION:
If you drop (delete) an OLAP Metadata Catalog, you also delete the OLAP models and metaoutlines that it contains.

The following topics discuss various aspects of upgrading OLAP Metadata Catalogs manually:

SQL Scripts used to create and upgrade tables

The SQL scripts to create tables for the OLAP Metadata Catalog (listed in Table 21) are located in the ocscript directory where you installed Integration Services.

DB2 OLAP Integration Server provides SQL scripts for each supported RDBMS:

Note:
If you need to rebuild an OLAP Metadata Catalog, you must drop (delete) the catalog tables before you build them. Remember that if you drop an OLAP Metadata Catalog, you also delete the OLAP models and metaoutlines that it contains.

The utilities listed in Table 21 have been tested to work with the SQL scripts.

Table 21. SQL Scripts for Creating an OLAP Metadata Catalog

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

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

MS SQL Server

oc_create_sqlsrv.sql 
oc_drop_sqlsrv.sql 
oc_upgrade651_sqlsrv.sql

Query Analyzer

(MS SQL Server 7.0 and 2000)

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

CAUTION:
After you update an OLAP Metadata Catalog, you cannot roll back to a previous version. The new version of OLAP Metadata Catalog is not compatible with previous releases of Integration Services. In addition, do not attempt to use the Version 8.2 catalog with previous releases of DB2 OLAP Integration Server software. Such an attempt can result in corrupted OLAP Metadata Catalog data.

Upgrading tables in the OLAP Metadata Catalog

To upgrade an OLAP Metadata Catalog manually, you upgrade the tables of the OLAP Metadata Catalog database.

To upgrade tables for the OLAP Metadata Catalog database:

  1. Start the utility program.
  2. Connect to the database that you created for the OLAP Metadata Catalog as the user who created the original OLAP Metadata Catalog tables.
  3. Open the appropriate SQL script file in the ocscript directory.

    See SQL Scripts used to create and upgrade tables.

  4. Run the SQL script (or scripts, if applicable) to upgrade the tables.

    On 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 the new tables have been added to the existing OLAP Metadata Catalog.

    You can verify this by entering a command, for example:

    SELECT * FROM OM_DESCRIPTION 

    Additionally, you can start the applicable RDBMS utility program and verify that the OLAP Metadata Catalog has the new tables.

  6. Close the utility program.