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:
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
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.
- 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.
- 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.
- 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.
