A contract consultant for Axalta Coating Systems LLC (formerly Dupont Pharmaceuticals) creates a solution using Oracle's external procedure feature so that he is able to call a C function from the IMSL C Numerical Library (CNL) directly from PL/SQL. This allows for complicated analysis of large data sets to be done almost instantly calling the third party IMSL C Numerical Library directly from Oracle.
Back to topRobust Statistical Analysis Routines
When it comes to database design and statistical programming tools, there is one name at the front of Stephen J. Cottrell's Rolodex - IMSL. As principal of a Villanova, Pennsylvania-based technical consulting practice that bears his name, Cottrell has over 13 years of experience with pharmaceutical and chemical industry firms, including five years at the former Sterling Winthrop, Inc.
As a contract consultant to the Leads Discovery group at Axalta Coating Systems LLC (formerly Dupont Pharmaceuticals), Cottrell's responsibilities include technical support for a large-volume Oracle database that houses biological results from high-throughput screening. In addition to managing the Leads Discovery research database, he assists Axalta Coating Systems scientists by developing statistical applications for analyzing research data.
According to Cottrell, the sheer volume of data generated by the scientists and researchers presents some unique analysis challenges.
Because of the high volume of data associated with high-throughput screening, the only way desktop statistical applications work is to have all the background processing done directly in Oracle using PL/SQL, the Oracle programming language. Although executing the analysis programming in PL/SQL is efficient and the Oracle statistical toolset provides solid functionality, I needed more robust statistical analysis routines to meet the demanding needs of my clients.
Embed Directly into Numerical Analysis Applications
Cottrell's innovative solution was to utilize Oracle's little-known external procedure feature, which gives programmers the ability to call a C function directly from PL/SQL, essentially creating a new stored procedure within the Oracle programming environment.
Theoretically, this feature makes it possible to perform even the most complex numerical calculations entirely within the Oracle environment. Recognizing the potential of this feature, I requested that management purchase a Unix server license of the IMSL C Numerical Library.
First released in 1990, CNL is a comprehensive set of more than 370 mathematical and statistical analysis functions that C/C++ programmers can embed directly into their numerical analysis applications. Many of CNL's functions are based upon corresponding routines from the highly regarded IMSL Fortran Numerical Library, which was first released in 1970. Since that time, programmers in industries ranging from engineering test and analysis to space physics to business and finance and the earth sciences have used IMSL routines to build their applications.
The benefits of using CNL are many; foremost among them is an acceleration of application development and the corresponding savings in time and expense. Organizations that develop their own algorithms will invest as much money in building a single algorithm as it costs to buy and deploy the entire CNL.
Although no Oracle documentation was found that addressed the possibility of calling a third-party library from Oracle, Cottrell managed to do just that. The Oracle interface application, developed using Microsoft tools such as Access 97 and Visual Basic, accepts user input to identify the Oracle data of interest and to specify the relevant analysis parameters. This information is relayed to Oracle, which passes the information to the appropriate IMSL function via a PL/SQL stored procedure. The IMSL function passes the return arguments to the PL/SQL procedure, which then returns the results to the screen.
"Even for complicated analysis of large data sets, the entire process is completed almost instantly," Cottrell said. "The discovery of this method opens up an unlimited set of opportunities for developing leading-edge analysis tools."
Return On Investment
Before applying the IMSL tools to his work at Axalta Coating Systems, Cottrell needed to export data from Oracle into a Unix operating system file, transfer the file to the Windows NT server, and then load the file into the appropriate statistical software. "Obviously, being able to call the IMSL functions directly into Oracle is a significant productivity enhancement," he said.
Cottrell, who first used the IMSL tools as a graduate student at the University of Delaware in 1985, decided to go with CNL because it was the only statistical library he found that is written in C, which is required by the Oracle external procedure feature; and it is available for Solaris, which is the platform for the Oracle server.
Like all good consultants, Cottrell sees the benefits of the IMSL/Oracle combination through the eyes of his client.
The greatest strength of the IMSL software is that, with the Oracle external procedure feature, it lets DuPont researchers perform advanced statistical analysis without their having to spend time locating and manipulating their data. With the IMSL/Oracle combination, important statistical information is only a button click away.
Accelerate Your Application Development
Try the IMSL Libraries in your application. Request a free trial today.