OLAP Setup and User's Guide

Loading and Configuring ODBC for the SQL Interface

If you selected the SQL interface add-on, you must ensure that your RDBMS environment is set correctly. You must also set up the environment and the ODBC drivers for the SQL interface

The installation program does not load and configure the open database connectivity (ODBC) drivers. To be able to use the SQL Interface add-on, you need to set up the ODBC for IBM DB2 manually.

When you use the SQL Interface, make sure the password for the account you use for SQL access is uppercase.

The following scenario shows how to load and configure ODBC for IBM DB2. This scenario assumes you have installed the SQL Interface.

1. In the $ARBORPATH/bin directory, create a text file called esssql.cfg that contains the following:

   [
   Description "IBM DB2 ODBC Driver"
   DriverName db2.o
   Database 0
   Userid 1
   Password 1
   SingleConnection 0
   UpperCaseConnection 0
   IsQEDriver 0
   ]
   

2. Run the inst-sql.sh file from the /home/essbase ($ARBORPATH) directory. This file links the SQL Interface to the driver library.
3. Create two files called .odbcinst.ini and .odbc.ini in the $ARBORPATH directory. Also, remember that /home/db2inst1/ equals the contents of your $INSTHOME environment variable.
4. Edit the .odbcinst.ini file and set the correct path for the driver. For example:

   [ODBC Drivers]
   IBM DB2 ODBC DRIVER=Installed
    
   [IBM DB2 ODBC DRIVER]
   Driver=/home/db2inst1/sqllib/lib/db2.o
   

5. Edit the .odbc.ini file and set the correct path for the driver and installation directory. You must put entries in this file for each databases that is listed by the DB2 List Database Directory function. For example, if you have two databases, SAMPLE and OLAPSRC, your file might look like this:

   [ODBC Data Sources]
   SAMPLE=IBM DB2 ODBC DRIVER
   OLAPSRC=IBM DB2 ODBC DRIVER
    
   [SAMPLE]
   Driver=/home/db2inst1/sqllib/lib/db2.o
   Description=Sample DB2 ODBC Database
    
   [OLAPSRC]
   Driver=/home/db2inst1/sqllib/lib/db2.o
   Description=DB2 OLAP SERVER Source Database
    
   [ODBC]
   Trace=0
   TraceFile=odbctrace.out
   InstallDir=/home/db2inst1/sqllib/odbclib
   

   If you want to access remote data sources, add them to the ODBC Data Sources list.

6. Log into your system as the DB2 UDB instance owner and take the following steps:
   1. Grant select authority for all relevant tables to the account you want to use for SQL access. For example, from the DB2 command line processor, run this command to grant authority to the arbsql account:

      GRANT SELECT ON STAFF TO arbsql
      

   2. Run the DB2 TERMINATE to flush the catalog buffers.

Follow these steps to test the SQL Interface:

1. Log into your system using the account you want to use for SQL access. Make sure you can access your tables with the DB2 command line processor.
2. From a client machine, use the Application Manager to create an application and a database.
3. Open the outline and add some dummy dimensions and members, and save the outline.
4. Open a new rules file.
5. From the File menu, select Open SQL. Verify the server, application, and database names and click OK.
6. On the Define SQL panel, the SQL Data Sources box lists each data source you have cataloged. Make sure the table name is qualified. Complete the SELECT, FROM, and WHERE fields and click OK/Retrieve.
7. On the SQL Connect panel, enter your DB2 user ID and password, and verify the Server, Application, and Database names, and click OK. If your database is on a remote machine, enter a user ID and password for that machine.