DB2 OLAP Server Installation Guide

Configuring SQL Interface on UNIX

Install Essbase SQL Interface on UNIX (AIX, HP-UX, and Solaris) from the Essbase CD-ROM after you have installed the Essbase OLAP Server. Before installing SQL Interface, ensure that your RDBMS environment is set correctly. After installing SQL Interface, set up the environment and the ODBC drivers.

The following workflow is an overview of tasks required to effectively install Essbase SQL Interface on UNIX.

Workflow for configuring SQL Interface on UNIX

To install SQL Interface on UNIX:

1. Make sure the RDBMS environment is set up correctly before installing SQL Interface. See Setting up RDBMS for the SQL Interface on UNIX.
2. Set up the ODBC driver you plan to use. Steps vary depending on platform/driver configurations. For examples, see Setting up ODBC drivers before linking them to SQL Interface.
3. Run inst-sql.shto link SQL Interface to your ODBC driver. See Linking SQL Interface to the ODBC driver on UNIX.
4. Complete post-installation tasks, including setting the library path and configuring the .odbc.ini file. See After you link SQL Interface to your ODBC Driver on UNIX.
5. To learn more about SQL Interface and how to configure your ODBC data source, see Configuring the data source and ODBC driver.

Setting up RDBMS for the SQL Interface on UNIX

You must be able to connect to the RDBMS from the Essbase server machine before SQL Interface can work.

To set the RDBMS environment variables, make sure that:

1. The RDBMS server is installed and running. You must set the environment variables required to connect to the RDBMS.

   Examples of Setting RDBMS Environment Variables (Solaris):

   For DB2 Universal Database:

   • Set DB2INSTANCE and INSTHOME.
   • Make sure that LD_LIBRARY_PATH contains the DB2 library path ($INSTHOME/sqllib/lib).
   • Make sure that the path contains the DB2 bin ($INSTHOME/sqllib/bin).

   For Informix:

   • Set INFORMIXDIR and INFORMIXSERVER.
   • Make sure that LD_LIBRARY_PATH contains the Informix library path ($INFORMIXDIR/lib).
   • Make sure that the path contains the Informix bin ($INFORMIX/bin).

   For Oracle:

   • Set ORACLE_HOME and ORACLE_SID.
   • Make sure that LD_LIBRARY_PATH contains the Oracle library path ($ORACLE_HOME/lib).
   • Make sure that the path contains the Oracle bin ($ORACLE_HOME/bin).

   For Sybase:

   • Set SYBASE and DSQUERY.
   • Make sure that LD_LIBRARY_PATH contains the Sybase library path ($SYBASE/lib).
   • Make sure that the path contains the Sybase bin ($SYBASE/bin).

   Library path variable names vary across UNIX platforms. For more information, see your RDBMS documentation.

2. The RDBMS client software is installed on the machine where the Essbase OLAP Server is (or will be) running.
3. You can connect to the RDBMS.
4. You can issue a query to the RDBMS using an RDBMS client utility.

   See the Supported ODBC drivers and relational data sources for information on tested and supported SQL Interface platforms and ODBC drivers.

   You must also set the environment variables required to run your RDBMS.

Directories and files created

The SQL Interface installation program installs files into the $ARBORPATH/bin and $ARBORPATH/dlls directories.

The SQL Interface installation program:

• Copies the.odbc.ini file into the $ARBORPATH/bin directory on your UNIX server machine. This tells Essbase which drivers are installed.

  If you accepted the default directory during installation, $ARBORPATH is /ibm/db2olap.

• Installs MERANT ODBC drivers files to an $ARBORPATH/dlls directory on your UNIX server machine.
• Installs the SQL Interface files to the $ARBORPATH/bin directory. These include:
  • On AIX: $ARBORPATH/bin
  • On HP-UX: libesssql.insolv.sl
  • On Solaris: libesssql.so.1

When the OLAP server component is installed on UNIX, it creates:

• The SQL Interface library file:
  • On AIX: libesssql.a
  • On HP-UX: libesssql.sl
  • On Solaris: libesssql.so
• A stub ODBC drivers file:
  • On AIX: libesssql.arbor.a
  • On HP-UX: libesssql.arbor.sl
  • On Solaris: libesssql.arbor.so.1

After you install SQL Interface, you need to run the shell script inst-sql.sh, which creates a symbolic link between the SQL Interface library file and the MERANT ODBC drivers file. See Linking SQL Interface to the ODBC driver on UNIX.

Setting up ODBC drivers before linking them to SQL Interface

After you install SQL Interface on AIX, HP-UX, or Solaris, you must set up your ODBC driver before you link SQL Interface to your ODBC driver by running inst-sql.sh.

To set your environment so that you can link SQL Interface to the driver you chose, complete the steps after you run setup.sh, but before you run inst-sql.sh. The steps you take depend on the driver and platform you use. This section includes examples on how to set up the drivers.

For information about setting up MERANT Oracle or Informix drivers, see the MERANT DataDirect Connect ODBC Reference. Also see Oracle or Informix documentation.

Setting up the MERANT DB2 Driver on AIX or Solaris

To set the environment so that you can link SQL Interface to the MERANT DB2 driver, complete the following steps after you run setup.sh, but before you run inst-sql.sh:

1. Log on as the instance owner, and make sure you have Read or Read/Write access to $ARBORPATH.
2. Set the DB2 environment variable, for example, INSTHOME, to point to the home directory of your DB2 instance. An instance corresponds to one or more databases. For every database you want to connect to, you must ensure that an instance corresponding to that database exists.
3. Set the environment variable DB2INSTANCE to point to the instance name, and set LIBPATH.

   Here is a Bourne shell example:

   DB2INSTANCE=inst1
   LIBPATH=/lib:/usr/lib:/home/essbase/bin:$INSTHOME/sqllib/lib:
   export INSTHOME DB2INSTANCE LIBPATH
   

   where INSTHOME is the environment variable that you defined in step 2, and inst1 is the instance name.

For more information about setting environment variables for the MERANT DB2 driver, see the MERANT DataDirect Connect ODBC Reference.

Setting up the IBM DB2 Driver on AIX

To set the environment so that you can link SQL Interface to the IBM DB2 driver, complete the following steps after you run setup.sh, but before you run inst-sql.sh:

1. Make sure the relational client software for DB2 is installed on the same AIX machine that you are using for SQL Interface.
2. Create a configuration file, esssql.cfg. An example is provided below:

   [
   Description "IBM DB2 Call Level Interface"
   DriverName db2.o
   Database 1
   Password 1
   UserId 1
   SingleConnection 0
   UpperCaseConnection 0
   IsQEDriver 0
   ]
   

3. Set the environment variable INSTHOME to point to the home directory of your DB2 instance.
4. Define DB2 instance names for SQL Interface. An instance corresponds to one or more databases. For every database you want to connect to, make sure that an instance corresponding to that database exists.
5. Log on as the DB2 OLAP Server system administrator account. This is the account used to install and run DB2 OLAP Server. You should have Read or Read/Write access to the driver manager library, libodbc.a, in INSTHOME/sqllib/odbclib/lib.
6. Set the environment variables DB2INSTANCE and LIBPATHas in the following Bourne shell example:

   DB2INSTANCE=inst1
   LIBPATH=/lib:/usr/lib:/home/essbase/bin:$INSTHOME/sqllib/lib:
   export INSTHOME DB2INSTANCE LIBPATH
   

   where INSTHOME is the environment variable that you defined in 3, and inst1 is the instance name.

Linking SQL Interface to the ODBC driver on UNIX

After you have completed the following actions, you are ready to link SQL Interface to your ODBC driver by running inst-sql.sh:

• Installed and configured your RDBMS.
• Installed SQL Interface.
• Set up the ODBC driver you selected during installation

Running inst-sql.sh ensures that server processes can access the software necessary for SQL Interface. The installation program, which is launched by setup.sh, places inst-sql.sh in the $ARBORPATH directory. The default is /home/hyperion/essbase.

The inst-sql.sh file uses the odbc.ini, odbcinst.ini, and template (extension .tmpl) files in the $ARBORPATH/setup directory to create a set of files that point DB2 OLAP Server to the correct driver product.

The script handles the following tasks for you:

• Ensures root privileges are not accidentally granted to a user-level script.
• Checks for the availability of the OLAP server.
• Checks for the availability to the OLAP server of supported database systems.
• Provides default environment extensions as shell files in the $ARBORPATH/bin directory.
• Provides initialization files in the $ARBORPATH/bin directory for access by the driver software.
• Creates and dynamically compiles an extra library if needed.
• Generates the shell scripts odbc.csh for the C shell, and odbc.sh for the Bourne or Korn shell. These files contain commands that update the library path to specify the correct environment variables for Essbase. After you run inst-sql.sh to link SQL Interface to the ODBC driver, you can optionally use these scripts to update your library path, or you can do it manually. See Setting the library path on UNIX.

To run inst-sql.sh:

1. Using the system administrator account (this is the account used to install and run DB2 OLAP Server), enter the following command:

   sh inst-sql.sh
   

2. Supply information when the script prompts you for it.

   When the script finishes, the command prompt is displayed.

3. Type the following command:

   exit
   

After you link SQL Interface to your ODBC Driver on UNIX

Now that you have linked SQL Interface to your ODBC driver, you are ready to complete the following post-installation tasks:

• Set the library path, either manually or by using the odbc.sh or odbc.csh shell scripts. See Setting the library path on UNIX.
• If you are using the MERANT DB2 driver, bind files in the /ESSBASE/bin directory. See Binding files for the MERANT DB2 driver on AIX or Solaris.
• Complete the configuration of your ODBC drivers by editing the .odbc.ini file. Some examples are provided in Editing .odbc.ini driver files.
• Configure the ODBC data source and (if applicable) configure your non-MERANT ODBC driver.

Setting the library path on UNIX

On UNIX, you must add the $ARBORPATH/dlls directory to the library path before you can use SQL Interface. If your installation of SQL Interface is an upgrade to an existing installation, you may not have to reset the library path.

Alternatively, you can use shell scripts created by inst-sql.sh to update your library path. See Using shell scripts to set the library path (optional) for more information.

• On Solaris, add the $ARBORPATH/dlls directory to the LD_LIBRARY_PATH variable:

  For example, if you are using the C shell, the.cshrc file might look like this:

  setenv ARBORPATH /ibm/db2olap
  setenv LD_LIBRARY_PATH /usr/lib:$ARBORPATH/dlls
  

• On AIX, add the $ARBORPATH/dlls directory to the LIBPATH variable:

  For example, if you are using the C shell, the .cshrc file might look like this:

  setenv ARBORPATH /ibm/db2olap
  setenv LIBPATH /usr/lib:$ARBORPATH/dlls
  

• On HP-UX, add the $ARBORPATH/dlls directory to the SHLIB_PATH variable:

  For example, if you are using the C shell, the .cshrc file might look like this:

  setenv ARBORPATH /ibm/db2olap
  setenv SHLIB_PATH /usr/lib:$ARBORPATH/dlls
  

  The .profile file, if you are using the Bourne or Korn shell, might look like this:

  ARBORPATH=/home/ibm/db2olap
  export ARBORPATH
  SHLIB_PATH=$SHLIB_PATH:$ARBORPATH/dlls
  export SHLIB_PATH
  PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$ARBORPATH/dlls:
  /usr/bin/X11:/sbin:.export PATH
  

  HP-UX uses SHLIB__PATH to search for shared library files at runtime and LPATH to link to shared library files while compiling. For more information, see your UNIX documentation.

Using shell scripts to set the library path (optional)

The script that links SQL Interface to your ODBC driver (inst-sql.sh) also creates shell scripts which you can use to automatically update the library path for Essbase. The inst-sql.sh script generates the following files and places them in the $ARBORPATH/setup directory:

• odbc.csh for the C shell
• odbc.sh for the Bourne or Korn shell.

These files contain commands that specify the correct environment variables for Essbase. $ARBORPATH/dlls is added to the library path, and ODBCINI is set to $ARBORPATH/bin/.odbc.ini.

To use these scripts so that Essbase environment variables are available to your shell environment every time you log on, complete one of the following tasks after you run inst-sql.sh:

• In the C shell, use the source command against odbc.csh in the .login or .cshrc file. For example, edit the .login or .cshrc file to include this line:

  % source $ARBORPATH/setup/odbc.csh
  

• In the Bourne or Korn shell, use the . (dot) command against odbc.sh in the .profilefile. For example, edit the .profile file to include this line:

  % . $ARBORPATH/setup/odbc.sh
  

Binding files for the MERANT DB2 driver on AIX or Solaris

To bind files so that the DB2 driver can understand the MERANT commands on AIX or Solaris, complete the following steps after you run inst-sql.sh:

1. Change to the $ARBORPATH/bindirectory.

   For example:

   cd /home/ibm/db2olap/bin
   

2. Start the DB2 command-line interface.

   For example:

   db2
   

3. Connect to the DB2 database.

   For example:

   CONNECT TO DATABASENAME
   

4. Bind all files, or at least the .bnd files, in your $ARBORPATH/bin directory.

   For example:

   BIND iscsso.bnd
   

   File names may change for new driver versions. For the files you need to bind, list all files with the .bnd extension in the $ARBORPATH/bin directory.

   It is recommended that you bind all the files in the bin directory.

5. Grant user privileges for accessing data.

For more information about binding and granting privileges, see the MERANT DataDirect Connect ODBC Reference.

Editing .odbc.ini driver files

To complete your ODBC driver setup after installing Essbase SQL Interface, you need to modify .odbc.ini to include the correct driver and data source names. See the following examples for the IBM DB2 driver on AIX, and for the MERANT DB2 driver on Solaris.

Editing .odbc.ini for the IBM DB2 driver on AIX

To set up your .odbc.ini file for DB2 Universal Database on AIX, complete the following tasks after you run inst-sql.sh.

On AIX, inst-sql.sh prompts you to choose between the MERANT DB2 driver and the IBM Call-Level Interface (CLI) for DB2 driver.inst-sql.sh links SQL Interface to different libraries, depending on which DB2 driver you choose. To switch DB2 drivers sets after you run inst-sql.sh, reinstall SQL Interface.

In $ARBORPATH (the directory where DB2 OLAP Server is installed), in the /bin directory, modify .odbc.ini to include the correct driver and data source name.

For example:

[ODBC Data Sources]
SAMPLEP=IBM DB2 ODBC DRIVER
 
[SAMPLE]
Driver=/home/db2inst/sqllib/lib/db2.o
Database=dbname
 
[ODBC]
Trace=0
TraceFile=odbctrace.out
InstallDir=/home/db2inst/sqllib/odbclib

In these examples, /home/db2instis the $INSTHOMEdirectory. You cannot use environment variables in .inifiles. See the documentation for DB2 Universal Database for more information.

Editing .odbc.ini for the MERANT Oracle driver on Solaris

To set up your .odbc.ini file for Oracle 8 on Solaris, complete the following tasks after you run inst-sql.sh:

In $ARBORPATH (the directory where Essbase is installed), modify .odbc.ini to include the correct driver and data source name.

For example:

[ODBC Data Sources]
Oracle8=MERANT 4.0 Oracle 8 Driver
[Oracle8]
Driver=/home/ibm/db2olap/dlls/ARor815.so
Description=Oracle8
ServerName=name

where name is the appropriate Oracle server name.

Configuring the data source and ODBC driver

You have already completed the following tasks:

• Installed SQL Interface.
• Set up the ODBC driver for your platform/driver configuration.
• Linked SQL Interface to the ODBC driver using inst-sql.sh.
• Set the library path for Essbase.
• Completed ODBC driver setup by editing the odbc.ini file.
• Bound files in ESSBASE/bin, if you are using the DB2 driver.

You are now ready to configure the data source. You may also need to configure your ODBC driver.