SmartETL

Download SmartETL Datasheet (PDF fromat)

Myxa’s SmartETL Utility is a robust and versatile data migration and data synchronization tool. SmartETL allows data to be intelligently moved between two independent data stores (e.g. databases or files). SmartETL will map data even when it is organized differently in the two similar or differing data repositories.

The SmartETL Utility is designed to allow database systems to share common data while they work independently of each other. It is common for organizations to have similar or the same data in many systems. Common data elements that are often needed in multiple systems include:

• contact information (customers, prospects, vendors)
• transaction data (invoices, orders)
• manufacturing data
• product data

Myxa’s SmartETL Utility automates the intelligent sharing of these common data elements. SmartETL will allow data elements to be mapped even when there is not a direct one-to-one mapping of these elements or tables across the data stores. Changes made in one system can be automatically applied to other systems without loss of data and without having to manually update the same data across multiple systems.

The Challenge

The need to maintain accurate and current information across the spectrum of systems is increasingly a major issue for organizations. Moving data from one data source to another one time can be very easy when a simple one-to-one data mapping occurs. Many tools and bulk data load facilities can handle one-time, simple data migration tasks. However, these simple tools are ineffective when there are two database-driven systems that need to share common data regularly — and both systems need to build on this common data independently.

The second challenge lies in running the synchronization routinely — on a daily, weekly or monthly basis. Remote database connections and replication have significant performance and maintenance overhead that makes these solutions unappealing for relatively simple needs. Intelligent synchronization addresses the challenge of moving data between systems and databases — converting data from one table layout to another, moving across database vendors, or even completely differing data storage methods.

The Myxa Solution
Myxa’s SmartETL Utility allows updates to the data to be synchronized. New data from one source is compared with the data in the target, and updates are applied based on a user-defined set of rules. These rules allow the mapping of one or more data fields from a source database into one or more fields in the target database, even if those fields occur in a differing number of tables in both databases. The rule sets allow:

• easy conversion of data types and formats (e.g. date formats, numeric formats)
• concatenation of data fields (e.g. building a full name field from a first name, last name, and middle initial)
• other data conversions (e.g. EBCDIC to ASCII, packed data fields to floating point numbers)

The Myxa SmartETL Utility runs on a UNIX system, and its modular construction allows for rapid adaptation to new database formats or even API and flat file data sources. SmartETL consists of an input/output module to handle reads and writes from each database and a data synchronization module which compares data and determines what data fields need to be updated in the target database. The input/output module utilizes Perl’s DBI to provide database specific routines and provide support for a wide array of DBMS products and versions.

Myxa has been using its SmartETL utility for a wide range of projects where there is a need for intelligently updating and synchronizing data across systems. Myxa is now making this tool available for general use.

Features

• Data mapping controlled through editable ASCII configuration files
• Two-way mapping with independent data migration rules for moving data in either direction
• Data synchronization by updates to the target database, while maintaining the data integrity and relationships of existing data
• Data mapping transforms allow data conversions even when data organizations, formats, and structures differ between systems
• Migrate/synchronize data between different databases (e.g. Oracle to Sybase, DB2 to Oracle, Sybase to Sybase)
• Easy scheduling of regular data updates through UNIX scripts and UNIX ”cron” functions
• Support for fully automated data synchronizations with nominal support and monitoring requirements

System Requirements

• Runs on Unix platforms
  Sun Solaris
  HP-UX
  AIX
  Linux

• Required support software
  Perl
  Perl DBI modules

• Supported databases
  Oracle
  Sybase
  DB2
  MySQL

• Tools/Technologies
  Unix (Solaris, HP-UX, AIX, Linux)
  Perl
  Myxa ToolKits

(go to top of page)

Copyright Myxa Corporation 2001-2006