Configuring DSN for IBM Integration Bus in Fedora for Oracle
I had a very troubling time setting up a DSN for IIB in Fedora. To make sure that others don't have to waste a precious weekend and long time on this rather menial subject I thought I would document it for all.Assumption :- The software is installed at the location - /opt/ibm/mqsi/9.0.0.0
Here are the steps -
- A sample odbc.ini and odbcinst.ini file are in the 'install_dir/ODBC/unixodbc/' in my case it is /opt/ibm/mqsi/9.0.0.0/ODBC/unixodbc. Copy the files to /var/mqsi/odbc directory.
cp /opt/ibm/mqsi/9.0.0.0/ODBC/unixodbc/odbcinst.ini /var/mqsi/odbc/odbcinst.ini
- Change the owner of the files to mqm:mbrkrs using the following command
chown mqm:mqbrkrs /var/mqs/odbc/odbc.ini
chown mqm:mqbrkrs /var/mqs/odbc/odbcinst.ini
- Open the '/var/mqsi/odbc/odbc.ini' file. Copy the following lines and paste them just above the copied part-
;# Oracle stanza
[ORACLEDB]
Driver=<Your Broker install directory>/ODBC/V7.0/lib/UKora26.so
Description=DataDirect 7.0 ODBC Oracle Wire Protocol
HostName=<Your Oracle Server Machine Name>
PortNumber=<Port on which Oracle is listening on HostName>
ServiceName=<Your Oracle Service Name>
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
LoginTimeout=0
[XE]
Driver=/opt/ibm/mqsi/9.0.0.0/ODBC/V7.0/lib/UKora26.so
HostName=localhost
PortNumber=1521
ServiceName=XE
Not to forget, at the end of the file mention the install directory.
[ODBC]
InstallDir=/opt/ibm/mqsi/9.0.0.0/ODBC/V7.0
UseCursorLib=0
IANAAppCodePage=4
UNICODE=UTF-8
Save the file.
- Download the IE02 support pac from the following location - http://www-01.ibm.com/support/docview.wss?uid=swg24026935 . In my case I used the 64 bit version 2.0.1 and the file name is 'ie02_amd64_linux_2.tar'. Extract the archive file and it create a folder-in my case 'amd64_linux_2'- that will contain 'install-ie02.bin' file. Run the .bin file and install it. I had it installed in the location '/opt/ibm/IE02'
- Now that we have all the files in place we need to setup some environment variables in the .profile file of the user that would control the broker runtime. I have the following variables added to end of my .bash_profile
export ODBCINI=/var/mqsi/odbc/odbc.ini
export ODBCSYSINI=/var/mqsi/odbc/
export IE02_PATH=/opt/ibm/IE02/2.0.1/- Use the mqsisetdbparms command to associate the user id and password to the ODBC. The following example command will prompt you for the password and then set the user id and password -
- Restart the broker to allow it to absorb the setting and then issue the command to check if the broker runtime can access the DSN.
If the command runs with success you should see the output of the command as follows -
BIP8290I: Verification passed for the ODBC environment.
BIP8270I: Connected to Datasource 'XE' as user 'SYSTEM'. The datasource platform is 'Oracle', version '11.02.0000 Oracle 11.2.0.2.0'.
===========================
databaseProviderVersion = 11.02.0000 Oracle 11.2.0.2.0
driverVersion = 07.01.0097 (B0099, U0067)
driverOdbcVersion = 03.52
driverManagerVersion = 03.52.0002.0002
driverManagerOdbcVersion = 03.52
databaseProviderName = Oracle
datasourceServerName = localhost
databaseName = N/A
odbcDatasourceName = XE
driverName = UKora26.so
supportsStoredProcedures = Yes
procedureTerm = PL/SQL
accessibleTables = Yes
accessibleProcedures = Yes
identifierQuote = "
specialCharacters = None
describeParameter = Yes
schemaTerm = User Name
tableTerm = Table
sqlSubqueries = 31
activeEnvironments = 0
maxDriverConnections = 0
maxCatalogNameLength = 128
maxColumnNameLength = 30
maxSchemaNameLength = 30
maxStatementLength = 0
maxTableNameLength = 30
supportsDecimalType = Yes
supportsDateType = No
supportsTimeType = No
supportsTimeStampType = No
supportsIntervalType = No
supportsAbsFunction = Yes
supportsAcosFunction = No
supportsAsinFunction = No
supportsAtanFunction = No
supportsAtan2Function = No
supportsCeilingFunction = Yes
supportsCosFunction = Yes
supportsCotFunction = No
supportsDegreesFunction = No
supportsExpFunction = Yes
supportsFloorFunction = Yes
supportsLogFunction = Yes
supportsLog10Function = Yes
supportsModFunction = Yes
supportsPiFunction = No
supportsPowerFunction = Yes
supportsRadiansFunction = No
supportsRandFunction = No
supportsRoundFunction = Yes
supportsSignFunction = Yes
supportsSinFunction = Yes
supportsSqrtFunction = Yes
supportsTanFunction = Yes
supportsTruncateFunction = Yes
supportsConcatFunction = Yes
supportsInsertFunction = Yes
supportsLcaseFunction = Yes
supportsLeftFunction = Yes
supportsLengthFunction = Yes
supportsLTrimFunction = Yes
supportsPositionFunction = No
supportsRepeatFunction = Yes
supportsReplaceFunction = Yes
supportsRightFunction = Yes
supportsRTrimFunction = Yes
supportsSpaceFunction = Yes
supportsSubstringFunction = Yes
supportsUcaseFunction = Yes
supportsExtractFunction = No
supportsCaseExpression = No
supportsCastFunction = No
supportsCoalesceFunction = No
supportsNullIfFunction = No
supportsConvertFunction = Yes
supportsSumFunction = Yes
supportsMaxFunction = Yes
supportsMinFunction = Yes
supportsCountFunction = Yes
supportsBetweenPredicate = Yes
supportsExistsPredicate = Yes
supportsInPredicate = Yes
supportsLikePredicate = Yes
supportsNullPredicate = Yes
supportsNotNullPredicate = Yes
supportsLikeEscapeClause = Yes
supportsClobType = No
supportsBlobType = No
charDatatypeName = CHAR
varCharDatatypeName = VARCHAR2
longVarCharDatatypeName = CLOB
clobDatatypeName = N/A
timeStampDatatypeName = N/A
binaryDatatypeName = RAW
varBinaryDatatypeName = RAW
longVarBinaryDatatypeName = BLOB
blobDatatypeName = N/A
intDatatypeName = NUMBER
doubleDatatypeName = BINARY_DOUBLE
varCharMaxLength = 0
longVarCharMaxLength = 0
clobMaxLength = 0
varBinaryMaxLength = 0
longVarBinaryMaxLength = 0
blobMaxLength = 0
timeStampMaxLength = 0
identifierCase = Upper
escapeCharacter = \
longVarCharDatatype = -1
clobDatatype = 0
longVarBinaryDatatype = -4
blobDatatype = 0
BIP8273I: The following datatypes and functions are not natively supported by datasource 'XE' using this ODBC driver: Unsupported datatypes: 'DATE, TIME, TIMESTAMP, INTERVAL, CLOB, BLOB' Unsupported functions: 'ACOS, ASIN, ATAN, ATAN2, COT, DEGREES, PI, RADIANS, RAND, POSITION, EXTRACT, CASE, CAST, COALESCE, NULLIF'
Examine the specific datatypes and functions not supported natively by this datasource using this ODBC driver.
When using these datatypes and functions within ESQL, the associated data processing is done within IBM Integration Bus rather than being processed by the database provider.
Note that "functions" within this message can refer to functions or predicates.
BIP8071I: Successful command completion.
References -
ftp://public.dhe.ibm.com/software/integration/support/supportpacs/individual/ie02_v2.pdf
http://pic.dhe.ibm.com/infocenter/wmbhelp/v9r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fbk58060_.htm
