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.
To define an OBDC Data Source for Windows:
To open the ODBC Data Source Administrator, click> > > :
Clickto open the Create New Data Source dialog box.
Selectfrom the list, then click to open the ODBC IBM DB2 Driver – Add dialog box.
In the MOSQL.field, enter
Leave thefield empty.
Clicknext to the field to open the CLI/ODBC Settings dialog box.
Ifdoes 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 thefield and the corresponding password in the field.
Select thecheck box.
A warning dialog box opens, indicating that the password is stored in an unsecured location.
Clickto close the warning dialog box.
In theand fields, enter mosql.
In thefield, 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 Server Installation Guide.
Select thecheck box.
The ODBC Data Source Administrator dialog box displays MOSQL, which represents the database alias.
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.
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.
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.