Configuring odbc.ini for IBM i Users

Although Microsoft Windows users do not use an odbc.ini file, IBM i users do. As well, with multi-versioned HCL Domino, more than one odbc.ini file exists. To correctly configure an ODBC data source, you must know which odbc.ini file to edit. Each version of Domino will have its own copy of the ODBC drivers and odbc.ini file. The location of the drivers and the odbc.ini file depends on which version of Domino the HEI server is running. The odbc.ini file is stored in the EITODBC directory under the UserData directory for the version of Domino running on that server. Every version of Domino that is installed on an IBM i machine has its own UserData directory located at the following adress, where XXX corresponds to the version of Domino that uses that directory.

/QIBM/UserData/LOTUS/DOMINOXXX

For example, the odbc.ini for an HCL Enterprise Integrator (HEI) server running Domino 8.5.x is located at the following address: 

/QIBM/UserData/LOTUS/DOMINO85x/EITODBC/odbc.ini

Editing the odbc.ini file for IBM i Users

To edit the odbc.ini file, use the EDTF command specifying the path to the odbc.ini file. For example, to edit the odbc.ini file of an LEI server running Domino 8.5.x enter the following command at the command line:

EDTF '/QIBM/UserData/LOTUS/DOMINO85x/EITODBC/odbc.ini'
Note: Do not use a Windows-based text editor to edit the odbc.ini file. Lines in the odbc.ini file must end with a line feed (LF) character. Many text editors often end lines with a carriage return and line feed (CRLF), which will yield errors when attempting to use the odbc.ini file. If an editor that ends lines with CRLF has already been used, you can remove the CR characters from the odbc.ini using the instructions in the section entitled "Removing CR in the odbc.ini File" below.
Note: All /QIBM/ProdData/LOTUS/DOMINOXXX/... paths in the odbc.ini are relative to the version of Domino that the server is running. These paths change dependent on the version of Domino the Domino server is running. For example, for a Domino server running Domino 8.5, these paths would appear as shown below:
/QIBM/UserData/LOTUS/DOMINO850/...

A sample odbc.ini file that would be used on a Domino 8.5.x server is shown below for use when connecting to either the Sybase Wire Protocol or Oracle Wire Protocol using an IBM i system. Instances of the term "DataDirect" are replaced with the term "IBM Lotus OEM" in the .ini file.

[ODBC Data Sources]
DB2 Wire Protocol= 5.3 DB2 Wire Protocol Driver
dBase=IBM Lotus OEM 5.3 dBaseFile(*.dbf)
FoxPro3=IBM Lotus OEM 5.3 dBaseFile(*.dbf)
Informix=IBM Lotus OEM 5.3 Informix
Informix Wire Protocol=IBM Lotus OEM 5.3 Informix Wire Protocol
Oracle=IBM Lotus OEM 5.3 Oracle
Oracle Wire Protocol=IBM Lotus OEM 5.3 Oracle Wire Protocol
Progress9=IBM Lotus OEM 5.3 Progress9
Progress10=IBM Lotus OEM 5.3 Progress10
SQLServer Wire Protocol=IBM Lotus OEM 5.3 SQL Server Wire Protocol
Sybase Wire Protocol=DataDirect 5.2 Sybase Wire Protocol
Text=DataDirect 5.2 TextFile(*.*)
[DB2 Wire Protocol]
Driver=/opt/odbc/lib/LOdb223.so
Description=DataDirect 5.3 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=UID
password=PWD
Package=DB2 package name
PackageOwner=
TcpPort=DB2 server port
WithHold=1
[DB2V71]
Driver=/opt/odbc/lib/LOdb223.so
Description=DataDirect 5.3 DB2 Wire Protocol Driver
AddStringToCreateTable=
AlternateID=
Database=DB2V71
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=rage
IsolationLevel=CURSOR_STABILITY
LogonID=UID
password=PWD
Package=DDODBC
PackageOwner=
TcpPort=50000
WithHold=1
[Informix Wire Protocol]
Driver=/opt/odbc/lib/LOifcl23.so.so
Description=DataDirect 5.3 Informix Wire Protocol
ApplicationUsingThreads=1
CancelDetectInterval=0
Database=db
HostName=Informix host
LogonID=UID
password=PWD
PortNumber=Informix server port
ServerName=Informix server
TrimBlankFromIndexName=1
[InformixDSN]
Driver=/opt/odbc/lib/LOifcl23.so.so
Description=DataDirect 5.3 Informix Wire Protocol
ApplicationUsingThreads=1
CancelDetectInterval=0
Database=qetest
HostName=flood
LogonID=UID
password=PWD
PortNumber=1526
ServerName=informix_940
TrimBlankFromIndexName=1
[Oracle Wire Protocol]
Driver=/opt/odbc/lib/LOora23.so
Description=DataDirect 5.3 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=Oracle server
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=UID
password=PWD
PortNumber=1521
ProcedureRetResults=0
SID=Oracle SID
UseCurrentSchema=1
[ODBCToOracle10]
Driver=/opt/odbc/lib/LOora23.so
Description=DataDirect 5.3 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=flood
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=UID
password=PWD
PortNumber=1521
ProcedureRetResults=0
SID=flood
UseCurrentSchema=1
[SQLServer Wire Protocol]
Driver=/opt/odbc/lib/LOmsss23.so
Description=DataDirect 5.3 SQL Server Wire Protocol
Address=SQLServer host,SQLServer server port
AnsiNPW=Yes
Database=db
LogonID=UID
password=PWD
QuotedId=No
[ODBCToSQLServer7]
Driver=/opt/odbc/lib/LOmsss23.so
Description=DataDirect 5.3 SQL Server Wire Protocol
Address=frost,1433
AnsiNPW=Yes
Database=Tracey
LogonID=UID
password=PWD
QuotedId=No
[Sybase Wire Protocol]
Driver=/opt/odbc/lib/LOase23.so
Description=DataDirect 5.3 Sybase Wire Protocol
ApplicationName=
ApplicationUsingThreads=1
ArraySize=50
Charset=
CursorCacheSize=1
Database=db
DefaultLongDataBuffLen=1024
EnableDescribeParam=0
EnableQuotedIdentifiers=0
InitializationString=
Language=
LogonID=UID
NetworkAddress=Sybase host,Sybase server port
OptimizePrepare=1
PacketSize=0
password=PWD
RaiseErrorPositionBehavior=0
SelectMethod=0
WorkStationID=
[ODBCTOSybase12]
Driver=/opt/odbc/lib/LOase23.so
Description=DataDirect 5.3 Sybase Wire Protocol
ApplicationName=
ApplicationUsingThreads=1
ArraySize=50
Charset=
CursorCacheSize=1
Database=SILK_DATA
DefaultLongDataBuffLen=1024
EnableDescribeParam=0
EnableQuotedIdentifiers=0
InitializationString=
Language=
LogonID=UID
NetworkAddress=frost,5000
OptimizePrepare=1
PacketSize=0
password=PWD
RaiseErrorPositionBehavior=0
SelectMethod=0
WorkStationID=
[ODBC]
IANAAppCodePage=4
InstallDir=/opt/odbc
Trace=0
TraceDll=/opt/odbc/lib/odbctrac.so
TraceFile=odbctrace.out
UseCursorLib=0

The odbc.ini file is supplied with example connections for various data sources. To create a new data source name (DSN), copy one of the existing examples and change the required entries to match those of the data source. In the example above, the section starting with "[ODBCToOracle10]" is the configuration information for the data source named ODBCtoOracle10. It uses the Oracle Wire Protocol driver (specified by the "Driver="line) to connect to a system named flood (specified by the "HostName=" line) on port 1521 (specified by the "PortNumber='" line) with SID flood (specified by the "SID=" line). You may need to specify the fully-qualified host name for the "HostName=" entry. The required options vary for each type of data source. For details on configuration options, reference http://www.datadirect.com/support/product_info/proddoc_product/index.ssp.

Note: When using an Oracle Wire Protocol ODBC data source, ProcedureRetResults=1 must be set in the Data Source configuration in order to get result sets from stored procedures.
Note: All /QIBM/ProdData/LOTUS/... paths in the odbc.ini depend on which Domino version the server is running, as described above.
Note: To point to the data source code page, use the "IANAAppCodePage" parameter in each connection section. If the parameter is not added, the default "IANAAppCodePage" in the "[ODBC]" section is used. For more details, reference http://www.datadirect.com/support/troubleshooting/su_faq_iana/index.ssp.

IANAAppCodePage usage for multi-language support

The parameter "IANAAppCodePage" is introduced into odbc.ini to support multi-language for different ODBC database source. It can be added to each connection section with the format "IANAAppCodePage=XXX". The "XXX" indicates the code page of the ODBC database source.

For example, adding "IANAAppCodePage=17" in the connection section "[ODBCToOracle10]" means the code page of the Oracle database source is Japanese Shift_JIS. For more code page details, see http://www.datadirect.com/support/troubleshooting/su_faq_iana/index.ssp.

If "IANAAppCodePage" is not used in the separate connection section, the default setting "IANAAppCodePage=4" in the section [ODBC] is used.

Removing CR in the odbc.ini File

If the odbc.ini file was edited using the CRLF line terminator instead of the LF line terminator, you can remove the CR character using the following procedure:

  1. From the command line type "CALL QP2TERM" (no quotes) to start a PASE terminal session
  2. Change to the directory where the odbc.ini file is located. An example is shown below:
    cd /QIBM/UserData/LOTUS/DOMINO850/EITODBC
  3. Back up your existing odbc.ini file using the following command: 
    cp odbc.ini odbc.iniBKUP
  4. Use the following command to remove the CR from the file:
    awk '{ gsub( /\r$/, "" ); print $0 }' odbc.ini odbc.ini