[A StarQuest Data Replicator and StarSQL Case Study] University of Tennessee Finds Fast Data Access for Students University of Tennessee, Knoxville replicates crucial DB2 data to SQL Server databases to facilitate new Web-enabled and data warehousing/data mart applications. The University of Tennessee, Knoxville (UTK) Division of Information Infrastructure (DII), which develops large numbers of business applications for departments campus-wide, had a serious data access problem. UTK needed to provide additional services for students via PC-based Web applications, but the data was stored in IBM DB2, causing DII to start looking for a cost-effective and secure way to open up access to its corporate data. Identifying the Problem "There is only one place that student information is located at the University, and it is the student system stored in DB2 on the IBM mainframe," said David Stinnett, senior systems analyst in DII. "Because of the mission-critical nature of our mainframe, we can’t possibly do development work or allow queries and look-ups against the live data," he said. "The problem we were facing was the data needed to enable new applications was locked up inside DB2, while the majority of our development expertise and user population operate in the Windows environment." The official student database, which contains detailed background information on the 19,000 undergraduates and 7,000 graduate students who attend UTK, is stored in DB2 Version 3.1 running on an IBM 9672 Model R 42 Parallel Enterprise Server with four processors running MVS. StarSQL Pro brings DB2 to Microsoft SQL Server "To facilitate the development of new, user-friendly applications, we wanted to be able to quickly and cost-effectively move large quantities of DB2 data to SQL Server without compromising the security afforded by MVS," Stinnett said. "We needed to maintain that level of security as we open the data up to our customers." The University found a solution in the StarQuest Data Replicator (SQDR), database replication software, which rapidly copies data to and from IBM DB2, Oracle and Microsoft SQL Server databases. StarQuest Data Replicator was jointly developed by Microsoft with StarQuest Software, a Berkeley, CA-based developer of desktop to database connectivity solutions. SQDR is completely integrated with Microsoft SQL Server and provides fast, full refresh replication of DB2 data for use with Microsoft SQL Server. To access IBM host systems, StarQuest Data Replicator uses the certified StarSQL ODBC driver from StarQuest and Microsoft’s SNA Server. "We currently leverage the replicated data in several server-based applications-both Web-based and traditional client/server-such as our new Graduate School online application system, and a client/server system used to track teacher education and licensure procedures," Stinnett said. UTK is also using HDR to facilitate the development of targeted data marts and data warehouses for the benefit of UTK’s user community. "To maintain optimum performance levels, we don’t allow queries against the OLTP system," Stinnett said. "We are now able to provide our analysts access to key DB2 data from their Windows-based systems for decision support activities," Stinnett said. DII is currently replicating one-to-two million rows daily from its mainframe to one of its NT servers using SQDR-representing 150 megabytes - and according to Stinnett, the delivered performance and throughput has been excellent. Going Forward DII has also been using SQDR for moving DB2 data to an Oracle-based database system as well. "In addition to the greatly improved data access, one of the other major benefits of using SQDR and StarSQL is we were able to avoid buying an Oracle-to-DB2 gateway, which would have been very expensive and added system overhead." Stinnett added that the University also is doing some pilot work moving SQL Server data back into DB2 as part of a centralized Student Accounting System application, taking advantage of SQDR’s bi-directional replication capabilities. "The ability to send data back up to DB2 with SQDR is saving us a significant amount of coding time, while at the same time adding value back into DB2," Stinnett said. "SQDR has proved to be an invaluable tool for us, as having safe and secure access to DB2 has enabled us to develop a series of innovative applications for the University."