Transform and model your DB2 data using WebSphere Transformation Extender

An application takes data as input for business logic processing in a specific format. The format of the data could be text, XML, EDIFACT, X12, flat files, and so on. The database contains the raw data that needs to be modeled and converted into a specific data format so that the application can use it. For example, a web services-based application requires data in XML format for processing. To achieve this business requirement, you can use WebSphere Transformation Extender for data modeling and data transformation.
Figure 1 shows how WebSphere Transformation Extender performs transformation and routing of data in any format from source system to target system in real-time environments.

Figure 1. Working functionality of WebSphere Transformation Extender

The source system can include files and databases. After retrieving the data from its source, WebSphere Transformation Extender transforms the data and routes it to any number of target systems where it is needed, for example, legacy, J2EE, or web services applications, providing the appropriate content and format for each target system.
WebSphere Transformation Extender uses type trees and maps for defining input data, output data, and transformation logic. A type tree describes the hierarchical structure of a data format. There are two type trees, input and output. The input type tree describes input data in a hierarchical structure, while the output type tree describes output data in a hierarchical structure.
A map is a hierarchical structure that contains input and output cards and encapsulates the rules for data transformation from one data format to another. These cards represent data objects. The input card of a map is associated with a single input terminal of a map, and the output card of a map is associated with a single output terminal of a map. Each input type tree is configured with an input card, and each output type tree is configured with an output card with adapter settings. Each input type tree is mapped to an output type tree using transformation rules. The adapter setting contains the information (location, type, platform, and so on) of a source system and a target system. After running a map successfully, data from a source system is transformed and routed to a target system.
WebSphere Transformation Extender uses load, update, and delete operations during data modeling.

• In a load operation, WebSphere Transformation Extender exports data from a source system, transforms the data, and writes the data to a target system using transformation rules.
• In an update operation, WebSphere Transformation Extender exports data from a source system, transforms the data, and updates the data to a target system using transformation rule.
• In a delete operation, WebSphere Transformation Extender exports data from a source system, transforms the data, and deletes the data from a target system using transformation rules.

This article describes the complete process of transforming DB2 data using WebSphere Transformation Extender with the following three scenarios:

• Database to file system: WebSphere Transformation Extender performs transformation and routing of data from database tables to files. This article describes transformation from a IBM DB2 database to a file.
• Database to database (DB2, ORACLE, Sybase, and so on): WebSphere Transformation Extender performs transformation and routing of data from any database table to any other database table. In this article, we describe transformation from a DB2 database to another DB2 database.
• File system to database: WebSphere Transformation Extender performs transformation and routing of data from file to any database. In this article, we describe transformation from a file to a DB2 database.

Software requirements
This article uses the following software.

• WebSphere Transformation Extender V8.3
• IBM DB2 V9.7

However, these instructions will work with all versions of WebSphere Transformation Extender V8.x and with other databases such as ORACLE, Sybase, and Informix as well.

---

Back to top

Database to file system
The following sections demonstrate how to use the WebSphere Transformation Extender framework to export data from a DB2 database, perform data modeling, and route the data to a file system. In this example, the source is EMPLOYEE table and the target is a text file called output.txt.
Creating the database object
Use the following SQL statements to create a database SAMPLE with table EMPLOYEE, as shown in Listing 1.

Listing 1. Creating the database and employee table

CREATE DATABASE SAMPLE AUTOMATIC STORAGE YES ON 'C:\' DBPATH ON 'C:\' USING CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM PAGESIZE 4096; CREATE TABLE ADMINISTRATOR.EMPLOYEE (EMPLOYEE_ID CHARACTER (10) NOT NULL, FIRST_NAME VARCHAR (40), LAST_NAME VARCHAR (40), MANAGER_ID CHARACTER (10) NOT NULL, MANAGER VARCHAR (50), CONSTRAINT CC1317720073859 PRIMARY KEY ( EMPLOYEE_ID, MANAGER_ID) ) ;

The Employee table with the data is shown in Table 1.

Table 1. EMPLOYEE table with data

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID	MANAGER
071D	F_Name1	L_Name1	072E	Manager1
072D	F_Name2	L_Name3	073E	Manager2
073D	F_Name3	L_Name3	074E	Manager3
074D	F_Name4	L_Name4	074E	Manager3
075D	F_Name5	L_Name5	072E	Manager1

Attaching the database
To attach the database to WebSphere Transformation Extender using Database Interface Designer, perform the following steps.

1. Open Database Interface Designer by clicking Start > WebSphere Transformation Extender V8.3 > Design Studio > Database Interface Designer, as shown in Figure 2.
   
   Figure 2. Database Interface Designer
   
   
   
2. Right-click Database/Query Files in the Database Interface Designer navigator and select New Database/Query File, as shown in Figure 3. Name it DB2_txt.
   
   Figure 3. New Database/Query File
   
   
   
3. Right-click Databases and select New as shown in Figure 4.
   
   Figure 4. Select Database
   
   
   
4. In the Database Definition dialog, type SAMPLE in the Database Name field as shown in Figure 5.
   
   Figure 5. Database Adapter List
   
   
   
5. In the Adapter field, select DB2 as database type, and Microsoft Windows as platform. WebSphere Transformation Extender offers a number of database adapters, including Oracle, MS SQL Server, Sybase, and Informix. In this article, DB2 is selected as database adapter. The drop-down list for database adapter is shown previously in Figure 5.
6. Expand Data Source as shown in Figure 6.
   
   Figure 6. Data Source and Security fields
   
   
   
7. The Database interface Designer option identifies the database you want to access. Select SAMPLE from the drop-down list as shown in Figure 6.
8. The Runtime option identifies the database to access at runtime from the Map Designer, Command Server or Launcher. The runtime and development data sources are same for this article, so select SAMPLE as shown previously in Figure 6.
9. Expand the Security options. These options are used to specify the user ID and password to connect to the database instance. Type your user ID and password for DB2. Click OK to save the database connection information as shown previously in Figure 6.

Creating the input type tree for the database table
The input type tree describes the hierarchical structure of input data format. You can create the input type tree manually or automatically using Database Interface Designer. Type trees can be automatically created from databases, queries, stored procedures, or views using Database Interface Designer. Database connections can be established according to the previous section. The following steps are used to create input type tree using tables.

1. In the Database Interface Designer, right-click Tables in the navigator, and select Generate Tree as shown in Figure 7.
   
   Figure 7. Generate Tree from Tables
   
   
2. Select the EMPLOYEE table from the Tables dialog as shown in Figure 8.
   
   Figure 8. Configuration of Generate Tree from Table
   
   
   
3. The File Name field specifies the name of the Type tree file to create. Type Emp_Query.mtt in the File Name field as shown in Figure 8.
4. Also under Type options select the Override type check box, as shown previously in Figure 8.
5. 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 drop-down list lets you specify the format of the Row group as delimited or fixed format. For this example, keep the default values as shown previously in Figure 8.
6. Specify Group options such as the delimiter between each field of the record, and the terminator for each record and release character as shown previously in Figure 8.
7. For this example, keep the Represent date/time columns as text items default values for National and Data Language fields as shown previously in Figure 8.
8. Click the Generate button. As shown in Figure 9, the Database Interface Designer will produce a type tree that corresponds to the EMPLOYEE table and the “Command file completed successfully” notification message will be displayed.
   
   Figure 9. Create Input Type Tree from Employee table
   
   
   

Generating type trees for queries
In the previous section you established a connection with the database. Now the same connection is used to generate a type tree from a query. The query can be simple, complex or join, which means that type tree can be generated that references multiple tables. In this section, the type tree is created using a simple query referencing the EMPLOYEE table in the database SAMPLE.

1. In the Database Interface Designer, right-click Queries in the Navigator and select New as shown in Figure 10.
   
   Figure 10. Create new query
   
   
2. The query name uniquely identifies the query. Type Emp_Query in the name field as shown in Figure 11.
   
   Figure 11. New Query screen
   
   
3. Type select * from EMPLOYEE as the SQL statement in the Query window, which will select all columns in the EMPLOYEE table as shown previously in Figure 11.
4. Click OK. The query name appears in the Navigator under the Queries subheading, as shown previously in Figure 11.
5. Select Generate Tree, as shown in Figure 12. The Generate Tree dialog is the same as when generating a type tree from a table.
   
   Figure 12. Generating type tree for a query
   
   
   
6. Generate the type tree and save it as Emp_Query.mtt. This type tree will be used by map in the next section.
7. Right-click DB2_txt.mdq and save it. DB2_txt.mdq is an XML file that contains the information from the database. Figure 13 shows the content of the DB2_txt.mdq file.
   
   Figure 13. XML format of Database_QueryFile1.mdq
   
   

Output type tree
Output type tree describes a hierarchical structure of output data format. In this example, Emp_Query.mtt is used as output type tree which is created in the previous section.
Transformation logic implementation in WTX Design Studio using map
The following steps are for the map development of this scenario.

1. Start WebSphere TX Design Studio.
2. Create an Extender Project by launching WebSphere TX Design Studio for mapping and transformation. Then select Start > IBM WebSphere Transformation Extender > Design Studio. Select a workspace and close the Welcome view.
3. Create an Extender project as shown in Figure 14.
   
   Figure 14. Create an Extender Project
   
   
   
4. Enter the name as TestProject, and then click Finish. A project will be added to your workspace.
5. Develop the map with the following steps. The map source file will be created first. The following is the structure for the executable map node in the map source file. Configure input type tree in input card with the adapter settings. Configure output type tree in output card with the adapter settings. Define the transformation rule, and build and run the map to check the results.
   1. Right-click the Map Files folder in your TestProject and select New > Map Source, as shown in Figure 15.
      
      Figure 15. Create new Map Source file
      
      
   2. Right-click the Map source file and create a new Map source with the name EmpMap.mms in the outline view of the Design Studio. Then click Finish.
      
      Figure 16. Map Node Creation
      
      
      
   3. Create a map called DB2Totxt by selecting New Map from the context menu of the Map source.
6. Add the input card with the following steps.
   1. Add an input card called InputDBCard in CardName field as shown in Figure 17.
      
      Figure 17. Input Card Settings
      
      
      
   2. Select Emp_Query.mtt in TypeTree and select DBTable in Type field because this group represents the entire table and not a single record as shown previously in Figure 17.
   3. Select Emp_Query.mtt in TypeTree and select DBTable in Type field because this group represents the entire table and not a single record as shown previously in Figure 17.
   4. 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 as shown previously in Figure 17.
   5. The File setting identifies the database/query file (.mdq) that contains the definition for the Table and query. Select DB2_txt.mdq as the File setting which is shown previously in Figure 17.
   6. The Database drop-down list is automatically updated to display all databases defined in the selected file. SAMPLE is the only database defined in the file DB2_txt.mdq so that it is automatically selected in the database field as shown previously in Figure 17.
   7. The Query setting identifies the query to use as the data source as shown in Figure 17. If more than one query is defined in the selected database, a drop-down list of all queries is displayed.
   8. Click OK to save the settings as shown previously in Figure 17.
7. Add the output card with the following steps.
   1. Add an output card named OutputTXTCard as shown in Figure 18.
      
      Figure 18. Output Card Setting
      
      
   2. Select Emp_Query.mtt in TypeTree and select DBTable in Type field as shown previously in Figure 18.
   3. Select Target as the File adapter because the output will be written to a text file. so keep c:/output.txt in the path field as shown previously in Figure 18.
   4. Click OK to save.

Building and running the map
After successfully configuring the map input and output cards, drag and drop the group InputDBCard from the input card to the output card group rule column as shown in Figure 19.

Figure 19. Mapping between input card and output card

Right-click the map in the outline view and click Build to compile the map, then right-click the map again and click Run. You should get the message map completed successfully as shown in Figure 20.

Figure 20. Run the map

Verifying the result
Right-click the map in the outline view and click Run results. Choose your result file and click OK. As shown in Figure 21, you will be able to see output.txt in the Design Studio with your records from the EMPLOYEE table that have been transformed into a text file.

Figure 21. Output File

In this example, simple mapping is used so that the same data from the EMPLOYEE table is transformed to a text file. However, you can manipulate data using the feature and function of WebSphere Transformation Extender according to your requirements.

---

Back to top

Database to database, such as DB2, Informix, Oracle, or Sybase
The following sections demonstrate how to use WebSphere Transformation Extender framework to export data from a DB2 database, perform data modeling, and route the data to another database table. In this example, the source is the EMPLOYEE table and target is the EMP_OUTPUT table in the SAMPLE database.
Creating the database objects
Use the following command to create table EMP_OUTPUT in the SAMPLE database as shown in Listing 2.

Listing 2. Creating the output table in the sample database

CREATE TABLE ADMINISTRATOR.EMP_OUTPUT (EMPLOYEE_ID CHARACTER (10) NOT NULL , FIRST_NAME VARCHAR (40) , LAST_NAME VARCHAR (40) , MANAGER_ID CHARACTER (10) NOT NULL , MANAGER VARCHAR (50) , CONSTRAINT CC1317720073860 PRIMARY KEY ( EMPLOYEE_ID, MANAGER_ID) ) ;

Attach to database
Use the instructions in the previous section to attach database SAMPLE with table EMPLOYEE and EMP_OUTPUT.
Create the input type tree of database table (EMPLOYEE)
The Database Interface Designer will produce a type tree as shown previously using EMPLOYEE table. An alert message will be displayed that employee.mtt is created successfully.
Create the output type tree of database table (EMP_OUTPUT)
The output type tree can be created from database SAMPLE and table EMP_OUTPUT with the steps mentioned previously. In this example, you use employee.mtt for output type tree. The employee.mtt is generated in the previous section.
Transformation logic implementation in WTX Design Studio using map
In this section, a map named DB2ToDB2 is created with the detail steps mentioned in the Transformation Logic Implementation in WTX Design Studio using Map section. You will develop the map, configure input Type Tree in input card with the adapter settings, configure output Type Tree in output card with the adapter settings, define the transformation rule, then build and run the map to check the results.

1. Add the input card as follows. The input card setting is similar to what is shown in Figure 17 previously.
   
   1. Add an input card named InputDBCard.
   2. Select employee.mtt, created previously.
   3. Select Type in the DBSelect field.
   4. Change the Source setting to database adapter.
   5. Change the Source setting to Database, the DatabaseQueryFile settings appear in the input card.
   6. The File setting identifies the database/query file (.mdq) that contains the definition for the query. Select db2_db2.mdq as the file setting.
   7. The Database drop-down list is automatically updated to display all databases defined in the selected file. SAMPLE is the only database defined in the file db2_db2.mdq so that it is automatically selected as the database.
   8. 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.
   9. Click OK to save the settings.
2. Add the output card as follows.
   
   1. Add an output card named OutputDBCard as shown in Figure 22.
   2. Select employee.mtt, as shown in Figure 22.
   3. Select Type in DBSelect field as shown in Figure 22.
   4. Change the Source setting to the adapter Database as shown in Figure 22.
      
      Figure 22. Output Card Setting
      
      
      
   5. The File setting identifies the database/query file (.mdq) that contains the definition for the query. Keep db2_db2.mdq in the File Path as shown previously in Figure 22.
   6. The Database drop-down list is automatically updated to display all databases defined in the selected file. SAMPLE is the only database defined in the file db2_db2.mdq so that it is automatically selected as the database.
   7. Enter Table name as EMP_OUTPUT which is shown previously in Figure 22.
   8. Click OK to save the settings. The output card setting is shown previously in Figure 22.

Building and running the map
After successfully configuring the input card and output card of the map, drag and drop the group InputDBCard from the input card to the output card group rule column. The mapping between input and output card is similar to what is shown in Figure 19.
Right-click the map in the outline view, click Build to compile the map, click Run to execute the map. You should get the message map completed successfully which is similar to Figure 20.
Verifying the result
EMP_OUTPUT table is empty before running the map as shown in Figure 23.

Figure 23. EMP_OUTPUT Table is empty

Data in EMP_OUTPUT table after running the map successfully which is shown in Figure 24.

Figure 24. EMP_OUTPUT table with modeled data

In this example, simple mapping is used so that the same data from the EMPLOYEE table is modeled to EMP_OUTPUT table at run time. However, we can manipulate data using the feature and function of WebSphere Transformation Extender according to our requirement.

---

Back to top

File system to database
This section describes WebSphere Transformation Extender framework to read data from input file, perform data modeling, and store the data to a database table. In this example, the source is input.txt file and the target is EMP_OUTPUT table of SAMPLE database.
Creating the input file
The input data is read from a text file called input.txt as shown in Listing 3.

Listing 3. input.txt

071D |F_Name1|L_Name1|072E |Manager1 072D |F_Name2|L_Name2|073E |Manager2 073D |F_Name3|L_Name3|074E |Manager3 074D |F_Name4|L_Name4|074E |Manager3 075D |F_Name5|L_Name5|072E |Manager1

Create the target database and table
The following commands are used to create EMPLOYEE table in SAMPLE database, as shown in Listing 4.

Listing 4. Creating the target database and table

DROP TABLE EMPLOYEE; CREATE TABLE ADMINISTRATOR.EMPLOYEE ( EMPLOYEE_ID CHARACTER (10) NOT NULL , FIRST_NAME VARCHAR (40) , LAST_NAME VARCHAR (40) , MANAGER_ID CHARACTER (10) NOT NULL , MANAGER VARCHAR (50) , CONSTRAINT CC1317720073859 PRIMARY KEY ( EMPLOYEE_ID, MANAGER_ID));

Attach to database
Use the details from the first section to attach database SAMPLE with table EMPLOYEE.
Create the output type tree of database table (EMPLOYEE)
The following steps are used to create output type tree which is similar to the steps shown previously in section 1.

1. In the Database Interface Designer, right-click Tables in the navigator and select Generate Tree.
2. Select the EMPLOYEE table from the Tables dialog.
3. The File Name field specifies the name of the type tree file to create. Type employee.mtt in File Name field.
4. Change the Type option to Override type.
5. The Database Interface Designer will produce a type tree that corresponds to the EMPLOYEE table, and alert message will be displayed that employee.mtt is created successfully.

Create the input type tree of database table (EMP_OUT)
Use the same type tree generated in the previous section.
Transformation logic implementation in WTX Design Studio using map
In this section, a map named as TxtToDB2 is created with the detailed steps described in the first section. You will develop the map by configuring Input type tree in input card with the adapter settings, configure output type tree in output card with the adapter settings, define the transformation rule, then build and run the map to check the results.

1. Add the input card. The input card setting is similar to what is previously shown in Figure 17.
   1. Add an input card named InputTXTCard.
   2. Select employee.mtt, which you created previously.
   3. Select Type as DBTable.
   4. Change the Source setting to adapter File.
   5. Enter the file location, such as input.txt.
   6. Click OK to save the settings.
2. Add the output card. The output card setting is similar to what was previously shown in Figure 18.
   1. Add an output card named OutputDBCard.
   2. Select employee.mtt.
   3. Change the Source setting to the adapter Database.
   4. 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.
   5. The File setting identifies the database/query file (.mdq) that contains the definition for the query. Select txtToDB2.mdq.
   6. The Database drop-down list is automatically updated to display all databases defined in the selected file. SAMPLE is the only database defined in the file txtToDB2.mdq, so it is automatically selected as the database.
   7. 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 Employee as the Query.
   8. Click OK to save the settings.

Building and running the map
After successful configuration of input card and output card, drag and drop the group InputTXTCard from the input card to the output card group rule column. The mapping between input and output card is similar to what was previously shown in Figure 19.
Right-click the map in the outline view, click Build to compile the map, then click Run to execute the map. You should get the message "Map completed successfully" which is similar to what was shown in Figure 20.
Verifying the result
Before running the map, EMPLOYEE table contained zero records. After running the map successfully, the data from the text file is routed to EMPLOYEE table as shown in Figure 25.

Figure 25. EMPLOYEE table with modeled data

In this example, simple mapping was used so that the same data from the text file was transformed to EMPLOYEE table, but you can manipulate data using the feature and function of WebSphere Transformation Extender according to your requirement.

---

Back to top

Conclusion
This article has shown how WebSphere Transformation Extender can be used with DB2 database to generate data in different formats. After reading this article, you should be able to use a database with WebSphere Transformation Extender for data modeling to quickly transform the data in a specific format, and in a simple manner.

Resources
Learn

• Read more about WebSphere Transformation Extender product page, including product descriptions, product news, training information, support information, and more.
  
• Visit the DB2 V9.7 Information Center to learn more about IBM DB2 V9.7.
  
• Visit the WebSphere Transformation Extender information center to learn more about WebSphere Transformation Extender.
  
• Find out the WebSphere Transformation Extender requirements.
  
• Stay current with developerWorks technical events and webcasts focused on a variety of IBM products and IT industry topics.
  
• Attend a free developerWorks Live! briefing to get up-to-speed quickly on IBM products and tools as well as IT industry trends.
  
• Follow developerWorks on Twitter.
  
• Watch developerWorks on-demand demos ranging from product installation and setup demos for beginners, to advanced functionality for experienced developers.
  

Get products and technologies

• Visit the WebSphere trial downloads page to see the most popular WebSphere trial downloads.
  
• Download the free IBM DB2 V9.7 Enterprise Edition trial version.
  
• Visit the WebSphere Transformation Extender support page for information on different support options.
  
• Build your next development project with IBM trial software, available for download directly from developerWorks, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.
  

Discuss

• Participate in the discussion forum.
  
• Participate in the DB2 Discussion Forum.
  
• Participate in the WebSphere Transformation Extender Discussion forum.
  
• Check out the developerWorks blogs and get involved in the developerWorks community.
  

About the author

Anuruddha Kumar Pandey works for IBM India software Labs as a Software Engineer. He has been working on the DB2 Tools Continuing Engineering team. He has completed a Master Of Technology degree from Indian Institute of Information Technology and Management, Gwalior.