2.2 Configuring the ODBC Driver

An ODBC driver is required to connect SQL Views on the Operations Center server to any external reporting tool that does not have native support for JDBC. This includes most Windows reporting tools (such as Business Objects Crystal Reports, Microsoft Excel, and so on).

An Open Database Connectivity (ODBC) driver is a standard database access method. It is used to access any data from any application, regardless of the database management system (DBMS) that handles the data. It translates application data queries into commands understood by the DBMS.

At the time of the release of Operations Center 5.6, no free 64-bit Windows ODBC drivers were compatible with SQL Views. If your reporting solution requires an ODBC connection to SQL Views, you can evaluate the IBM JDBC/ODBC driver, but there are reliability issues with the driver for SQL Views on 32-bit Windows operating systems and it does not work with 64-bit Windows operating systems. Some third parties have drivers that seem to work available for a fee.

The ODBC client driver for DB2 (Version 9 or lower) from IBM works with SQL Views. The ODBC driver for DB2 from IBM can be downloaded from the IBM Web site.

The ODBC client driver from IBM does not work with Apache Derby. To use an ODBC driver with Apache Derby, you must purchase a proprietary driver from another vendor. You can use a JDBC driver with Apache Derby.

After the driver has been installed, you must define an ODBC data source in either Windows or UNIX.

2.2.1 Defining an ODBC Data Source for Windows

To define an OBDC Data Source for Windows:

To open the ODBC Data Source Administrator, click Start > Control Panel > Administrative Tools > ODBC Data Source Administrator:
Click Add to open the Create New Data Source dialog box.
Select IBM DB2 ODBC Driver from the list, then click Finish to open the ODBC IBM DB2 Driver – Add dialog box.
In the Data Source Name field, enter MOSQL.

Leave the Database Alias field empty.
Click Add next to the Database Alias field to open the CLI/ODBC Settings dialog box.

If Add does not display, then you could have a different version of the DB2 driver. These instructions are based on version 8.2.4. Earlier versions might require configuring SQL using the IBM DB2 Configuration Assistant or the command line interface.
Enter the user name for a user account with admin privileges in the User ID field and the corresponding password in the Password field.
Select the Save Password check box.

A warning dialog box opens, indicating that the password is stored in an unsecured location.
Click OK to close the warning dialog box.
Click the TCP/IP tab.
In the Database Name and Database Alias fields, enter mosql.
In the Host name field, specify the host name of the Operations Center server.

If configuring remote client access, specify the client machine name instead of localhost.
Enter the port number for SQL Views.

The default value is 1560. This port number is set in the Configuration Manager. For more information, see the Operations Center 5.6 Server Installation Guide.
Click the Security Options tab:
Select the Specify the Security Options check box.
Select the Server Authentication (SERVER) option.
Click OK.

The ODBC Data Source Administrator dialog box displays MOSQL, which represents the database alias.

2.2.2 Setting Up an ODBC Data Source for UNIX

To set up the database source on UNIX, use the DB2 command prompt.

The general set up process involves setting up a database and node in the DB2 database catalog. This involves running the DB2 command prompt and setting up the proper aliases in the DB2 driver database. To catalog the SQL database, run the DB2 command prompt.

The following commands use the database name mosql and the node name fnode:

    db2 => catalog tcpip node fnode remote localhost server 1527
    DB20000I  The CATALOG TCPIP NODE command completed successfully.
    DB21056W  Directory changes may not be effective until the directory cache is refreshed.
    
    db2 => catalog db mosql at node fnode authentication server
    DB20000I  The CATALOG DATABASE command completed successfully.
    DB21056W  Directory changes may not be effective until the directory cache is refreshed.
    
    db2 => connect to mosql user admin using formula
    Database Connection Information
    Database server        = Server_Name
    SQL authorization ID   = FNODE
    Local database alias   = MOSQL

If you are using other names for the database and node, change the commands accordingly.

2.2.3 Reconfiguring the Command Line Driver

After reinstalling Operations Center, it is sometimes necessary to reconfigure the command line driver. Use the following commands to uncatalog the database and node in DB2. The examples use a database name mosql and a node name fnode. If necessary, replace these with the names used in your system.

To reconfigure the driver:

Open the ODBC Data Source Administrator and delete the SQL Views ODBC configuration.

Go to the DB2 command prompt and enter the Uncatalog db mosql command:

db2 => uncatalog db mosql 
DB20000I  The UNCATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is refreshed.

Enter the Uncatalog fnode command:

db2 => uncatalog node fnode
DB20000I  The UNCATALOG NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is refreshed.

Use the ODBC Data Source Administrator to reconfigure SQL Views.

2.2.4 Defining Character Encoding for Windows Clients in International Environments

Client programs, such as MS Excel, might error out because of character encoding issues while accessing SQL Views on computers that are configured for non-western regional languages.

To force the ODBC drivers to use UTF character encoding:

Set the environment variable DB2CODEPAGE to 1208 on the client side machine.

This forces the IBM DB2 ODBC driver to use UTF character encoding.
Restart the client program interfacing with SQL Views (such as MS-Excel) after setting the environment variable.
If restarting the client program does not work, reboot the machine for the client programs to pick up the environment variable change.