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

How to create a SOAP Action Policy

Adding a SOAP action policy

A user agent can require that the contents of the HTTP SOAPAction request header field be supplied.

About this task

The HTTP header contains the SOAP action (a URI that identifies the intent of the SOAP HTTP request). If the header contains the SoapAction: http://example.org/add header, the URI of http://example.org/add is the value.

Procedure

1. In the search field, enter User Agent.
2. From the search results, click User Agent.
3. Click the name of a user agent configuration.
4. Click the Soap-Action Policy tab.
5. Add a policy.
   1. Click Add.
   2. In the URL Matching Expression field, enter a shell-style expression to be the matching pattern for the URL set.
   3. In the Soap Action field, enter the URI of the SOAP action.
   4. Click Apply.
6. Optional: Repeat the previous step to add another policy.
7. Click Apply to save the changes to the running configuration.
8. Optional: Click Save Configuration to save the changes to the persisted configuration

adding a header injection policy

Adding a header injection policy

Add a header injection policy to a user agent to inject an HTTP header (name-value pair) into a request to the remote server.

About this task

Several DataPower® services can also inject HTTP headers. The user agent operates on the request after the service.

Procedure

1. In the search field, enter User Agent.
2. From the search results, click User Agent.
3. Click the name of a user agent configuration.
4. Click the Inject Header Policy tab.
5. Add a policy.
   1. Click Add.
   2. In the URL Matching Expression field, enter a shell-style expression to be the matching pattern for the URL set.
   3. Define the header to inject: Header name and value.
   4. Click Apply.
6. Optional: Repeat the previous step to add another policy.
7. Click Apply to save the changes to the running configuration.
8. Optional: Click Save Configuration to save the changes to the persisted configuration.

Adding an SMTP client policy

Adding an SMTP client policy

How to add an SMTP client policy to a user agent.

About this task

Availability: Appliances with the B2B feature.

The user agent controls the client settings for outgoing SMTP connections for requests that match the URL expression. These client settings can be overridden by query parameters in the URL that initiates the mail transfer.

Procedure

1. In the search field, enter User Agent.
2. From the search results, click User Agent.
3. Click the name of a user agent configuration.
4. Click the SMTP Client Policies tab.
5. Add a policy.
   1. Click Add.
   2. In the URL Matching Expression field, enter a shell-style expression to be the matching pattern for the URL set.
   3. In the Recipient email field, enter the default email address to which to send email messages.
   4. In the Sender email field, enter the default email address that identifies the sender.
   5. In the Subject field, enter the default subject for email messages.
   6. From the Options list, click the check boxes for the SMTP options to enable.
   7. To use the Simple Authentication and Security Layer (SASL) client authentication: From the Client Authentication list, select the authentication method.
   8. Click Apply.
6. Optional: Repeat the previous step to add another policy.
7. Click Apply to save the changes to the running configuration.
8. Optional: Click Save Configuration to save the changes to the persisted configuration.

MQ Linux Commands

The majority of these commands are run as the mqm (or equivalent) user. By default these commands are in /opt/mqm/bin which I would advise adding to the mqm users PATH.

Create a Queue Manager

$ crtmqm REPLACE_QMGR_NAME

Delete a Queue Manager

$ dltmqm REPLACE_QMGR_NAME

Start Queue Manager

$ strmqm REPLACE_QMGR_NAME

Stopping Queue Manager

Wait for queue manager to shutdown

$ endmqm -w REPLACE_QMGR_NAME

End Immediately

$ endmqm -i REPLACE_QMGR_NAME

Start Queue Manager (Init Script)

By default Websphere MQ does not ship with an init script, you can add this functionality via a support pac (MSL1) provided by IBM.

$ service ibm.com-WebSphere_MQ start

Stopping Queue Manager (Init Script)

$ service ibm.com-WebSphere_MQ stop

Start MQ Listener

$ echo "start LISTENER(SYSTEM.DEFAULT.LISTENER.TCP)" | runmqsc REPLACE_QMGR_NAME

Stop MQ Listener

$ echo "stop LISTENER(SYSTEM.DEFAULT.LISTENER.TCP)" | runmqsc REPLACE_QMGR_NAME

Display Queue Managers & Status

$ dspmq

Set MQ Privileges

In order for a Unix user to start utilizing MQ they must have the appropriate privileges. You can find the available privileges in IBM's Documentation.

Set MQ Privileges By User

$ setmqaut -m REPLACE_QMGR_NAME -t qmgr -p REPLACE_USER REPLACE_PLUS_OR_MINUS_PRIVILEGE

Set MQ Privileges By Groups

$ setmqaut -m REPLACE_QMGR_NAME -t qmgr -g REPLACE_GROUP REPLACE_PLUS_OR_MINUS_PRIVILEGE

Display MQ Privileges

Display MQ Privileges By Users

$ dspmqaut -m REPLACE_QMGR_NAME -t qmgr -p REPLACE_USER

Display MQ Privileges By Groups

$ dspmqaut -m REPLACE_QMGR_NAME -t qmgr -g REPLACE_GROUP

Lookup MQ Error Numbers

$ mqrc REPLACE_ERROR_NUM

MQ Sample Scripts

Within the MQ release by IBM there is a package that contains sample scripts for MQ. There are about 3 of these sample scripts that I have found pretty useful.

Pop (GET) Messages off a queue

$ amqsget REPLACE_Q_NAME REPLACE_QMGR_NAME

This command will remove messages from the queue, only use this if you no longer want the messages in the queue.

Browse Messages in a queue

$ amqsbcg REPLACE_Q_NAME REPLACE_QMGR_NAME

Open a Queue for writing

$ amqsput REPLACE_Q_NAME REPLACE_QMGR_NAME

When you find yourself with a system that is connected to a repository but doesn't see new messages you can try opening the queue for writing with amqsput to "refresh" the connectivity. You do not have to write anything to the queue. Simply open the queue and press ctrl+d without typing any additional characters.

Open Websphere MQ CLI

$ runmqsc REPLACE_QMGR_NAME

Websphere MQ CLI Commands

The following commands are to be run directly from the MQ command line interface.

Start MQ Listener

start LISTENER(SYSTEM.DEFAULT.LISTENER.TCP)

Stop MQ Listener

stop LISTENER(SYSTEM.DEFAULT.LISTENER.TCP)

Disable Channel Authentication

alter qmgr chlauth(disabled)

Channel Authentication is new as of MQ 7.0. If your systems don't use it you can simply turn it off.

Display Queues

All Queues

display queue(*)

Specific Queues

display queue(REPLACE_Q_NAME)

Short hand

dis q(REPLACE_Q_NAME)

Display Local Queues only

All Queues

display qlocal(*)

Specific Queue

display qlocal(REPLACE_Q_NAME)

Short hand

dis ql(REPLACE_Q_NAME)

Display Alias Queues only

All Queues

display qalias(*)

Specific Queue

display qalias(REPLACE_Q_NAME)

Short hand

dis qa(REPLACE_Q_NAME)

Display Cluster Queues only

All Queues

display qcluster(*)

Specific Queue

display qcluster(REPLACE_Q_NAME)

Short hand

dis qc(REPLACE_Q_NAME)

Display Channels

All Channels

display channel(*)

Specific Channel

display channel(REPLACE_CHANNEL_NAME)

Short hand

dis channel(REPLACE_CHANNEL_NAME)

Display Channel Status

All Channels

display chstatus(*)

Specific Channel

display chstatus(REPLACE_CHANNEL_NAME)

Short hand

dis chstatus(REPLACE_CHANNEL_NAME)

Display Local Queue Manager Information

display qmgr

Display Cluster Queue Manager Information

display clusqmgr(REPLACE_CLUSQMGR_NAME)

Show number of INPUT and OUTPUT threads open

display qstatus(REPLACE_Q_NAME) IPPROCS OPPROCS

Find Process ID of Processes Accessing Queue

display qstatus(REPLACE_Q_NAME) TYPE(HANDLE) ALL

Refresh Cluster Queue Manager

refresh cluster(REPLACE_CLUSQMGR_NAME)

Start Channel

start channel(REPLACE_CHANNEL_NAME)