WTX EDI and BPM Solutions By Mallibabu: 2016 <h1>WTX EDI and BPM Solutions By Mallibabu</h1>

Learn how to use the WebSphere Transformation Extender Design Studio, Database Interface Designer, and Database Adapter to fetch data from a database table and transform it into a text file.

you will use IBM WebSphere Transformation Extender (TX) resource adapters to retrieve and route data. The adapters provide access to databases, files, messaging systems, enterprise applications, and other data sources and targets. one of the resource adapters as an example, the Database Adapter, and along the way it shows you how to use the WebSphere TX Design Studio and Database Interface Designer.

You should have the following software installed:

WebSphere TX V8.2
DB2

1. Connecting to the database

creates a database named BPMDB with tables ID,User,, and Group in DB2. Type Trees are the data definition files that define the format of the input and output data. Since the input data is being fetched from the User table in BPMDB , you need a type tree that defines the data in that table. Type trees can be automatically generated from databases, queries, stored procedures, and views in the Database Interface Designer. You will use the same type tree for your input and output.

1.1. Testing the connection with the existing database

Open Database Interface Designer: Start => WebSphere Transformation Extender V8.2 => Design Studio => Database Interface Designer.
Right-click Database/Query Files in the Database Interface Designer navigator and select New Database/Query File. Give name as Database_QueryFile1.
Right-click Databases and select New.
In the Database Definition dialog, enter BPMDB as the database name.
In Adapter, select DB2 as database Type and Microsoft Windows as Platform. WebSphere TX offers a number of database adapters, including Oracle, MS SQL Server, Sybase, Informix, and others. In this article you will work with the DB2 database adapter.

Figure 1. Selecting database adapter

Expand Data Source options.

The Database interface Designer option identifies the database you want to access. Select BPMDB from the drop-down list.

The Runtime option identifies the database to access at map runtime from the Map Designer, Command Server, or Launcher. The runtime and development data sources are the same for this article, so select BPMDB.

Expand the Security options. These options specify the user ID and password to connect to the database instance. Enter your user ID and password for DB2.

Click OK to save the database connection information.The Database Definition window should look like this:

Figure 2

Test the connection:

In the Database Interface Designer navigator, right-click Tables and select Generate Tree.
If a dialog window opens with options, it means that a connection has been established.
If a dialog window does not open, then a connection has not been established. Check Security Information to verify the connection:
Figure 3. Generate type tree from Tables wizard

See the tables ID,User, Group, and User Group in BPMDB.

Close the Generate Tree dialog and save the Database Query File as Database_QueryFile1.mdq, which you will use in the next section.

1.2. Generating type trees for database tables

Type Trees can be automatically generated from databases, queries, stored procedures, or views. In the previous section, you established a connection with a database. Now you can reuse the same connection to generate a type tree.
Generating a type tree for the ID table

In the Database Interface Designer, right-click Tables in the navigator and select Generate Tree.
Select the ID table from the Tables dialog.
The File Name field specifies the name of the type tree file to create. Enter Database_QueryFile1.mtt.
The Type option lets you replace an existing type tree file by selecting Overwrite file or Replace existing types, and add new types by selecting Override type. Since you are creating a new type tree, Change the Type option to Overwrite file.
When the Type Tree is generated for a database table, a group named Row is automatically defined by the Database Interface Designer. The Row group format drop-down list lets you specify the format of the Row group as delimited or fixed format. For this exercise, keep the default values.
You can specify Group options such as the delimiter between each field of the record, and the terminator for each record.
The Represent date/time columns as text items option lets you control how date and time data types are created in the type tree. When you select this option, a database column defined with a Date data type is defined in the type tree as a character text string.
The Treat text items as: option lets you specify the language as English (Western) or Japanese if you have the International version of Design Studio installed. Leave this option as Western and click Generate. The Database Interface Designer will produce a type tree that corresponds to the ID table, and notification message will be displayed:

Figure 4. Generating type tree from a table

Click Close to close the window. This dialog provides an overview of the created data types

1.3. Enable tracing in Database Interface Designer

Tracing provides information about the database connection. When tracing is enabled, a Database Interface Designer trace file is automatically created when a type tree is generated. The trace file is saved in the same directory as the database/query file and named using the full name of the database/query file plus the extension .dbl. For example, if the database/query file is named Orders.mdq, the trace file will be named Orders.dbl. You can use tracing in the Database Interface Designer by enabling the trace tools, as shown below:
Figure 5. Tracing tools in database interface designer

1.4. Generating type trees for queries

Type Trees can be automatically generated from databases, queries, stored procedures, and views. In the previous section you established a connection with the database. Now you will reuse the same connection to generate a type tree from a query.
Creating a simple query referencing the ID table in the database

In the Database Interface Designer, right-click Queries in the Navigator and select New.
The query name uniquely identifies the query. Enter MyQuery.
Enter the following SQL statement in the Query window that will select all columns in the ID table: Select * from ID.
Click OK. The query name appears in the Navigator under the Queries subheading.
Generate the Type Tree as shown Figure 6:
Figure 6. Generating type tree for a query

The Generate Tree dialog is the same as when generating a type tree from a table.

Generate the Type Tree and save it as MyQuery.mtt. You will use this type tree in your map in the next section.

Right-click and save the file Database_QueryFile1.mdq.

2. Developing maps in WebSphere TX Design Studio

In Section 2 you will fetch the records from the ID table and write them to a flat file using the MyQuery.mtt type tree, and the Database_QueryFile1.mdq file, which were both generated in Section 1. You will map this type in the input source in the input card.

2.1. Starting WebSphere TX Design Studio and creating an Extender Project

Since you have to work on mapping and transformation, start WebSphere TX Design Studio: Select Start => IBM WebSphere Transformation Extender => Design Studio => Design Studio. Select a work space and close the Welcome view. Create an Extender project as shown in Figure 7:

Figure 7. Create an Extender project

Enter the name as Project and click Finish. A project will be added to your work space.

2.2. Preparing for mapping and transformation

In this section, you will create first the map source file, which is the placeholder for your maps, and then the executable map in the map source file. The executable map can have more than one input and output card. In the input card, you configure your input type tree and the adapter settings, and in the output card, you will configure the output card and adapter settings. After you are done with the input and output cards, you will do the mapping and then build and run the map to check the results. Create a new map source file named db_map.mms in your working directory: right-click the Map Files folder in your Project and select New => Map Source:

Figure 8. Create new Map Source file

Right-click the map source file and add a new map with the name DatabaseMap in the outline view of the Design Studio.
Adding the input card

Add an input card named dbincard.
Select MyQuery.mtt, which you created from the query as the type tree, and select DB Table as Type, as shown in Figure 9 below, because this group represents the entire table and not a single record. If you need to transform a single record, then select the group Row.
Figure 9. Create an input card

Change the Source setting to the adapter Database, because you are going to fetch the input data from the database.

Identify the type of data being used as the data source. When you change the Source setting to Database, the DatabaseQueryFile settings appear in the input card.

The File setting identifies the database/query file (.mdq) that contains the definition for the query. Select Database_QueryFile1.mdq as the File setting.

The Database drop-down list is automatically updated to display all databases defined in the selected file. BPMDB is the only database defined in the file Database_QueryFile1.mdq, so it is automatically selected as the database.

The Query setting identifies the query to use as the data source. If more than one query is defined in the selected database, a drop-down list of all queries is displayed. Select My Query as the Query.

Click OK to save the settings. The input card is shown in Figure 10:

Figure 10. Input card settings

Adding the output card

Add an output card named OutputCard.
Select MyUserQuery.mtt as Type tree and select DB Table as Type.
Select Target as the File adapter, since the output will be written to a text file. Specify the path as c:/examplexsd .txt. Click OK to Save.

2.3. Building and running the map

Now that you have created the input and output cards for the map, you are ready for transformation. Drag and drop the group dbincard from the input card to the output card group rule column:

Figure 11. Mapping the input and output

Right-click the map in the outline view and click Build to compile the map. Then right-click the map and click Run. You should get the message Map completed successfully:

Testing the results
Right-click the map in the outline view and click Run results. Choose your result file and click OK. You will be able to see example xsd.txt in the Design Studio with your records from the ID table that have been transformed into a text file,
as shown in Figure 13:

Conclusion

WebSphere TX resource adapters and the WebSphere TX Database Interface Designer, and showed you how to use WebSphere TX to easily connect to a database and do the transformations by fetching data from the database. It also showed you how to enable tracing in the Database Interface Designer and described basic features of the Design Studio such as type trees and maps.