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.
The ODBC client driver for DB2 from IBM works with SQL Views. The ODBC driver for DB2 from IBM can be downloaded from the IBM Web site, by installing the DB2 Run-Time Client listed as in the table.
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
> > > :Click
to open the Create New Data Source dialog box.Select
from the list, then click to open the ODBC IBM DB2 Driver – Add dialog box.In the MOSQL.
field, enterLeave the
field empty.Click
next to the field to open the CLI/ODBC Settings dialog box.If
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
field and the corresponding password in the field.Select the
check box.A warning dialog box opens, indicating that the password is stored in an unsecured location.
Click
to close the warning dialog box.Click the
tab.In the
and fields, enter mosql.In the
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.0 Server Installation Guide.
Click the
tab:Select the
check box.Select the
option.Click
.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. To read an article relevant to connecting ODBC to Apache Derby (the foundation for SQL Views) go to:
http://www‑128.ibm.com/developerworks/db2/library/techarticle/dm-0409cline2/index.html
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 = Apache Derby CSS10000 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.