Configuring DSN for IBM Integration Bus in Fedora for Oracle

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/odbc.ini /var/mqsi/odbc/odbc.ini
           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

           Make the changes as follows for your DSN. In my case I create a DSN as XE for my XE database. The                driver path may be different as per your installation.

           [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 -

           mqsisetdbparms WBRK9 -n XE -u system

• 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. 

          mqsicvp -n XE -u system -p yourpassword

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