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.

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.

Websphere Transformation Extender Partner Manager Tutorial- Define Database in Partner Manager

1. Start Partner Manager. From the Start menu, select Programs → IBMWebSphere Transformation Extender n.n → Trading Manager → PartnerManager.

2. Click on the Utilities icon in the Partner Manager tool bar.

3. On the Utilities navigator, click Database Settings. The Partner ManagerDatabase Selection dialog appears.

4. Select Microsoft Access from Database Vendor drop-down list as shown below.5. Select TradingManagerTutorial from the DSN drop-down menu.

6. Click OK.

7. Partner Manager will restart to activate the new database.

Websphere Transformation Extender Partner Manager Tutorial- Define ODBC Data Source

Trading Manager uses a relational database to store information about tradingpartners, routing instructions, access privileges, document tracking and tradingrelationships. A sample Microsoft Access database is included with the PartnerManager installation.

1. From the Windows Start menu, select Settings → Control Panel →Administrative Tools → Data Sources ODBC.The ODBC Data Source Administrator dialog opens.

2. Select the System DSN tab, and then click the Add button.The Create New Data Source dialog opens.

3. Select Microsoft Access Driver (*.mdb) from the list box, and then click Finish.The ODBC Microsoft Access Setup dialog opens.

4. In the Data Source Name field, enter the following information:TradingManagerTutorial5. Click Select.The Select Database dialog opens.

6. Navigate to C:\install_dir\<tmgr_vn.n\pmgr and select the tmgrnn.mdb file.

7. Click OK.The Select Database dialog closes. The new entry, TradingManagerTutorial, isnow displayed in the ODBC Data Source Administration dialog in the SystemData Sources list box.

8. Click OK.The ODBC Data Source Administrator dialog closes and the System DataSource Name is defined.