Websphere IIB record and replay

You will find in this post the commands that is necessary to configure the Integration Bus to record and replay the events generated by the flows.

Even though all the following information is available in the knowledge center, I found sometime difficult to find out all the necessary commands to be executed.

Parameters

Integration server

Integration Node name: <INName>
Integration Server name: <ISName>
IIB Queue Manager name: <IIBQMgrName>

Configurable Service

    Data capture store name. Configuration to define the database to be used: <DCStoreN>
DataCaptureSource name. Configuration to define the event source: <DCSourceN>
Data Destination Name: configuration used to define the queue where the message will be send when using the replay mechanism: <DDName>
Queue name used to send back the data: <ReplayQName>

Database configuration

ODBC Database DSN: <DSN>
Table Schema used to store the IIB events: <IIBSchema>
User/password for accessing the database under the schema <IIBSchema>: <DBUsr>/<BDPwd>

Script

Create configurable services for IIB

1. DataCaptureSource
 

mqsicreateconfigurableservice <INName> -c DataCaptureSource -o <DCSourceN> -n dataCaptureStore,topic -v <DCStoreN>,"$SYS/Broker/<INName>/Monitoring/#"

2. DataCaptureStore

mqsicreateconfigurableservice <INName> -c DataCaptureStore -o <DCStoreN> -n backoutQueue,commitCount,commitIntervalSecs,dataSourceName,egForRecord,egForView,queueName,schema,threadPoolSize,useCoordinatedTransaction  -v "SYSTEM.BROKER.DC.BACKOUT","10","5",<DSN>","<ISName>","<ISName>","SYSTEM.BROKER.DC.RECORD","<IIBSchema>","10","false"

3. DataDestination

mqsichangeproperties<INName> -c DataDestination -o <DDName> -n egForReplay,endpoint,endpointType  -v "<ISName>","wmq:/msg/queue/<ReplayQName>@<IIBQMgrName>","WMQDestination"

Database connection configuration

1. Create odbc connection in ODBC Data Source Administrator (Demo =  <DSN>)
2. Set security connection information

mqsisetdbparms <INName> -n <DSN> -u <DBUsr> -p <BDPwd>

3. Run script DataCaptureSchema.sql from db2 command line (non-administrator). This script are available under
<IIBInstallation>\ddl\db2

JDBC with IBM Integration Bus

#create configurable serivce:
mqsicreateconfigurableservice TESTNODE -c JDBCProviders -o DSNName -n type4DatasourceClassName,type4DriverClassName,databaseType,jdbcProviderXASupport,portNumber,serverName,description,databaseName,securityIdentity,connectionUrlFormat,databaseSchemaNames -v com.ibm.db2.jcc.DB2XADataSource,com.ibm.db2.jcc.DB2Driver,DB2,jdbcProviderXASupport,446,

,"Data Store",DSNName,DB2SecurityIdentity,"jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];",useProvidedSchemaNames

#provide broker with user password
mqsisetdbparms TESTNODE -n jdbc::DB2SecurityIdentity -u userid -p pwd


# link that documents the database config for the different environments:
http://sharethewealth/SiteDirectory/bdsg/IbmRdz/rdzblog/Lists/Posts/Post.aspx?ID=41

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

DB2 Client Installation on Linux SUSE Linux Enterprise Server & DSN Setup

DB2 Client Installation on Linux SUSE Linux Enterprise Server & DSN Setup

1.     Download the DB2 client v10.1fp5_linuxx64_client.tar.gz from below link.

http://www-933.ibm.com/support/fixcentral/swg/selectFix?source=dbluesearch&product=ibm%2FInformation+Management%2FIBM+Data+Server+Client+Packages&release=10.1.*&platform=Linux+64-bit,x86_64&searchtype=fix&fixids=*-client-*FP005&function=fixId&parent=ibm/Information%20Management

i.                 Use your IBM ID & password to get the DB2 server client software.

2.     Pre-requisite before installation of DB2 client into Linux 64 Bit Suse Linux Enterprise server 10.3.

i.                 Check the operating system compatibility.

http://www-969.ibm.com/software/reports/compatibility/clarity-reports/report/html/softwareReqsForProduct?deliverableId=1319124752192&osPlatform=Linux

ii.                For DB2 client installation, /opt/ibm & /tmp filesystem should be 1 to 2 GB free.

iii.               Verify the Library files.

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.qb.server.doc/doc/r0008865.html?cp=SSEPGG_10.5.0%2F2-0-1-2-2-4

iv.               Verify the Kernel parameters. If needed then change the kernel parameters in /etc/sysctl.conf file.

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.qb.server.doc/doc/t0008238.html?cp=SSEPGG_10.5.0%2F2-0-1-2-2-0-10-3

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.qb.server.doc/doc/c0057140.html?cp=SSEPGG_10.5.0%2F2-0-1-2-2-0-10-1

v.                Below are the kernel parameters which are already fulfilled for DB2 client installation.

3.     Copy the v10.1fp5_linuxx64_client.tar.gz file, wherever you have free disk space.

I have copied into /MQHA/BDBKRD01/wmb path.

Execute the below command for gunzip & then extract the tar file which will create client folder as shown below.

/usr/bin/gunzip v10.1fp5_linuxx64_client.tar.gz

tar –xvf v10.1fp5_linuxx64_client.tar

Go to client folder.

4.     You can check the DB2 client pre-requisite by using following command.

./db2prereqcheck -c -v 10.1.0.5

5.     There are 4 types of DB2 client installation but we have used the response file installation method for DB2 client.

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.qb.server.doc/doc/c0008711.html?lang=en

i.                 You can take the copy of sample response file (/MQHA/BDBKRD01/wmb/client/db2/linuxamd64/samples/db2client.rsp) & keep it in another folder (/MQHA/BDBKRD01/wmb/client/db2client.rsp) as well as update it with accept the license.

ii.                Installation of DB2 client with Response file installation method by using root ID. Execute the below command

db2setup -r /MQHA/BDBKRD01/wmb/client/db2client.rsp -l /MQHA/BDBKRD01/wmb/client/db2setup_eimb.log -t /MQHA/BDBKRD01/wmb/client/db2setup_eimb.trc

You can verify the logs & trace files of DB2 client installation.

 

                              NOTE:- DB2 client & drivers will be installed in /opt/ibm/db2/V10.1 path.

iii.               Once you installed DB2 client by using root ID, then db2inst1 User ID & db2iadm1 group ID will get create by default.

iv.               You can work with unix team & set the password for db2inst1 user ID. For example password of db2inst1 User ID is Start123

6.     DB2 Validation.

i.                 Login to server with db2inst1 user ID & execute the below commands.

db2level :- verify the DB2 client installation version.

              

               db2val :- db2 validation.

7.     Installed the DB2 Client licenses.

You need to contact to IBM DB2 team to buy the license. We have purchased below one.

IBM DB2 Connect 10.1 Unlimited Edition for System i Quick Start and Activation Multiplatform Multilingual        CI6N8ML         db2consv_is.lic

i.                 db2licm is the command which we used to add the license.

eimb@j700s013:/MQHA/BDBKRD01/wmb/client/db2/license> db2licm -a db2consv_is.lic

LIC1402I  License added successfully.

LIC1426I  This product is now licensed for use as outlined in your License Agreement.  USE OF THE PRODUCT CONSTITUTES ACCEPTANCE OF THE TERMS OF THE IBM LICENSE AGREEMENT, LOCATED IN THE FOLLOWING DIRECTORY: "/opt/ibm/db2/V10.1/license/en_US.iso88591"

ii.                Get the db2 licenses details.

eimb@j700s013:/MQHA/BDBKRD01/wmb/client/db2> db2licm -l

Product name:                     "DB2 Connect Unlimited Edition for iSeries"

License type:                     "Client Device"

Expiry date:                      "Permanent"

Product identifier:               "db2consv"

Version information:              "10.1"

iii.               Add the db2jcc_license_cisuz.jar file in /home/db2inst1/sqllib/java path.

iv.               Export /home/db2inst1/sqllib/java/db2jcc_license_cisuz.jar path in CLASSPATH variable.

v.                Export /opt/ibm/IE02 path in IE02_PATH variable.

8.     DB2 commands, Driver configuration & connection setup

i.                 Configure the db2 driver. Go to /opt/ibm/db2/V10.1/cfg path & update the db2dsdriver.cfg file with below details.

vi.               Create the connection with DB2 Database by using following commands.

eimb@j700s013:/opt/ibm/db2/V10.1/bin> db2 catalog tcpip node NANTESD remote DEV.CG.EU.JCI.COM server 446

eimb@j700s013:/opt/ibm/db2/V10.1/bin> db2 catalog database S4427047 at node NANTESD

eimb@j700s013:/opt/ibm/db2/V10.1/bin> db2 connect to S4427047 user UNITYUSR using UnItY15

db2 list database directory

9.     WMB DSN Setup.

i.                 Work with unix team & asked them to add eimb user ID in db2iadm1 group ID as well as add dbinst1 user ID in mqbrkrs group ID.

ii.                Login to sever with your global ID & then sudo with eimb user ID. Add the below command in eimb profile file (/home/eimb/.profile).

# DB2 Profile

. /home/db2inst1/sqllib/db2profile

iii.               Once above both the things completed successfully then you can execute the DB2 commands by eimb user ID.

iv.               Go to /var/mqsi/odbc path & update .odbc.ini file with below details & execute the commands as shown below.