Service Nova Scotia & Municipal Relations (SNSMR) is a relatively new Provincial Government department created from the amalgamation of the former departments of Business & Consumer Services and Housing & Municipal Affairs. The Department is using technology to deliver a wide range of government services across the province. The ultimate goal is to deliver the services people need, when, where, and how they need them, not simply during regular office hours.

What is Service Nova Scotia & Municipal Relations? In a nutshell, the new department’s businesses fall into five categories: Registry and Information Management, where businesses can register a name, apply for licences, and enroll in government programs Alternate Program Delivery, which investigates new ways of serving clients through innovative partnerships Service Delivery and Operations, which manages the day-to-day frontline and electronic operations, such as the Access Centres, Registry offices, and Service Nova Scotia Express kiosks Program Management and Corporate Services, which develops policies and programs in consultation with key stakeholders Municipal Relations, which deals with Nova Scotia’s 55 municipal administrations

One group, the Compliance Unit, is responsible for enforcing regulation of the commercial trucking industry in Nova Scotia and ensuring adherence to road safety requirements.

The Compliance Unit works in conjunction with the Canadian Council of Motor Transport Administrators (CCMTA), which is the official organization in Canada for coordinating all matters dealing with the administration, regulation, and control of motor vehicle transportation and highway safety. CCMTA incorporates members from the Federal government and all Canadian Provincial and Territorial governments, as well as Associate Members from more than 370 transportation-related organizations.

Prior to this development project, the Compliance Unit used a number of disparate systems to collect Nova Scotia Carrier (NSC) information at the Head Office, weigh stations, and roadside. The Compliance Unit required a new integrated system to eliminate duplicate data entry, improve efficiencies, and meet Federal requirements for information sharing between provinces. The new system was designed to help the Compliance Unit improve processes and productivity relating to the collection, input, transmittal, retrieval, and use of carrier conviction, collision, Commercial Vehicle Safety Alliance (CVSA), and audit information.

The CCMTA-mandated information exchange was the prime business driver to initiate development of the Carrier Profile System. The Director of Compliance also viewed this as an opportunity to fulfill a long-standing vision of providing real-time information retrieval and entry capabilities to mobile in-car and regional Compliance Officers and Facility Auditor staff. Wireless Internet services via Cellular Digital Packet Data had recently been provisioned within the Province..

After the successful demonstration, and a collective sigh of relief, the project team was committed to using Treehouse.

Most of the pertinent data pertaining to drivers, vehicles, and carriers is collected and maintained in the main provincial Registry of Motor Vehicles (RMV) system, a mainframe NATURAL/ADABAS application that first went into production in 1987. The RMV system has numerous interfaces for data interchange with other systems, such as the Department of Justice (summary offense tickets and convictions under the Motor Vehicles Act), CCMTA (non-commercial driver and vehicle information), and the International Registration Program.

The main development criteria were to provide a Carrier Profile System that met the CCMTA data interchange mandate, provide a client-friendly data input and retrieval capability over low bandwidth network connections, and provide ad hoc query and reporting capabilities to facilitate road safety activities and assess the effectiveness of road safety programs. These criteria pointed to a browser-based application interface with a relational database back end. The initial functional and technical design work completed under contract by a local consulting company had recommended such a solution but with a wholesale manual export of selected RMV data to an ORACLE back end and disengaging the RMV and Carrier Profile systems. Although technically “doable”, this proposal was less than ideal, as there would now be two application interfaces to operate, RMV and CAPS, and duplicate data entry required for updates. In light of other development work in the Provincial Government dealing with alternative access channels to NATURAL/ADABAS applications, the Compliance Unit decided to proceed with a development contract but with an alternative architecture evaluation as the initial deliverable. EDS Canada was awarded the contract, and the Project Team began their work in October 2000.

The design choices considered included: Remain fully in the NATURAL/ADABAS environment by adding CAPS functionality to RMV and utilize a 3270 client front-end Develop the application as a browser interface using a middleware or message broker product front-end and have the data continue to reside in RMV ADABAS files Utilize the hybrid model proposed in the initial design exercise Deal with the CAPS system at the data level by transferring to ORACLE with synchronization from RMV, and develop the web front-end using common web programming techniques

Although NATURAL/ADABAS applications, such as RMV, fulfill their transaction processing role well, the lack of experienced NATURAL/ADABAS programmers in this region presented a significant risk to continued new development. That approach was discounted fairly early in the decision process. The second choice, application integration using a middleware or message broker interface was thought to be both cost and performance prohibitive. RMV comprises some 3,000,000 lines of code in 1500 modules, and there was a concern that the granularity of program modularity would not easily lend itself to this type of EAI architecture.

The Lead Technical Architect for the Project Team found a company with the unlikely name of Treehouse Software with two products, tRelational and DPS, that were thought to provide a good fit for our requirements. RMV would remain as the principal vehicle, driver, and activity registration platform, as well as the interface to Justice for driver convictions, while CAPS would serve its intended function with regular updates propagated from RMV and CCMTA. The product literature was promising, and the live, on-line demo confirmed that if tRelational and DPS could work in our mainframe NATURAL/ADABAS environment, an alternative architecture was available. However, there were a number of risks. The deadline for demonstrating CCMTA data exchange capability was March 31, 2001. This was a product set that no one in the development team had any experience with, and, if the learning curve was too protracted, we were in danger of missing the deadline. Our NATURAL installation was Release 2.2.8, and there was some question as to compatibility with Treehouse. Since RMV is a dynamic system with regular enhancements, bug fixes, and legislative changes, the manual export from ADABAS to ORACLE as suggested in the initial architecture proposal was also threatening the timeline.

An evaluation copy of tRelational and DPS arrived just before Christmas 2000, and arrangements were made with the mainframe service providers for installation. Also, to minimize installation risks and lower the learning curve, Compliance decided to take advantage of Treehouse Software’s pilot project offer and bring their consultants on site to do the initial materialization demonstration. Wayne Lashley and Dan Sycalik arrived on a Tuesday in early January and, after spending the first three days clearing up installation issues (mostly NATURAL and individual file security), managed to demonstrate a materialization to the ORACLE database on day four. After the successful demonstration and a collective sigh of relief, the project team was committed to using Treehouse.

tRelational as an ADABAS modeling and analysis tool was invaluable in identifying ADABAS data elements and attributes, and generating ORACLE tables.

Over the next two months, while one part of the development team concentrated on the Web front-end, client interfaces, and web server implementation, the other group delved into transfer of the data from RMV to ORACLE. For the initial release of CAPS, five main RMV ADABAS files were used comprising some 8,000,000 records. During the transfer (materialization) effort, Treehouse support was efficient and effective in identifying and resolving product and data issues, with the end result of the initial materialization completed by mid-March and data interchange capability with CCMTA successfully demonstrated prior to the deadline. Of the lessons learned, the most significant would be to gain intimate knowledge of the data prior to materialization efforts. Due to the operational life span of RMV with the numerous extensions and enhancements, that knowledge was not held by any one person and was often discovered by trial and error (e.g., ORACLE and ADABAS treat NULL primary key fields differently). tRelational as an ADABAS modeling and analysis tool was invaluable in identifying ADABAS data elements and attributes and generating ORACLE tables. However, it is not a substitute for an up-to-date data dictionary.

...the end result is a preserved investment in the existing RMV ADABAS application...

The other benefit demonstrated with the materialization/propagation approach was the cost savings in mainframe charges. With the final materialization run completed and DPS propagation now run as a regular nightly process, cost metrics have shown that mainframe charges resulting from a full materialization against the RMV ADABAS data files are orders of magnitude greater that the DPS propagation process running against the transaction log.

With the first phases delivered and the initial CAPS release about to go into production, the end result is a preserved investment in the existing RMV ADABAS application and a Carrier Profile System that has been well received and endorsed by those who have viewed it. The next CAPS phases, convictions and collisions, are now under development. The Compliance Unit is considering more widespread distribution to other agencies with an interest in this information (e.g., law enforcement) as per an expanded vision.

We also received some comments from Tom Stroud, Technical Lead for the SNSMR project. How did tRelational impact the data transfer schedule? If we were to create manual extracts of the ADABAS data to populate our ORACLE back end, it would have at least quadrupled our development time. How maintainable are tRelational and DPS ? Following our initial materialization, we have added additional functionality to our application that requires bringing more ADABAS files into our ORACLE database. This is simply a matter of "Implementing" the new ADABAS files through tRelational, and then running a few batch jobs to transfer the data. (A very simple process!) How do tRelational and DPS affect your current ORACLE environment? tRelational allows for the importing of existing structures or generating new ones from the ADABAS file structure. We currently have an environment where both tRelational generated and pre-existing tables are being used. The existence of our tRelational generated structures alongside our other application tables allows us to easily incorporate the legacy mainframe data into our new Web-based application. What lessons were learned during the implementation? If I were to pass along one piece of advice to others it would be: Know your data prior to implementation. The reason I say this is we were new to the data, and because of this, we found instances that where fields we would have expected to be unique on the mainframe actually were not. Usually this was due to some legacy fields not being used consistently throughout the various pieces of the old mainframe system. This initially threw off some of our primary key assignments on the tRelational generated tables. When we became aware of this, both tRelational and tRelationalPC provided a quick means of re-defining our field relationships.

SNSMR Consulting Visit by Dan Sycalik SNSMR contracted a one-week consulting site visit from TSI to assist with tRelational modeling and mapping and DPS “Implementation”. The technical lead assigned to the Carrier Profile project was skilled with ORACLE but was not conversant with IBM mainframe, ADABAS, NATURAL, or the source application data. We began with a review of the technical environment and met with the ADABAS DBA, the ORACLE DBA, and others to agree on naming conventions, logon ids, ORACLE table space, etc. A golden rule for data modeling is “know your data”. We continued with a tRelationalPC ODBC Import of the ORACLE Schema and a mapping of the source ADABAS fields. Several questions prompted a meeting with the applications staff. Following a discussion, they viewed the data with ad hoc queries and discovered the data was different than expected. Note: This is not uncommon, and tRelational provides statistical analysis specifically to provide a better understanding of the application data. A golden rule for data modeling is “know your data”. To that end, we discussed the benefits of PEEK, an ADABAS “File Browser”, distributed by TSI. We altered the model to account for the new discoveries and generated the ORACLE DDL and the DPS parameters. We executed DPS Materialization and loaded the data to the ORACLE tables. We discovered several data related issues, such as invalid dates and non-unique Primary Keys. We discussed the issues and options during a status meeting with project managers. Despite the many unexpected data discoveries and data issues, we materialized the data and the technical lead had the ‘building blocks’ to successfully implement the procedures without further consulting assistance.

Editor's note: Thanks to Wayne Richard, Business Solutions Manager for the SNSMR project, for providing this article, and to Tom Stroud, Technical Lead for the SNSMR project, and Dan Sycalik, TSI Project Manager, for their contributions to this article.