In 1994 with more than 100 years of business experience, Gordon Food Service (GFS) entered the Canadian marketplace through the merger of some significant businesses in the food service industry. The company's explosive growth required it to handle more products, provide more services, and satisfy more customers' needs than ever before.

GFS Canada turned to HiT Software for its data-integration needs as a key component in its plan to deliver an enterprise-wide business intelligence (BI) solution for all divisions. The company is now using HiT Software's DBMoto and Ritmo/i5 .NET managed data provider to continuously mirror data from DB2 on its P30 iSeries source system to its SQL Server 2005-based Data Warehouse Staging Area, which is hosted on an IBM X366 2 CPU (dual core) x64-bit Windows 2003 R2 Server.

HiT Software's DBMoto, as shown in Figure 1, tackles realtime data replication and data integration across multiple relational databases residing on any hardware platform (IBM DB2, Oracle, Microsoft SQL Server, Sybase ASE, MySQL, SolidDB for MySQL, SQL Anywhere, Cloudscape, Informix, Ingres, PostgreSQL, EnterpriseDB, Gupta SQLBase, MS Access, Firebird, Solid, and ANTs). Based on Microsoft's .NET framework and fully customizable, DBMoto provides a high-performance, standards-based, secure system for companies to integrate data from relational databases with e-commerce and data warehouse applications.

Stuart Bestbier, project manager for the delivery of the BI solution for GFS Canada, managed the design, development, and implementation of the enterprise-wide solution. The project included selecting and executing the necessary BI software and supporting infrastructure. Specifically, the purpose of the GFS Canada BI venture was to implement the necessary data warehouse processes, tools, and techniques to enable the sales and marketing business areas to perform margin and profitability analysis for product items, vendors, and customers.

The completed solution supports analysis, reporting, and decision-making across all divisions of GFS Canada. The project manages approximately 50 tables with between 20 and 230 columns and 26 and 150 million records per table. The overall number of new transactions per month runs at about 3 million, translating to roughly 100,000 primary business transactions per day with many hundreds of thousands of supporting transactions every month.

The GFS Canada solution uses continuous one-way mirroring of DB2 source data to the Data Warehouse Staging Area with controlled stops and restarts. Mirroring is set to run every 60 seconds. The use of HiT Software's DBMoto helped the company overcome challenges so that:

• Custom code is no longer needed to identify source data changes from one day to the next.
• Source data tables do not need to be accessed directly, thus minimizing the potential impact to end users.

Additionally, the implementation using DBMoto offered GFS Canada the following advantages:

• DBMoto minimizes the impact to the mission-critical production source system and has no negative impact on end-user access to production data.
• It provides the flexibility to automatically stop and restart the replication process in order to force a "point of quiescence" in the Data Warehouse extract process.
• DBMoto enables the replication of only the changes to source data (i.e., the deltas) from one Data Warehouse extract to the next.
• It offers a 100 percent reliable mechanism for capturing changes to source data and includes a verbose record of all inserts, updates, and deletes.
• Finally, DBMoto meets the goal for every BI solution -- product performance. The volume of data that GFS Canada needed to manage required a fast, reliable data-replication capability.

DBMoto's Enterprise Manager, as shown in Figure 2, includes wizards that help users easily connect to and identify source and target databases, create target tables, and set up replication processes. It offers a graphical point-and-click interface that makes it easy to drill down into databases, tables, and columns to understand data elements. DBMoto was specifically designed to replicate data between databases in "snapshot," "incremental," or "synchronization" modes.

Unlike other replication products, DBMoto provides unlimited control over data replication and transformation using scripting. It supports two mapping methods -- automatic mapping, in which source field values are duplicated for the target table, and manual mapping -- and both methods can be optionally controlled via scripts. DBMoto also includes a powerful expression generator for interactive data transformation. It automatically generates replication events during operation, letting database administrators define responses to events with scripts and to configure DBMoto "send e-mail" notifications if critical events occur.

DBMoto uses HiT Software's Ritmo/i5, a Windows .NET 100 percent-managed data provider, to access data running on the System i, iSeries, and AS/400. Leveraging the native IBM Optimized Database Server protocol, Ritmo/i5 communicates with native i5/OS and OS/400 programs and does not require any additional DB2 server software. HitHiT Software's products provide a set of easy-to-use, powerful, enterprise-class tools with a proven record of mission-critical applications that bridge diverse data sources such as IBM, Microsoft, and Oracle. HiT Software's standards-based products enable seamless collaboration within enterprises and among business partners.

Bestbier reports that the technical support team at HiT was both knowledgeable and helpful during project development, particularly when the GFS Canada group was coming up-to-speed with the product features and capabilities. HiT Software technical support is readily available from any customer location, and the company sets a high priority on resolving customer issues.

HiT Software has more than 10 years of experience in tailoring, marketing, and selling database integration products for IBM DB2 on the System i, z/OS and AIX, and Microsoft Windows .NET environments and Java in addition to a dedicated relationship with a large customer base in more than 40 countries. Clients span large, medium, and small companies across all industries (including General Electric, IBM, the U.S. Navy, Capital One, MetLife, Silicon Valley National Bank, State Farm, Kemper Securities, Bourse of Luxembourg, and GE Financial).