IBM Unveils ETL Solution for DB2 Web Query
March 18, 2015 Alex Woodie
IBM next week will begin delivery of DataMigrator for i, a new extract, transform, and load (ETL) solution unveiled in February that’s designed to work with DB2 Web Query software. DataMigrator for i will give customers a better and more automated way to feed their IBM i-based BI and analytics applications with data originating from (gasp!) outside the platform.
The ongoing explosion of data is driving organizations to adopt new business intelligence and analytics solutions designed to help grow sales, reduce costs, improve marketing, and increase customer satisfaction. While the IBM i server is typically utilized for transaction processing, it can also run analytic workloads, either using third-party analytics software or IBM’s BI offering for the platform, DB2 Web Query.
DataMigrator for i is an add-on tool that works with DB2 Web Query Express and DB2 Web Query Standard Edition version 2.1 or higher. As an ETL tool, the software helps users create, populate, and maintain database tables on DB2 for i from one or more data sources. It can perform bulk loads and incremental updates of data, which makes it useful for maintaining a reporting database such as a data mart or data warehouse, IBM says.
The ETL tool can work with any data available to DB2 Web Query. Existing synonyms, or metadata, that users have defined for DB2 Web Query for i can be used by DataMigrator for i, IBM says. In most cases, the data source will be the DB2 for i database that resides on the same system where DB2 Web Query is running. However, DataMigrator can also be used to import data from one or more remote systems, including DB2 for Linux, Unix, and Windows (cousin LUW), DB2 for System z, as well as Microsoft SQL Server (but only with DB2 Web Query Standard Edition).
Those are the only supported databases at the moment, but perhaps IBM will add more with future releases; the Oracle database or MySQL (the only other officially supported relational database on IBM i) are possible options. As it currently stands, Oracle’s JD Edwards ERP systems can be used as a data source for DataMigrator for i, via JDE adapters (but again, only with DB2 Web Query Standard Edition).
Beyond databases, DataMigrator for i can also tap into flat files sitting in IBM i’s IFS file system. Perhaps even more interesting, DataMigrator can read data stored in local and remote journals. “Journals are particularly useful for incremental maintenance flows where data changes to files/tables can be captured in a very low overhead manner,” IBM says in the DataMigrator for i announcement letter.
The concept of a data flow is central to working with DataMigrator for i. “A data flow defines where the data comes from, how it should be transformed, and into what files the resulting data should be loaded,” IBM says in the announcement letter. Defined data flows can be run immediately or scheduled to run later or on a recurring basis. Users can create as many data flows as they need, and can even run multiple data flows as part of a single overarching process flow that controls when and how the data flows run.
Users work with DataMigrator and define data flows through the Data Management Console, which is a component of the Windows-based DB2 Web Query Developer Workbench. Since they’re working within a familiar interface, that should help users get productive quickly.
The new offering–officially called IBM DB2 Web Query for i DataMigrator ETL Extension–will become available as a PTF starting March 27. Licenses start at $995 for the first Developer Workbench user, and $400 for each additional user. There’s also a $14,000 per-processor charge for the executable component on the IBM i server. For more information see IBM United States Software Announcement 215-056.