WebSphere Transformation Extender DB2 insert

WebSphere Transformation Extender can be used to insert transformed data into a Database in this case I used DB2. Below is the steps to follow to do a DB2 Insert into WTX:

Step 1: Open WebSphere Transformation Extender Database Interface Designer. Select New to create a new Database Query File. Right Click on Databases to specify all the parameters to connect to your database, specify a data source and specify username and password of database:

Step 2: Right click on your newly created Data Source and Select: Generate Tree From -> Table

Step 3: Select the table you want to Generate a Type Tree for to do the insert

Step 4: Add this newly created Type Tree to your WTX Design Studio as an input card. Add the following tree called DB2load.mtt as your output Card.

Step 5: Complete your Output Card with all the necessary DB2 parameters and fields by dragging and dropping the from input card to output card:

Step 6: Run and compile your map, make sure all the database parameters are set and DB2 has started successfully.

Create DB2 Data Source for WebSphere Application Server

How to create a DB2 Data Source for Websphere Application Server:

Step 1: Click on Resource -> JDBC -> JDBC Provider -> New

Confirm that the scope of the JDBC Connection is correct -> Node level.

Step 2: Complete the JDBC Provider Fields.

  • Database type : DB2
  • Provider type: DB2 Universal JDBC Driver Provider
  • Implementation Type: XA data source
  • Name: DB2 Universal JDBC Driver Provider (XA)

Click Next to complete the next step. Specify the class path to DB2, which will be at C:\IBM\SQLLIB\java

Step 3: Click Finish and confirm JDBC Provider was successfully created. Next step is to create the Data Source. Click on the new JDBC Provider and click on ‘Data Source’ and select ‘New’ to create a new Data Source. Below is the information that needs to be completed.

Confirm DB2 Server and Database in DB2 Explorer

Step 4: Create JAAS – J2C authentication Data specifying DB2 username and password for Container Connection:

Step 5: Select newly created container managed authentication alias

Click Next and Finish.

Step 6. Test Data Source Connection. Select Data Source and click ‘Test Connection’ button. Confirm connection was successful else check all fields.

Setup Websphere MQ File Transfer Database Logger

Step 1. Run the following db2 command:

— drop if necessary and then create FTELOGDB

echo *******************************************************

echo the following command may fail because the database did

echo NOT previously exist, this is an acceptable error

echo *******************************************************

drop database FTELOGDB

echo — Creating database FTELOGDB

create database FTELOGDB

echo — Connect to FTELOGDB database

connect to FTELOGDB user esbuser using wesb4soa

— catalog FTELOGDB as a odbc data source

catalog system odbc data source FTELOGDB

list system odbc data sources

echo — now create the schemae, tables and views

echo — use this db2cmd command console

echo — it is already connected to the database

Step 2: Create the DB2 Schema Tables

Navigate to: C:\Program Files\IBM\WMQFTE\tools\sql

Execute the following command:

db2 –v -t –f ftelog_tables_db2.sql

Step 3: Configure the Database Logger Properties File

Navigate to: *\WMQFTE\config

Open file in Notepad: databaselogger.properties

Edit the following values:

wmqfte.queue.manager=QM_COORD1

(Coordinator Queue Manager Name)

wmqfte.database.name=FTELOGDB

(Database Name created in Step 1)

wmqfte.database.driver=D:/Applications/IBM/SQLLIB/java/db2jcc.jar;D:/Applications/IBM/SQLLIB/java/db2jcc_license_cu.jar

(DB2 Driver Path)

wmqfte.database.native.library.path=C:/Applications/IBM/SQLLIB/lib

(Library Path to DB2)

Step 4: Setup the transaction manager

Open MQ Explorer right click on the Coordinator Queue Manager and select Properties. Under XA Resource Manager click the ‘Add’ button.

Enter FTELOGDB as the Name.

Enter jdbcdb2.dll as the SwitchFile.

Enter db=FTELOGDB,uid=db2admin,pwd=db8admin,toc=p,tpm=mq as the XAOpenString.

Make sure the ThreadOfControl is set to Process.

Press the OK button to add the XA resource manager to the queue manager.

Click OK

Step 5: Copy the jdbcdb2.jar to the existing folder

Navigate to *\IBM\WebSphere MQ

execute the following command to copy the jdbcdb2.jar file: copy java\lib\jdbc\jdbcdb2.dll exits

Step 6: Execute a Controlled Stop of the Coordinator Queue Manager

Step 7: Start the Database Logger

Execute the following command in *\MQFTE\tools\bin: fteStartDatabaseLogger –F


Step 8: Run a new Transfer

Navigate to ‘Manage File Transfer’ and connect the Coordinator Queue Manager and right click on Transfer Log and select ‘New Transfer’

Select a Agent, file to transfer and Click Next till you see the following window:

Add two metadata attribute value pairs:

Click Next and Finish to start file transfer

Step 9: Open DB2 Control Panel to View the Database Logger Table

Navigate to your Database Logger Database -> Views -> Basic_Transfer_Details.

Navigate to your Database Logger Database -> Tables -> Metadata.

Optional Stop Database Logger:

Navigate to *\MQFTE\tools\bin\fteStopDatabaseLogger.cmd

Database Logger will stop succesfully else see result of error.

Insert row DB2 with Websphere Cast Iron

How to insert a row into DB2 using Websphere Cast Iron.

Step 1: Create an DB2 Endpoint by specifying the following information:

  • DB2 Database Name
  • DB2 Host Name
  • DB2 Port Number
  • DB2 Username
  • DB2 Password

Please make sure that the Cast Iron appliance or Virtual image has rights to insert a row onto DB2. This can be done by specifying the Privileges in the DB2 Control Centre.

Step 2: Test the connection to verify all information is correct.

Step 3: Create your integration flow

Step 4: Specify the DB2 Endpoint created in Step 1 and select the table and row you want the data to be inserted into

Step 5: Create the input and output maps for both activities

Step 6: Verify and test the flow.

This flow will update a website that is located in a cloud environment (like Amazon EC2) and updated an on-premise DB2 instance.

Salesforce and DB2 integration with Cast Iron

Below is a short tutorial on how to integrate Salesforce with IBM DB2 using Cast Iron.

1. Define your SalesForce Endpoint by providing the following information (you must be a registered member of Salesforce):

  • Username
  • Password

Ste 2: Test the connection and make sure that it is successful.

Step 3: Define the DB2 Endpoint by providing the following information:

  • DB2 Hostname
  • DB2 Port Number
  • DB2 Username
  • DB2 Password
  • PackageCollection: 100 (this can be any value)

Step 4: Test your DB2 connection to make sure it successfully connects to DB2

Step 5: Create you Orchestration flow

Step 6: Select the Salesforce Endpoint in Step 1 as the Endpoint for the ‘Poll Updated Activities’ Activity

Step 7: Select the Salesforce table that you want to poll.

In this instance in the Account Table the Name field was selected and will be used to update into DB2.

Step 8: Create the Ouput Map that will be the Input to the DB2 Activity

Step 9: On the Insert Rows activity select the DB2 Endpoint created in Step 3. Select the table that you want to insert the new row.

Step 10: Select the field of the DB2 Endpoint to insert the Salesforce data.

Step 11: Map the output from the Salesforce Poll Update Object activity to the input of the DB2 insert row activity.

Step 12: Validate the flow and run the flow

What will happen is when any update or new entry is added in the Accounts Salesforce browser and new row entry will be added to the DB2 database.