DB2 OLAP Integration Server 7.1
Fixpack 4
Additional instructions for OLAP Integration Server
This file contains the following sections:
ODBC Requirements
Configuration Tips
New Sample Applications
ODBC Requirements
Starting with Fixpack 2, IBM DB2 OLAP Integration Server, requires an ODBC.ini
file for operation of Open Database Connectivity (ODBC) connections from
OLAP Integration Server to the relational data source and to the OLAP Metadata
Catalog.
On Windows systems, this file is in the Registry under HKEY_LOCAL_MACHINE/SOFTWARE/ODBC.
Use ODBC Data Source Administrator to store information about how to connect
to a relational data source.
On UNIX systems, DB2 OLAP Integration Server installation creates a
model odbc.ini file. To store information about how to connect to a relational
data source, edit the file using your preferred editor.
The ODBC.ini file is available in ODBC software packages and is included
with Microsoft Office software. For more information about applications
that install ODBC drivers or the ODBC Administrator, visit the following
web site:
http://support.microsoft.com/support/kb/articles/Q113/1/08.asp
For Oracle users on AIX and HP-UX machines: To configure ODBC for Oracle,
you must update the ODBC.ini file to point to the MERANT 3.6 drivers.
Operating System Requirements:
-
Windows NT 4.0 servers with SP 5 and Windows 2000 on Pentium or later computers
-
AIX version 4.3.3 or higher servers on PowerPC computers, including RS
6000
-
HP-UX version 11.0 servers on PA-RISC computers
-
Solaris version 2.6, 7, and 8 (Sun OS 5.6, 5.7, or 5.8) servers on Sun
SPARC or ULTRASPARC computers
-
Windows 95, Windows 98, Windows NT 4.0 SP5, or Windows 2000 for client
software
With Fixpak 2, DB2 OLAP Integration Server manages ODBC connections to
the relational data source and to the OLAP Metadata Catalog. To accommodate
these ODBC connections, DB2 OLAP Integration Server software uses ODBC
drivers on Windows NT 4.0, Windows 2000, AIX, HP-UX, and Solaris.
Supported ODBC Drivers matrix:
-
DB2 UDB 6.1 Database Client: DB2 6.1 ODBC drivers on Windows NT 4.0 SP5
or Windows 2000, AIX 4.3.3, HP-UX 11.0, and Solaris 2.6, 7, or 8 (Sun OS
5.6, 5.7, or 5.8).
-
DB2 UDB 7.1 Database Client: DB2 7.1 ODBC drivers on Windows NT 4.0 SP5
or Windows 2000, AIX 4.3.3, HP-UX 11.0, and Solaris 2.6, 7, or 8 (Sun OS
5.6, 5.7, or 5.8).
-
Oracle 8.04 and 8i SQL*Net 8.0 Database Client: MERANT 3.6 ODBC drivers
on Windows NT 4.0 SP5 or Windows 2000, AIX 4.3.3, HP-UX 11.0, Solaris 2.6,
7 or 8 (Sun OS 5.6, 5.7, or 5.8).
-
MS SQL Server 6.5.201 (no Database Client required): MS SQL Server 6.5
ODBC drivers on Windows NT 4.0 SP5 or
-
Windows 2000.
-
MS SQL Server 7.0 (no Database Client required): MS SQL Server 7.0 ODBC
drivers on Windows NT 4.0 SP5 or
-
Windows 2000.
-
Sybase 11.x: Not currently supported.
-
Informix 9.x: Not currently supported.
Configuration Tips
This supplements the OLAP Setup and User's Guide for DB2 OLAP Integration
Server 7.1.
Configuring Data Sources on UNIX Systems
On AIX, HP-UX and Solaris, you must manually set environment variables
for ODBC and edit the odbc.ini file to configure the relational data source
and OLAP Metadata Catalog. Note: Edit the odbc.ini file if you add
a new driver or data source or if you change the driver or data source.
Configuring ODBC Environment Variables
On UNIX systems, you must set environment variables to enable access to
ODBC core components. The is.sh and is.csh shell scripts that set the required
variables are provided in the DB2 OLAP Integration Server home directory.
You must run one of these scripts before using ODBC to connect to data
sources. You should include these scripts in the login script for the user
name you use to run OLAP Integration Server.
Editing the odbc.ini File
To configure a data source in an odbc.ini file, you must add a name and
description for the ODBC data source and provide the ODBC driver path,
file name, and other driver settings in a separate section that you create
for the data source name. The DB2 OLAP Integration Server installation
program installs a sample odbc.ini file in the ISHOME directory. The file
contains generic ODBC connection and configuration information for supported
ODBC drivers. Use the file as a starting point to map the ODBC drivers
that you use to the relational data source and OLAP Metadata Catalog.
Note: If you use a different file than the odbc.ini file, be sure to
set the ODBCINI environment variable to the name of the file you use.
To add a data source to an odbc.ini file:
-
On the computer running OLAP Integration Server, open the odbc.ini file
by using a text editor such as vi.
-
Find the section starting with [ODBC Data Sources] and add a new line with
the data source name and description; for example: mydata=data source
for analysis. To minimize confusion, the name of the data source
should match the name of the database in the RDBMS.
-
Add a new section to the file by creating a new line with the name of the
new data source enclosed in brackets; for example: [mydata]
-
On the lines following the data source name, add the full path and file
name for the ODBC driver required for this data source and any other required
ODBC driver information. Use the examples shown in the following sections
as a guideline to map to the data source on your RDBMS. Note: Make
sure that the ODBC driver file actually exists in the location you specify
for the Driver= setting.
-
When you have finished editing odbc.ini, save the file and exit the text
editor. For more information about the odbc.ini file and the ODBC driver
settings for each RDBMS, see the MERANT DataDirect Connect ODBC Reference
in PDF format and the related Readme files in the ODBCDocs directory. For
more information about native ODBC driver settings, refer to the installation
documentation for your RDBMS native ODBC drivers.
Example of ODBC Settings for DB2
Following is an example of how you might edit odbc.ini to connect to a
relational data source, db2data, on DB2 Version 6.1 (on AIX), using an
IBM DB2 native ODBC driver.
Use the vi $ODBCINI command to edit the odbc.ini file and insert the
following statements:
[ODBC Data Sources]
db2data=DB2 Source Data on AIX
...
[db2data]
Driver=/home/db2inst1/sqllib/lib/db2.o
Description=DB2 Data Source - AIX, native
Example of ODBC Settings for Oracle
Here is an example of how you might edit odbc.ini to connect to a relational
data source, oradata, on Oracle Version 8 (on Solaris), using a MERANT
Version 3.6 ODBC driver. Note: LogonID and Password in this example
are overridden with the actual values used in the Hyperion Integration
Server user name and password.
[ODBC Data Sources]
oradata=Oracle8 Source Data on Solaris
...
[myoracle] Driver=
/export/home/users/dkendric/is200/odbclib/ARor815.so
Description=my oracle source
Note: ServerName=mytnsServerName must refer to the name of an Oracle
database defined in the tnsnames.ora file. For more information, see the
Oracle installation documentation.
Tip: Run ivtestlib to verify that the environment is set to run the
correct odbc driver file. For example, run ivtestlib and paste the path
and file name that follow Driver= in the odbc.ini file that you have edited.
Configuring the OLAP Metadata Catalog on UNIX Systems
Configuring an OLAP Metadata Catalog on AIX, HP-UX, and Solaris is similar
to configuring a data source. For the OLAP Metadata Catalog database, add
a data source name and section to the odbc.ini file. No other changes are
required.
You must create an OLAP Metadata Catalog database in a supported RDBMS
before configuring it as an ODBC data source.
Here is an example how you might edit odbc.ini to connect to the OLAP
Metadata Catalog, TBC_MD, on DB2 Version 6.1 (on Solaris), using a native
ODBC driver:
[ODBC Data Sources]
ocd6a5a=db2 v6
...
[ocd6a5a]
Driver=/home/db2instl/sqllib/lib/db2.0
Description=db2
Using ODBC drivers coded in Unicode on Solaris:
On Solaris, the OLAP Integration Server (OIS) Desktop does not support
ODBC drivers coded in Unicode. If you use a Unicode ODBC driver,
you might experience problems connecting to the server component.
To enable the connection, update your odbc.ini file and specify a non-Unicode
driver, such as libdb2_36.so, under the $ISHOME/odbclib directory.
For example:
[ODBC Data Sources]
TBC_MD=IBM DB2 ODBC DRIVER
TBC=IBM DB2 ODBC DRIVER
[TBC_MD]
Driver=/export/home/arbor7fp/sqllib/lib/libdb2_36.so
Description=Sample DB2 ODBC Database
[TBC]
Driver=/export/home/arbor7fp/sqllib/lib/libdb2_36.so
Description=Sample DB2 ODBC Database
[ODBC]
Trace=0
TraceFile=odbctrace.out
InstallDir=/export/home/arbor7fp/sqllib/lib
Configuring Data Sources on Windows Systems
To configure a relational data source on Windows NT or Windows 2000 systems,
you must start ODBC Administrator and then create a connection to the data
source that you will use for creating OLAP models and metaoutlines.
Run the ODBC Administrator utility from the Windows Control Panel. Note:
This example creates a DB2 data source. The dialog boxes for other RDBMSs
will differ.
To configure a relational data source with ODBC Administrator, complete
the following steps:
-
On the Windows desktop, select Start > Settings > Control Panel to open
the Control Panel window.
-
In the Control Panel window, perform one of the following steps:
-
On Windows NT, double-click the ODBC icon to open the ODBC Data Source
Administrator dialog box.
-
On Windows 2000, double-click the Administrative Tools icon, and then double-click
the Data Sources (ODBC) icon to open the ODBC Data Source Administrator
dialog box.
-
In the ODBC Data Source Administrator dialog box, click the System DSN
tab.
-
Click Add to open the Create New Data Source dialog box.
-
In the driver list box of the Create New Data Source dialog box of ODBC
Administrator, select an appropriate driver—for example, IBM DB2 ODBC Driver—and
click Finish to open the ODBC IBMDB2 Driver - Add dialog box.
-
In the ODBC IBM DB2 Driver - Add dialog box Database alias drop-down list,
select the name of the database for your relational source data (for example,
TBC in the sample application). The name of the selected database is automatically
displayed in the Data Source Name text box.
-
If you want to change the name of the data source, select the name displayed
in the Data Source Name text box, press Enter, and type the name you want
to use for the data source.
-
In the Description text box, type an optional description that indicates
how you use this driver and click Add. For example, type the following
words to describe the My Business database:
Customers, products, markets
You might type the following words to describe the sample application database:
Sample relational data source
The descriptions help to identify the available data sources for your selection
when you connect from Hyperion Integration Server Desktop.
-
Click OK to return to the ODBC Data Source Administrator dialog box. The
data source name you entered and the driver you mapped to it are displayed
in the System Data Sources list box on the System DSN tab.
To edit configuration information for a data source:
-
Select the data source name and click Configure to open the ODBC IBM DB2
- Add dialog box.
-
Correct any information you want to change.
-
Click OK twice to exit.
Configuring the OLAP Metadata Catalog on Windows Systems
To configure an OLAP Metadata Catalog on Windows NT or Windows 2000, start
ODBC Administrator and then create a connection to the data source that
contains the OLAP Metadata Catalog database.
Note: This example creates a DB2 data source. Dialog boxes for other
RDBMSs will differ.
To create a data source for the OLAP Metadata Catalog, complete the
following steps:
-
On the desktop, select Start > Settings > Control Panel to open the Control
Panel window.
-
In the Control Panel window, perform one of the following steps:
-
On Windows NT, double-click the ODBC icon to open the ODBC Data Source
Administrator dialog box.
-
On Windows 2000, double-click the Administrative Tools icon, and then double-click
the Data Sources (ODBC) icon to open the ODBC Data Source Administrator
dialog box.
-
In the ODBC Data Source Administrator dialog box, click the System DSN
tab.
-
Click Add to open the Create New Data Source dialog box.
-
In the Create New Data Source dialog box of ODBC Administrator, select
an appropriate driver—for example, IBM DB2 Driver—in the list box, and
click Finish to open the ODBC IBM DB2 Driver - Add dialog box.
-
In the Database alias drop-down list, select the name of the database for
your OLAP Metadata Catalog (for example, TBC_MD in the sample application).
The name of the selected database is automatically displayed in the Data
Source Name text box.
-
If you want to change the name of the data source, select the name displayed
in the Data Source Name text box, type a new name to indicate how you use
this driver, and click Add. For example, you might type the following
name to indicate that you are using the driver to connect to the first
OLAP Metadata Catalog:
OLAP Catalog #1
You would type the following name to indicate that you are connecting
to the sample application OLAP Metadata Catalog database:
TBC_MD
-
In the Description text box, enter a description that indicates how you
use this driver. For example, you might type the following words to describe
the OLAP Metadata Catalog:
My first models and metaoutlines
You might type the following words to describe the sample application
OLAP Metadata Catalog database:
Sample models and metaoutlines
The descriptions help you to identify the catalog that you want to
select when you connect to the OLAP Metadata Catalog from Hyperion Integration
Server Desktop.
-
Click OK to return to the ODBC Data Source Administrator dialog box. The
data source name you entered and the driver you mapped to it are displayed
in the System Data Sources list box on the System DSN tab.
To edit configuration information for a data source:
1. Click Configure to open the ODBC IBM DB2 - Add dialog box.
2. Correct any information you want to change.
3. Click OK twice to exit.
After You Configure a Data Source
After you configure the relational data source and OLAP Metadata Catalog,
you can connect to them from DB2 OLAP Server. You can then create, modify,
and save OLAP models and metaoutlines.
Note: The SQL Server ODBC driver may time out during a call to an SQL
Server database. Try again when the database is not busy. Increasing the
driver time-out period may avoid this problem. For more information, see
the ODBC documentation for the driver you are using.
For more information on ODBC connection problems and solutions, see
the DB2 OLAP Integration Server OLAP System Administrator’s Guide.
Migrating to OLAP Integration Server 7.1
If you are migrating to IBM DB2 OLAP Integration Server 7.1, fixpack 2
or higher, from an earlier release of the Hyperion Solutions product Hyperion
Integration Server, you should assess several system environment and data
migration issues before installing the new software. This topic explains
and recommends solutions for the following migration issues:
Read the following topics carefully to assess migration issues that may
affect your OLAP Integration Server installation. Note that the following
tasks cannot be completed until after you have installed OLAP Integration
Server:
-
On UNIX systems, you cannot update environment variables until after the
installation process has completed and the is.csh (or is.sh)
files you need have been created .
-
On all systems, you cannot migrate existing OLAP Metadata Catalogs until
after the installation process has completed and the SQL scripts you need
have been created; see Upgrading the OLAP Metadata Catalog
for more information.
Preserving Existing ODBC Driver
Starting with Fixpak 2, OLAP Integration Server 7.1 has a new architecture
that concentrates ODBC driver configuration solely on the computer that
runs the server component of OLAP Integration Server. With this new architecture,
you do not have to configure ODBC drivers for client computers that run
OLAP Integration Server Desktop or worry about upgrading ODBC drivers on
clients.
If you use the INTERSOLV (now MERANT) Version 3.10 or Version 3.11 ODBC
drivers supplied with previous versions of Hyperion Integration Server,
you might want to preserve the older drivers for compatibility with the
other applications. In general, however, you should upgrade the ODBC drivers
to the versions provided with the latest release of OLAP Integration Server.
DB2 OLAP Integration Server 7.1 Fixpak 2 supports MERANT Version 3.6
ODBC drivers; it does not operate with MERANT Version 3.10 or Version 3.11
ODBC drivers.
Install the new MERANT Version 3.6 ODBC drivers and preserve the older
drivers by installing OLAP Integration Server to a new location. Be sure
that you do not update the ODBC register settings when prompted to do so
by the installation program. You can continue to use the previous ODBC
definitions.
Choosing an Installation Directory
If you are upgrading from Hyperion Integration Server, you should install
the software to the same directory as the Hyperion product to take advantage
of the previous environment settings. However, if you want to preserve
older ODBC driver settings for other applications, you should install Hyperion
Integration Server to a different directory.
Verifying Path Variable Settings
If you are upgrading to the current release of OLAP Integration Server
from an earlier release of Hyperion Integration Server, allow the setup
program to update the environment variables for you. After installation
is complete, however, you should check that the path variables are set
properly for the system configuration:
-
If you install the OLAP Integration Server on a computer that runs DB2
OLAP Server, verify that the ARBORPATH environment variable is set to the
main directory for DB2 OLAP Server.
-
If you install the OLAP Integration Server on a computer that is not
running DB2 OLAP Server, verify that the ARBORPATH environment variable
is set as follows:
-
For Windows systems: ARBORPATH=ISHOME\esslib
-
For UNIX systems: ARBORPATH=$ISHOME
-
For all upgrade scenarios, verify that the path environment variables are
set as shown in the following table, after installation:
Operating System |
Path Variable Settings (1) |
Windows NT or
Windows 2000 (2) |
PATH=CURRENT_PATH;%ISHOME%\Bin;
%ISHOME%\odbclib;%ISHOME%\hyperlib;
%ISHOME%\esslib |
AIX (3) |
LIBPATH=$LIBPATH:$ISHOME/odbclib/$ISHOME/esslib
PATH=$PATH:$ISHOME/bin |
HP-UX (3) |
SHLIB_PATH $SHLIB_PATH:$ISHOME/ odbclib/$ISHOME/esslib
PATH=$PATH:$ISHOME/bin |
Solaris (3) |
LD_LIBRARY_PATH=
$LD_LIBRARY_PATH:$ISHOME/odbclib/$ISHOME/esslib
PATH=$PATH:$ISHOME/bin |
Table Notes:
-
%ISHOME% represents the main installation directory of OLAP Integration
Server on Windows systems. $ISHOME represents the main installation directory
of OLAP Integration Server on UNIX systems.
-
For Windows systems, check to make sure that the setup program
did not add duplicate path entries for the OLAP Integration Server directories.
-
If you are using MERANT (formerly INTERSOLV) ODBC drivers, place the MERANT
library at the beginning of the library path; for example, LIBPATH=$ISHOME/odbclib:
$LIBPATH:$ISHOME/esslib).
Migrating Existing OLAP Metadata Catalogs
If you have an existing OLAP Metadata Catalog from Hyperion's Integration
Server product, you must upgrade it before using the OLAP Metadata Catalog
with the current release of the software.
You cannot upgrade an existing OLAP Metadata Catalog until after you
have completed the installation process. The Hyperion Integration
Server setup program installs the scripts that you need for the upgrade.
CAUTION: If you have installed the sample application from Hyperion
Integration Server, you should back up your existing sample database, OLAP
Metadata Catalog, and the OLAP models and metaoutlines stored in the catalog.
You can then upgrade your existing catalog to be compatible with the current
release of the software. You cannot, however, store new sample OLAP models
and metaoutlines in your previous catalog.
To upgrade an existing OLAP Metadata Catalog, perform the following
tasks:
-
Complete the upgrade steps for the existing OLAP Metadata Catalog described
in Upgrading the OLAP Metadata Catalog.
-
When connecting to the relational database, make sure to use the same user
name and password that you used when you created the original OLAP Metadata
Catalog. This procedure adds new tables to the OLAP Metadata Catalog without
disturbing the existing information.
Upgrading the OLAP Metadata Catalog
If you have OLAP Metadata Catalogs from a earlier releases of Hyperion
Integration Server, you need to upgrade the OLAP Metadata Catalogs before
you can use them with this release of DB2 OLAP Integration Server 7.1,
Fixpack 2.
Upgrade the tables of an OLAP Metadata Catalog with the same utility
program you normally use to create tables running SQL scripts. The SQL
scripts to upgrade tables for the OLAP Metadata Catalog are in the ocscript
directory where you installed OLAP Integration Server. The upgrade scripts
are named ocdatabase_name_upgrd20.sql and are listed
the following table, along with the utility programs with which they have
been tested.
Database DBMS |
SQL Script |
Utility Program |
DB2 |
ocdb2.sql
ocdrop_db2.sql
ocdb2_upgrd20.sql |
DB2 Command Center, or:
>DB2 -tvf |
Oracle |
ocoracle.sql
ocdrop_oracle.sql
ocoracle_upgrd20.sql |
SQL*Plus |
MS SQL Server |
ocsqlsrv.sql
ocdrop_sqlsrv.sql
ocsqlsrv_upgrd20.sql |
ISQL (MS SQL Server 6.5)
Query Analyzer (MS SQL Server 7.0) |
To upgrade tables for the OLAP Metadata Catalog database, perform the
following tasks:
-
Start the utility program.
-
Connect to the database 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.
-
Run the SQL script 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 you have created the additional OLAP Metadata Catalog tables.
For example, type the following command:
SELECT * FROM OM_DESCRIPTION
or start the RDBMS and verify that the OLAP Metadata Catalog has the
new tables.
-
Close the utility program.
New Sample Applications
IBM DB2 OLAP Integration Server provides a sample database based on a fictitious
company named The Beverage Company (TBC). A new sample OLAP Metadata Catalog
(TBC_MD) contains a sample OLAP model (TBC Model) which includes attribute-enabled
columns, and a sample metaoutline (TBC Metaoutline), which includes attribute
dimensions.
Shipped with DB2 OLAP Integration Server are two Drill-Through
sample spreadsheets based on the TBC sample data. These two spreadsheets
are: essdt.xls and essdt.123. Both spreadsheets are located
in IS\Samples\Spreadsheets.
In order to use the sample HIS Drill-Through reports, essdt.xls and
essdt.123, you need to login to a machine that has both the DB2 OLAP Server
and DB2 OLAP Integration Server installed. You must run member and data
load and calculate the data for the sample OLAP Integration Server database
that you will access from Spreadsheet Add-in.
Copyright (c) 1998-2001 by International Business Machines Corporation.
All Rights Reserved.
Copyright (c) 1991-2001 by Hyperion Solutions Corporation. All Rights
Reserved.