The Miami University, Oxford Ohio (MU) was engaged in a departure from the IBM mainframe platform. MU released a Request For Proposal (RFP) for the ADABAS to ORACLE Conversion – Alumni Database. TSI responded to the bid and was awarded a service contract to perform the conversion.

The following is a recent discussion between William Custer, MU Project Manager, and Daniel Sycalik, TSI Project Manager.

Bill, can you tell us a little about Miami University?

We are a public university with a large main campus, two regional campuses, and a campus in Luxembourg. In all, there are about 16,000 students. Our origins in 1809 date back to the Northwest Ordinance. MU made the top 100 in ZDNet's Ultimate College Guide list of the Most Wired Campuses last year.

Bill, can you describe the ADABAS to ORACLE - Alumni Conversion project?

The Alumni Conversion project was part of a larger Y2K conversion effort. Several mission critical business applications were not Y2K compliant. To meet our deadlines, we purchased UNIX/ORACLE based software. The plan called for the elimination of our IBM mainframe and shifted the cost savings to our UNIX platforms. Our Alumni system was one of several systems that were removed from the IBM mainframe.

---

MU's “Project Scope” Miami University has approximately 40 ADABAS files residing on an MVS Platform that need to be converted to ORACLE 7.3 format on a UNIX AIX platform. The University is seeking a fixed bid to do this conversion work. The following will be included in the work:

Install and configure the conversion routines: tRelational and DPS will be installed on the Client platform including standardized routines to process and convert ADABAS data.

Verify that the FDTs agree with the DDMs: tRelational provides for the capture of PREDICT and FDT definitions and resolution of discrepancies. The implemented files are the basis for the explicit ADABAS field to ORACLE column mapping.

Check PE/MU counts/occurrences between FDT and DDM and resolve discrepancy with customer: tRelational provides for statistical data analysis of MUs, PEs, candidate VARCHAR columns and descriptors. These statistics provide for improved modeling of the ADABAS data. The analysis processing is the only direct access to the ADABAS Data and Associator with read-only access.

ORACLE column names are to come from the DDMs: tRelational provides for the automatic generation of tables based on the ADABAS file structures. The column names are based on the ADABAS field names. If required, tRelational provides for standard ADABAS-to-RDBMS naming conversion.

Convert each PE or MU into a separate ORACLE table with primary/secondary key relationships to the main table: tRelational's automatic generation prompts for primary key column(s) and generates a child table for each MU and PE with a Foreign Key relationship to the parent table. The automatic generation may also generate a Primary Key to the child table(s) by adding a new DPS sequence column based on the MU or PE occurrence value.

Convert date formats to a format suitable to ORACLE. ADABAS data has several different date formats including: (a8), (n8), (a4), (n4), and ADABAS date format: tRelational supports date mask entry for ADABAS fields that are not NATURAL date datatypes. DPS may be customized by creating an External Transformation Routine (ETR) to process non standard date values and tRelational enables mapping ETRs to ADABAS source field(s).

Convert data types to a format suitable to ORACLE: tRelational performs automatic conversion of ADABAS datatypes to ORACLE datatypes. Handling of null values will be agreed upon. tRelational/DPS supports three options for NULL processing: null when null, null when empty and never consider null.

Several ADABAS files contain approximately 10 logical tables each with its own DDM. Vendor is to separate each logical file into a separate ORACLE table: tRelational provides automatic generation of table(s) based on user views and/or based on grouped fields.

Generate ORACLE keys from existing ADABAS data fields where possible with uniqueness: tRelational provides for statistical analysis for Descriptors, including Super Descriptors, for uniqueness. If the data does not represent a unique primary key, the ADABAS ISN may be selected as the primary key.

FTP the load file to UNIX AIX: tRelational generates the ORACLE Data Definition Language (DDL) to FTP to the target platform to create the table structures including Primary Key, Foreign Key and column not null constraints. DPS generates two output datasets to FTP to the target platform, the load data and the ORACLE SQL Load control statements. The load data is tab delimited to optimize space requirements.

Load data to ORACLE 7.3.4 tables on Unix AIX: Refer to prior bullet.

Verify that the data in the resulting ORACLE tables matches the data in the original ADABAS files: DPS provides summary statistics by table that may be verified against the SQL load log statistics.

Remote access will be provided for the vendor to run jobs: Remote access will be required for the mainframe, ADABAS/NATURAL and TSO (ISPF) to perform the modeling, mapping, analysis and to execute the DPS Materialization jobstreams. Remote access will be required to the AIX platform to perform the ORACLE load functions.

---

What motivated MU to distribute the RFP for the ADABAS to ORACLE – Alumni Conversion?

We considered doing the conversion in-house, but two factors influenced us to do the RFP. First, key personnel that we needed to convert the Alumni data in-house were also the key personnel on other parts of our Y2K project. Second, we liked the idea of purchasing a proven methodology; we thought it would reduce the possibility of error.

What alternative solutions did you consider for the ADABAS conversion effort?

We considered converting the data in-house, and we considered other vendors.

What were the primary factor(s) that prompted MU to select Treehouse's services?

We had followed Treehouse Software as a company for some years through SAG conferences and believed them to be significant and reputable players in that market. We also had experience with other Treehouse products. A primary consideration was the Treehouse tool set that would enforce standard rules and methodology. We found their staff to be knowledgeable and responsive to our technical questions.

How does this conversion project affect your current ORACLE systems?

The purpose of the conversion was to load our alumni legacy data to ORACLE and make it available for reporting in ORACLE, where the rest of our data was being loaded. The Treehouse conversion also eased the data load into the new on-line packaged software files in ORACLE.

What were the largest obstacles in the conversion project?

Our staff was busy working on many projects simultaneously, including the Treehouse conversion project. We scrambled to avoid being a bottleneck for Treehouse.

How much did the tRelational/DPS product solution factor in the deliverables/success of the project?

It was a significant factor in the project. It provided consistent rules and methodology. We had data in a format we did not fully understand, and the tRelational/DPS product set allowed us to fix our mistaken assumptions and reprocess the data quickly and accurately. It also discovered vendor encryption routines that needed to be decrypted.

In your opinion, how much did TSI contribute to meeting your project goals?

Hiring Treehouse to convert our alumni data was important to the overall success of our Y2K project because it allowed our staff to focus on other tasks, yet be confident that our data would be processed correctly.

Were you satisfied with the TSI services?

Very satisfied. I would recommend Treehouse to others. They understand the nuances of data. We found the staff knowledgeable in data formats and versatile in navigating our environment with a minimum of involvement by our staff.

---

Project Overview

TSI provided a response to the RFP with many documented features of the tRelational/DPS solution designed specifically for modeling, mapping and transfer of ADABAS data to target RDBMS(s). Upon award, TSI performed the services exclusively with remote access (e.g., e-mail, phone and ISP access to the IBM Mainframe and AIX platforms).

MU’s RFP stipulated the following: The ADABAS files will be converted in three groups, corresponding to List A, List B, List C. The ‘final data extraction’ for each ADABAS file is the extraction that begins after updates to those files have been permanently discontinued. A ‘preliminary data extraction’ is the extraction that begins before updates to those files have been discontinued. Both the preliminary and final data extractions will be of all records in the files. A ‘proof of concept’ involves a data extraction of a data sample (typically 1000 records) rather than the whole file, plus a load to ORACLE.

tRelational File Implementation was performed to capture the PREDICT and physical file definitions. TSI executed the Statistical Data Analysis to review the source ADABAS data structures. The analysis information provided an understanding of the source data and early identification of possible modeling or data transfer issues.

TSI created one tRelational model for each project phase (file list), and utilized tRelational Autogen to generate the ORACLE Schema with the ADABAS-to-RDBMS mapping. Each model was modified as required to account for specific client data requirements.

tRelational GENDDL and GENDPS functions were executed to create the ORACLE DDL and the DPS Parameter sets. TSI completed an end-to-end ‘pilot’ DPS Materialization with a file extract limit of 1,000 records per source file for each model, then created the ORACLE tables and loaded the extract data on the target AIX platform and confirmed processing summary counts for the Extract Transformation and Load (ETL) verification.

Editor's Note: The MU project required conversion of about 41 ADABAS files with approximately 1600 fields for 6.7 million records to about 224 ORACLE tables with approximately 16 million rows. TSI met all milestone objectives and completed the end-to-end ETL process with minimum time requirements from the MU staff.

Upon verification of each pilot, TSI performed the ‘preliminary data extraction’ for full file DPS Materialization. This provided normalized ORACLE tables to facilitate the design, development, and testing of the subsequent load scripts into the ERP (not performed by TSI).

Upon notification of readiness for the ‘final data extraction’, TSI executed the final DPS Materialization and load processing.