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:
- Create a database for OLAP Metadata Catalog tables using the applicable
RDBMS utility.
See Creating databases for OLAP Metadata Catalog tables.
- Take one of the following actions:
Create a database
for the OLAP Metadata Catalog tables by using the applicable RDBMS utility,
in the same way that you create any database.
- Create a database device or tablespace.
- Allocate 30 MB for storage.
- Create user names and passwords, if needed.
- Grant user privileges or permissions.
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.
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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
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:
- Start the utility program.
- Connect to the database that you created for the OLAP Metadata Catalog.
- Open the appropriate SQL script file in the ocscript directory.
- 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.
- 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.
- Close the utility program.
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:
- oc_create_database_name.sql to build tables
- oc_drop_database_name.sql to drop tables
- oc_upgrade651_database_name.sql to upgrade tables from Version 8.1 (Hyperion 6.5) to Version 8.1,
Fix Pack 1 (Hyperion 6.5.1). Tables created in Version 8.1, Fix Pack 2 and
later do not need to be upgraded.
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:
- Start the utility program.
- Connect to the database that you created for the OLAP Metadata Catalog as
the user who created the original OLAP Metadata Catalog tables.
- Open the appropriate SQL script file in the ocscript directory.
See SQL Scripts used to create and upgrade tables.
- 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.
- 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.
- Close the utility program.