Customer Case Studies

DPS is a robust product that provides modeling and data migration of legacy ADABAS data into modern RDBMS-based platforms for Internet/Intranet/Business Intelligence applications. DPS's modeling and mapping facility (tRelational) auto-generates complete RDBMS schema from existing ADABAS files and allows for easy mapping of ADABAS fields to already existing data warehouse or ERP schema. After the mapping is completed, DPS can materialize (initially load) and propagate (keep synchronized) the ADABAS data into the RDBMS without requiring direct access to ADABAS. Whether a site is completely replacing a legacy ADABAS system with an RDBMS-based system as was done at the University of Maine, or if the need is a long-term data transfer and synchronization solution for data warehousing/Internet/Intranet/ERP applications, DPS is the answer.

 

John St. Peter, Lead Database Administrator at the University of Maine System, illustrates how DPS, Treehouse Software's best-of-breed product, helped put the University's mainframe system on the fast track to its new home - on time and under budget. The University of Maine System chose DPS for migration of their legacy ADABAS data as part of their plan to move from the mainframe by the end of 2009. John writes to us:

The University of Maine System consists of 7 unique universities with a student body of 32,000. Certain administrative systems are centrally managed for all universities by the University of Maine System.

The large majority of ADABAS files supported three ADABAS applications:

1. The student system (ISIS)

2. The financial aid system (Financier)
   

3. The accounting system
   

Additionally, there were a few other files supporting some smaller in-house applications.

Nearly all students and faculty, and a large number of administrative staff, used systems that accessed the data stored in ADABAS, 24x7. The student and the financial aid systems were the lifeblood of the University system.

The mainframe ADABAS systems consisted of over 200 ADABAS files and 100 million records of data. The University of Maine System had been a Software AG customer since 1987 and used ADABAS and Natural extensively. The student system was written in COBOL, but all reporting was done with Natural, and the financial aid and financials systems were written in Natural.

The mainframe ran both VM/CMS and VSE as operating systems. Depending on the applications, on-line access to the data was through web, IVR, and CICS systems. Batch access was done via CMS and VSE, with all large batch jobs running in VSE.

On Target, and On Track

While our products had served us well, they were well past the end of their useful product cycle. Neither VSE nor ADABAS were seen by ERP vendors as a major program platform. The University of Maine System was concluding a multi-year effort to migrate to an ERP system and when that was finished, the mainframe would be retired. But the data in the ADABAS database was still of value. Whether due to regulatory requirements for retaining data, or because it did not convert readily to the new systems, much of the legacy data had value outside of the new applications. Even data that had been converted, it was decided, had value in its original form for a few more years, and the decision was made to convert virtually all the legacy data to Oracle, with the target system being Oracle version 10.2 running on a Sun Solaris platform.

Fully involved with our ERP implementation, we had neither the staff nor the time to manually do all the preparatory work in migrating the hundreds of files and tens of thousands of fields to Oracle, so it was decided to look for a vendor solution. It was a very easy decision to go with Treehouse Software. They came in with the best price, had the tools to meet our needs, and in our case, had a proven track record, as we had been satisfied long-time users of their TRIM and AUDITRE products for over 20 years.

Race Against Time

At the start of the migration project, we were faced with several challenges:

• We had a tight timeframe. The mainframe went out the door December 31, but the data was still being accessed until mid-December. Therefore, we had to make sure the final push of the data went fine and the data was accurately converted because we would not be able to come back and do it later.
  

• Our legacy data was not as clean as it should be. Not only did some of our older applications not do data integrity checks, but some fields were purposely in a format other than defined binary values in a character field, for example.
  

• Doing anything 200 times takes time. Doing lots of things 200 times takes lots of time.
  

• Our environmental architecture complicated the issue. Our mainframe operating systems and underlying products had essentially been frozen once they were made Y2K-compliant in the late 1990s, so they did not have current features and it was unclear how well they would work with the vendor solutions. Our version of VSE didn't have FTP capabilities for example, so data would have to get moved from VSE to VM/CMS before we could FTP it off the mainframe. Also, our VSE systems had only a small amount of intermediate disk space, so large files would need to be moved in pieces, and their starting point (ADASAV files) would need to be on tape. So the 100 million rows of data would need to be touched several times on the way from the starting destination on ADABAS to the final home on Oracle. Space concerns also meant we essentially had to move one ADABAS file at a time from start to finish.
  

Moving 100 million rows of data seven times, along with the additional rows created to support PEs and MUs, meant pushing more than one billion rows of data around, so we didn't have time to fix problems on the fly during our final push. Treehouse's products had to run quickly, and they had to run well... And they did.

Since some of our systems made good use of the non-relational ADABAS PE and MU features, we didn't know how well or how easily these would convert to Oracle.

Treehouse Software Provides a Smooth and Fast Ride

The Treehouse products were absolutely essential to the success of the project. The modeling features of tRelational made it very straightforward to generate models and DDL for the 300 Oracle tables built from the original 160 ADABAS files that we opted to convert (child tables were created from PE and MU groups in the ADABAS files). Many of our ADABAS files had multiple record types which we could have had the modeling tool easily convert into multiple Oracle tables, but we made a conscious decision to keep our original structure as much as possible. Natural date/time conversions happened by default and worked flawlessly. Working with our special fields that needed to be converted to something other than their default ADABAS format also turned out to be a simple process thanks to the several built-in conversion choices offered. When we chose on occasion to split or merge or take substrings of ADABAS fields when converting them to Oracle, the modeling tool made those actions easy.

The materialization steps also went smoothly for us. Because of our time constraints and because of the number of points our data had to go through, it was important that these steps go quickly and flawlessly during our final push. On our system, DPS could perform the materialization at speeds over 1 million rows per minute depending on the file. We were also able to have DPS work with our largest files by working on just a part of the data with each pass, usually about 2 million records at a time.

We took advantage of another DPS feature to help ensure that our final push would work as smoothly as possible. Ahead of the final push, we had DPS do a practice materialization and report on non-character data in character fields, which could disrupt the later FTP or SQL Loader steps. Identifying and resolving the patterns of bad data turned out to be more time-intensive than we thought and no one solution was right for all data, although one option that DPS could perform, and which we used on some files, was for DPS to clean up the data itself. Using DPS in practice runs to check on bad data meant that data issue surprises were kept to a minimum during our final push to Oracle.

Whether we had design choices with the modeling tool or questions about DPS, Treehouse would respond promptly and accurately, so we were never delayed by the tools.

Success Leads to a Parting of Ways

We couldn't be happier with our experience with Treehouse Software. The products worked as advertised and with no surprises. The tech rep that came on-site to help us install and learn the products was knowledgeable about both his products and our environment.

Just one example of the support that Treehouse Software provided occurred when a useful feature of their modeling program was not available in the version we were using (we needed to use the previous version of the tool because of our environment). On his own initiative, the technical rep onsite contacted support, and within just a few hours, that feature had been retrofitted to the earlier version of the product and the patch was sent to us and applied. I have only rarely experienced a vendor going to those lengths for a customer. One of the drawbacks of the movement off ADABAS and Natural for us is that after 20 years of working with Treehouse we no longer have them as a vendor.