Installing Lotus OEM ODBC on Unix

Learn how to set up the Lotus OEM ODBC on Unix. This article provides you with everything you need (including sample code) to install, configure, and troubleshoot your ODBC driver.

Tracey Hadwen, SQA Engineer, IBM, Software Group

Tracey Hadwen joined the IBM Lotus Software Group in Portsmouth, NH in 1999, working on Enterprise Integration products. She began working with DataDirect ODBC in late 2002.



26 April 2004

ODBC (Open Database Connectivity) is a standard for accessing database content. ODBC allows you to access data from any application, regardless of the database management system used by that application. ODBC does this through drivers. These drivers form a layer between the application and its database management system. The drivers accept data queries issued by the application and translate them into commands that the database management system can understand.

DataDirect, an industry leader in data connectivity, offers an ODBC driver called DataDirect Connect for ODBC. The Lotus version of this driver is called the Lotus OEM ODBC. IBM Passport Advantage customers can download the Lotus OEM ODBC driver for free from the Passport Advantage Web site. This driver can be used with Lotus Enterprise Integrator (LEI), DECS 6.5.x, and LSXLC and is available for Solaris 2.8; AIX 4.3.3, 5.1, 5.2; Linux Red Hat 7.2; and United Linux.

This article explains how to install, configure, and troubleshoot the Lotus OEM ODBC driver for Unix. We begin by discussing the minimum configuration settings required to get you started. We then describe how to create a connection document in LEI and how to create a sample LSX program to create a table using these ODBC drivers. (You can download this program from the Sandbox.) This article assumes that you're an experienced Domino programmer and are familiar with ODBC.

Installing the Lotus OEM ODBC driver

To use the Lotus OEM ODBC, you can install ODBC in addition to LEI's native database connectors, such as Oracle and Sybase. To do this:

  1. Create an ODBC directory (for example /opt/odbc).
  2. Download the Lotus OEM ODBC setup executable in that directory. The name of this executable depends on the operating system: setupSolaris, setupAix, or setupLinux.
  3. At your display terminal, type the command export DISPLAY=127.0.0.1:0.0. This allows the graphical display to display back to your workstation.
  4. At your operating system, run the setup executable for your Unix platform. This displays the InstallWizard for Lotus OEM ODBC Welcome screen:
    Figure 1. InstallWizard for Lotus OEM ODBC Welcome screen
    InstallWizard for Lotus OEM ODBC Welcome screen
  5. Click Next. The software license screen appears. Click the Accept terms option and click Next to continue.
  6. Specify the destination directory. We recommend /opt/odbc (to conform with the industry practice of installing third party applications in the /opt directory), although this is not mandatory. Then click Next.
  7. A summary screen showing data related to the installation appears:
    Figure 2. Summary screen
    Summary screen
    Read the summary screen and then click Next to being the installation.
  8. When installation is complete, the following screen appears:
    Figure 3. Installation complete screen
    Installation complete screen
    Click Finish to exit.

The Lotus OEM ODBC is now installed. Note that to use this driver, you don't need the native database client (for instance, Oracle or Sybase) installed on your computer.


Configuring the odbc.ini file

Next, edit the odbc.ini file on Unix so that your system knows where to get the "backends." (Oracle, Sybase, SQL Server, DB2, and Informix are all considered backends.) The edits you make to odbc.ini depend on which database management system you are using: DB2, Informix, Oracle, SQL. or Sybase.

Configuring ODBC for DB2

To edit the odbc.ini file for DB2, open the file and type in the following code just above the line [dbase]:

[DB2 Wire Protocol]
Driver=/opt/odbc/lib/LOdb219.so
Description=DataDirect 4.20 DB2 Wire Protocol Driver
AddStringToCreateTable=
AlternateID=
Collection=OS/390 and AS/400 (leave blank for DB2 UDB)
Database=DB2 UDB (leave blank for OS/390 and AS/400)
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=DB2 server host
IsolationLevel=CURSOR_STABILITY
Location=OS/390 and AS/400 (leave blank for DB2 UDB)
LogonID=
Password=
Package=DB2 package name
PackageOwner=
TcpPort=DB2 server port
WithHold=1

The following table explains what each line of code means and what additional changes (if any) you need to make to them:

LineComments
[DB2 Wire Protocol]Replace the name in brackets with your Data Source Name. (The following lines also apply for other Data Source Names.)
Driver=/opt/odbc/lib/LOdb219.soThis identifies the Lotus OEM ODBC driver. The filename changes with each release.
Description=DataDirect 4.20 DB2 Wire Protocol DriverThis is the default description of the driver; there is no need to change this.
AddStringToCreateTable=This field is chiefly for users who need to add an "in database" clause. Do not change this setting.
AlternateID=This field allows the default qualifier for unqualified object names in SQL statements. Do not change this setting.
Collection=OS/390 and AS/400 (leave blank for DB2 UDB)This field comes in the default odbc.ini file, and it should be removed.
Database=DB280Enter the DB2 database name.
DynamicSections=100This indicates the number of statements that the DB2 Wire Protocol driver package can prepare for a single user. Do not change this setting.
GrantAuthid=PUBLICThe default value, PUBLIC, identifies to whom execute privileges are granted. Do not change this setting.
GrantExecute=1The default value 1 grants execute privileges. Do not change this setting.
IpAddress=othelloEnter your IP address.
IsolationLevel=CURSOR_STABILITYThis enables other processes to change a row that your application has read if the cursor is not on the row that you want to change. This prohibits other processes from changing records that your application has changed until your program commits or terminates. This also prohibits your program from reading a modified record that has not been committed by another process. Do not change this setting.
Location=OS/390 and AS/400 (leave blank for DB2 UDB)This field comes in the default odbc.ini file, and it should be removed.
LogonID=uidEnter the user ID for your LogonID.
Password=pwdEnter the password for the LogonID.
Package=DDODBCEnter the name of the package you are binding.
PackageOwner=This indicates the authid assigned to the package. Do not change this setting.
TcpPort=50000Enter the TCP port number.
WithHold=1When set to 1, the cursor behavior is set to Preserve. Do not change this setting.

Configuring ODBC for Informix

To edit the odbc.ini file for Informix, replace any lines of code the file may contain with the following:

[InformixDSN]
Driver=/opt/odbc/lib/LOifcl19.so
Description=DataDirect 4.20 Informix Wire Protocol
ApplicationUsingThreads=1
CancelDetectInterval=0
Database=testdb
HostName=127.0.0.2
LogonID=uid
Password=pwd
PortNumber=1526
ServerName=informix_930
TrimBlankFromIndexName=1

The following table describes these lines and shows which ones need further editing:

LineComments
[InformixDSN]Replace the name in brackets with your Data Source Name.
Driver=/opt/odbc/lib/LOifcl19.soThis identifies the Lotus OEM ODBC driver. This filename changes with each release.
Description=DataDirect 4.20 Informix Wire ProtocolThis is the description of the driver; leave as is.
ApplicationUsingThreads=1A setting of 1 means that the driver works with multithreaded applications. Do not change this setting.
CancelDetectInterval=0This determines how often (in seconds) the driver checks whether or not a query has been canceled using SQLCancel. Do not change this setting.
Database=testdbEnter the database name.
HostName=127.0.0.2Enter the host name.
LogonID=uidEnter the user ID for your LogonID.
Password=pwdEnter the password for the LogonID.
PortNumber=1526Enter the port number.
ServerName=informix_930Enter the server name.
TrimBlankFromIndexName=1When set to 1, the driver trims leading space. Do not change this setting.

Configuring ODBC for Oracle

To modify odbc.ini for Oracle, find the [Oracle Wire Protocol] paragraph. Below that paragraph, add the following code:

[ODBCToOracle9]
Driver=/opt/odbc/lib/LOora19.so
Description=DataDirect 4.20 Oracle Wire Protocol
ApplicationUsingThreads=1
ArraySize=60000
CachedCursorLimit=32
CachedDescLimit=0
CatalogIncludesSynonyms=1
CatalogOptions=0
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=0
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
HostName=athena
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=uid
Password=pwd
PortNumber=1521
ProcedureRetResults=0
SID=athena
UseCurrentSchema=1

This table explains each line of code and tells whether or not you need to modify it:

LineComments
[ODBCToOracle9]Replace the name in brackets with your Data Source Name.
Driver=/opt/odbc/lib/LOora19.soThis identifies the Lotus OEM ODBC driver. This filename changes with each release.
Description=DataDirect 4.20 Oracle Wire ProtocolThis is the description of the driver; leave as is.
ApplicationUsingThreads=1A setting of 1 means the driver works with multithreaded applications. Do not change this setting.
ArraySize=60000This is the number of bytes the driver uses for fetching multiple rows. Do not change this setting.
CachedCursorLimit=32This number corresponds to the number of Oracle Cursor Identifiers that the driver stores in cache. Do not change this setting.
CachedDescLimit=0This number represents the number of descriptions that the driver saves for SELECT statements. Do not change this setting.
CatalogIncludesSynonyms=1A setting of 1 results in the inclusion of synonyms in calls to SQLProcedures, SQLStatistics, and SQLProcedureColumns. Do not change this setting.
CatalogOptions=0A setting of 1 means a reduction in performance of your queries; 0 returns SQL_NULL_DATA for the result columns COLUMN_DEF and REMARKS.
DefaultLongDataBuffLen=1024This integer specifies the maximum length of data fetched from LONG/LOB columns. Do not change this setting.
DescribeAtPrepare=0When set to 1, this allows the driver to describe the SQL statement at prepare time.
EnableDescribeParam=0Set this to 1 when using Microsoft Remote Data Objects (RDO) to access data.
EnableNcharSupport=0When set to 1, the char types (char, varchar, varchar2, long, clob) are described as SQL_CHAR, SQL_VARCHAR, and SQL_LONGVARCHAR regardless of the character set on the Oracle server.
EnableScrollableCursors=1When set to 1, this allows scrollable cursors for the data source. Do not change this setting.
EnableStaticCursorsForLongData=0A setting of 1 allows the driver to support long columns when using a static cursor. This impacts performance, however, so we recommend leaving this set to 0.
EnableTimestampWithTimeZone=0A setting of 1 exposes timestamps with time zones to the application.
HostName=athenaEnter the host name.
LocalTimeZoneOffset=When left blank, the driver determines local time zone information from the operating system. Do not change this setting.
LockTimeOut=-1A value of -1 means Oracle waits indefinitely for a lock to be freed before raising an error for Select... For Update statement. Do not change this setting.
LogonID=uidEnter the user ID for your LogonID.
Password=pwdEnter the password for the LogonID.
PortNumber=1521Enter the port number.
ProcedureRetResults=0A value of 0 instructs the driver to not return result sets from stored procedure functions. Do not change this setting.
SID=athenaEnter the SID of the Oracle database.
UseCurrentSchema=1When set to 1, the call for SQLProcedures is most effective, but only procedures owned by the user are returned. Do not change this setting.

Configuring ODBC for the SQL server

To modify odbc.ini for SQL, add the following lines below the [SQL Server Wire Protocol] paragraph:

[ODBCToSQLServer7]
Driver=/opt/odbc/lib/LOmsss19.so
Description=DataDirect 4.20 SQL Server Wire Protocol
Address=127.0.0.3,1433
AnsiNPW=Yes
Database=testdb
LogonID=uid
Password=pwd
QuotedId=No

The following table lists the code, along with any further edits you need to make:

LineComments
[ODBCToSQLServer7]Replace the name in brackets with your Data Source Name.
Driver=/opt/odbc/lib/LOmsss19.soThis identifies the Lotus OEM ODBC driver. This changes from one release to the next.
Description=DataDirect 4.20 SQL Server Wire ProtocolThis is the description of the driver; leave as is.
Address=127.0.0.3,1433Enter the address, followed by port number.
AnsiNPW=YesWhen field is set to yes, ANSI defined behaviors are uncovered. Do not change this setting.
Database=testdbEnter the database name.
LogonID=uidEnter the user ID for your LogonID.
Password=pwdEnter the password for the LogonID.
QuotedId=NoWhen field is set to No, Quoted Identifiers are turned off for the connection, and SQL Servers use the legacy Transact SQL rules concerning the use of quotation marks in SQL statements. Do not change this setting.

Configuring ODBC for Sybase

To edit the odbc.ini file for Sybase, locate the [Sybase Wire Protocol] paragraph and below it, add the following code.

[ODBCToSybase12]
Driver=/opt/odbc/lib/LOase19.so
Description=DataDirect 4.20 Sybase Wire Protocol
ApplicationName=
ApplicationUsingThreads=1
ArraySize=50
Charset=
CursorCacheSize=1
Database=testdb
DefaultLongDataBuffLen=1024
EnableDescribeParam=0
EnableQuotedIdentifiers=0
InitializationString=
Language=
LogonID=uid
NetworkAddress=mark,5000
OptimizePrepare=1
PacketSize=0
Password=pwd
RaiseErrorPositionBehavior=0
SelectMethod=0
WorkStationID=

This table describes each line of code and lists edits you need to make:

LineComments
[ODBCToSybase12]Replace the name in brackets with your Data Source Name.
Driver=/opt/odbc/lib/LOase19.soThis identifies the Lotus OEM ODBC driver. This name changes from release to release.
Description=DataDirect 4.20 Sybase Wire ProtocolThis is the description of the driver; leave as is.
ApplicationName=Enter the name used to identify your application.
ApplicationUsingThreads=1When field is set to 1, the driver is thread-safe. Do not change this setting.
ArraySize=50The setting 50 identifies the number of rows the driver gets from the server for a fetch. Do not change this setting.
Charset=This setting defaults to the character set on the Sybase server. Do not change this setting.
CursorCacheSize=1This is the number of connections that the connection cache can hold. Do not change this setting.
Database=testdbEnter the name of the Sybase database.
DefaultLongDataBuffLen=1024In 1024-byte multiples, this field is the maximum length of data fetched from a text or image column. Do not change this setting.
EnableDescribeParam=0When set to 0, the SQLDescribeParam is disabled. Do not change this setting.
EnableQuotedIdentifiers=0When set to 0, quoted identifiers are disabled. Do not change this setting.
InitializationString=This aids the running of Sybase commands at connect time. Multiple commands must be separated with semicolons.
Language=The default language is English.
LogonID=uidEnter the user ID for your LogonID.
NetworkAddress=mark,5000Enter the network address, followed by the port number.
OptimizePrepare=1When field is set to 1, the driver creates stored procedures only if the statement contains parameters. Do not change this setting.
PacketSize=0When this field is set to 0, the driver uses the default packet size as specified in the Sybase server configuration. Do not change this setting.
Password=pwdEnter the password for the LogonID.
RaiseErrorPositionBehavior=0When this field is set to 0, raiserror is handled separately from encompassed statements. The error is returned when raiserror is processed by SQLExecute, SQLExecDirect, or SQLMoreResults. The result set is empty. Do not change this setting.
SelectMethod=0When this field is set to 0, database cursors are used. In some cases, performance degradation can occur. Do not change this setting.
WorkStationID=This is the workstation ID used by the client.

Troubleshooting the Lotus OEM ODBC driver

Now that you have the ODBC driver installed and configured, you should run a test on it to see whether or not everything is working properly. To do this, run the dctest utility program. This utility verifies connectivity from your machine to the selected server. To run dctest, follow these steps.

  1. At your operating system, type dctest.
  2. When prompted, enter the number of the test you want to run (these choices depend on your operating system; yours may offer more or fewer options than are listed here):
    0 - Exit this program
    1 - Lotus Notes
    2 - Oracle Server
    3 - ODBC
    4 - Sybase Server
    6 - DB/2
    8 - Oracle8 Server
  3. Enter a valid ODBC data source, user name, and password. For example:
    Data Source: ODBCToOracle9
    User ID: qeuser
    Password: qepw
  4. After you enter this information and press Enter, you are asked whether or not you want to see detail driver information. Select Y or N.
  5. The dctest program now attempts to connect to the data source. If the connection is successful, a message appears informing you of this. You are then asked whether or not you want to repeat the test.

After dctest indicates you have connected successfully, you can begin to work within LEI and DECS.


Creating an ODBC connection document in LEI

You should now create a connection document that browses your selected Data Source Name. This connection document can include a table, view, or procedure. After you complete the connection document, you can begin to create LEI activities. Here is an example of an ODBC Oracle connection document:

Figure 4. Oracle connection document
Oracle connection document

To create a connection document, enter the following:

  • Name of the connection document
  • Name of the ODBC Data Source to be used
  • User name of the ODBC Data Source
  • Password of the ODBC Data Source

These are the minimum settings required to use the Lotus OEM ODBC driver.


Creating an ODBC LSX sample program

The following is an ODBC LSX example program that creates a table within Oracle. This program demonstrates the connection property settings that you can optionally apply to other ODBC LSX programs. You can download this sample program from the Sandbox.

The program begins as follows:

Option Public 
Uselsx "*lsxlc"
Sub Initialize

This line creates a new session:

Dim session As New LCSession

And this line defines the connection type:

Dim src As New LCConnection ("odbc2")

Next, the program defines variables:

 Dim fldLstRecord As New LCFieldList
 Dim fld As LCField

These lines define the field types for the columns in the table:

  Call FldLstRecord.Append ("F_INTEGER", LCTYPE_INT)
  Set fld = FldLstRecord.Append ("F_TEXT", LCTYPE_TEXT)
  Call fld.SetFormatStream (0, 64, LCSTREAMFMT_LMBCS)
  Call FldLstRecord.Append ("F_CURRENCY", LCTYPE_CURRENCY)
  Call FldLstRecord.Append ("F_BINARY", LCTYPE_BINARY) 
  Call FldLstRecord.Append ("F_DATETIME", LCTYPE_DATETIME)
  'Call FldLstRecord.Append ("F_NUMERIC", LCTYPE_NUMERIC)
  Call FldLstRecord.Append ("F_FLOAT", LCTYPE_FLOAT)

The program then sets the properties to connect to Oracle:

 src.server = "ODBCToOracle9" 
 src.userid = "qetest"
 src.password = "qepw"

Now the table is named:

  src.Metadata = "teh_example"
  src.Connect

This creates the table based on the field types for the columns (metadata property).

The program concludes with some error handling. First, it checks to determine whether or not the table has already been created:

  On Error LCFAIL_DUPLICATE Goto tableexists
  Call src.Create (LCOBJECT_METADATA, fldLstRecord)
  Messagebox "The '" & src.Metadata & "' table was created."
 End
 src.disconnect

If the table already exists, the program ends with a message informing the user of this:

tableexists: 
  Messagebox "The '" & src.Metadata & "' table exists." & Chr(13) & _ 
  "Run the Drop Method Script, and then rerun the Create Method script again.", 16, 
    "Metadata Already Exists!" 
 End 
End Sub

Conclusion

At this point, your Lotus OEM ODBC driver for Unix should be installed, configured, and running properly. And using our sample program as a guide, you are now ready to create LEI applications that use the ODBC driver to access information from a variety of data sources.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into IBM collaboration and social software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Lotus
ArticleID=12892
ArticleTitle=Installing Lotus OEM ODBC on Unix
publish-date=04262004