Simplifying Data Transfers Between AB Suite (or EAE) Databases
By Gary J. Taylor, System Architect, Unisys
Regardless of whether you’re using Agile Business Suite (AB Suite®) or EAE, it’s likely that a time will come when you need to copy data from one database to another.
For instance, maybe you need to transfer data residing on the same host or across host platforms, like from a ClearPath® MCP Runtime database into an AB Suite SQL Server debugger database. Or perhaps you have an EAE on UNIX® database in Oracle and need to move that data to AB Suite and SQL Server.
Unless you’re staying on the same platform and the source and target are identical, it may be difficult and time consuming to copy the data – especially if you have to deal with potentially hundreds of separate tables.
A Simpler Solution
To help you manage situations like these, we developed the AB Suite SSIS Generator, a utility designed to simplify the process of moving data between most EAE and AB Suite platforms. Note that this tool doesn’t actually transfer the data. Instead, it automates the creation of SQL Server Integration Services (SSIS) packages that can then be used to transfer the data.
SSIS is a standard feature of Microsoft® SQL Server®. It provides capabilities for moving, transforming, and manipulating data from virtually any source to any other destination – SQL Server or not. For example, you could use SSIS to move data from an Excel spreadsheet to an Oracle database table.
To use SSIS, you build “packages” that define what you want SSIS to do. Using SQL Data Tools – a graphical design tool that functions as a Visual Studio plug-in – you can quickly design a package that takes data from a source, applies transformations to it, and sends that data its destination. This would be suitable if you wanted to extract MCP data and load it into an AB Suite Debugger database in SQL Server.
The trouble is, this process can become quite laborious if you have to build and maintain packages to manage hundreds of tables. To address this, Microsoft has provided APIs that allow you to programmatically build these packages. Additional APIs facilitate the creation of custom components that further extend the capabilities of SSIS, such as by creating adaptors for a particular data source that isn’t supplied as standard.
The AB Suite SSIS Generator utilizes these APIs when creating SSIS packages via a simple interface – shown above – that allows you to define where the source and destination data is located. Because the utility understands the naming conventions of the both the source and destination data, it can automatically map the data, while coping with the different naming conventions and cases used to define tables and columns. It will also address things like “X” columns due to reserved words in EAE on UNIX and Microsoft Windows, regardless of if the target database has the “X” columns.
A Flexible Tool
The following table details all of the possible source and target destinations that can be used by the utility, with any combination of source and target allowed.
For sources or destinations that SSIS does not natively support, such as EAE Developer Test, custom adaptors have been built to provide that functionality, as have capabilities that convert the data to EBCDIC for use with AB Suite Debugger databases.
The utility takes a list of tables via a text file as input, allowing you to define which tables need transferring.
This list can be created manually or generated from an AB Suite Debugger or Runtime environment, depending on your needs.
The packages the utility builds are optimized for maximum performance wherever possible. For example, when targeting SQL Server, indexes are automatically disabled before the transfer and then re-enabled at the end. These packages are also standard SSIS packages, allowing them to be viewed or manipulated using SQL Data Tools, as shown in the following diagram:
The utility only builds one package per table. You then have to run the packages to actually move the data. You can do this manually via SSIS or automate the process using a VB Script that runs multiple packages concurrently, starts another package as soon as the prior one completes, and even retries any failed packages in the event of a network error.
A number of clients worldwide have used the utility to move their data for migration purposes or to populate Debugger databases. The largest database we moved consisted of 500-plus tables and 4.5TB of data, or over a billion rows. But many other sites use the utility to move just a few GB every day, so it is both powerful and flexible.