It goes a long way back, to the introduction of Relational database management systems (rdbms). In the 80’s nobody believed that relational technology could even perform well enough for transaction applications; SQL was for query-based systems only. How wrong they were! All new applications are built on rdbms, although the size and performance of many very large databases still dictates older technologies, VSAM and IMS in particular.
Coincidental with the introduction by Oracle, Ingres, Informix, Sybase, etc. of rdbms products was the development of the minicomputer, the DEC VAX in particular. And there was a third development, Unix, rapidly maturing. The problem was that VAX, VMS, Unix and the rdbms had no support for transactions (roll-back, multi-threading, I/O management, security, etc). VMS and Unix are relatively simple time-shared operating systems.
There was one obvious answer to the dilemma created by lack of transaction services in the minicomputer operating systems and that was to build them into the rdbms. At the same time the technology was introduced to pre-compile the SQL "unit of work" and to store that in the rdbms as well. In this way multiple application program instances could share the same UOW, dramatically improving performance.
With the arrival of the PC and the Ethernet, as well as the dominance of Unix, the rdbms vendors were already well set. They all introduced a client-side driver to run in a PC under Windows, which would enable a client application to call the execution of a server-side stored procedure, almost compatible with local Unix applications. These products, typified by Oracle SQL*net, were quite clever. They included session protocols, work space mapping across the network, error management and mapping into logical names to ease integration with PC development tools. These interfaces allowed SQL functions to be transmitted in the form of text strings for "dynamic" interpretation and to make calls to the pre-compiled stored procedures, the former for ad-hoc queries, the latter for transactions.
Unfortunately each database vendor had their own protocols between client and server. The SQL Access Group, another failed standards body, tried to define a standard. In desperation Microsoft took what SAG had produced and implemented it, which has become today’s standard ODBC. Compatibility and performance continue to be an issue and many companies still prefer the proprietary variants. With the advent of Java and Java-based Application Servers, a similar standard to ODBC, called JDBC, was introduced.
The development of stored procedures was accelerated when Oracle introduced a procedural extension called PL/SQL. This was a proprietary Oracle language which added procedural capabilities to SQL. Prior to this the logical separation on an application in two layers was simple – the SQL ran in the server, the rest in the client. Now the developer has to decide which part of the application logic runs in the client and which multi-user components in the rdbms stored procedures. There were obvious performance gains, but applications written for the Oracle rdbms were totally proprietary. The Oracle rdbms had become the application execution environment; the operating system was an irrelevance, Oracle did all the multi-threading, I/O buffering, etc.
In contrast IBM followed the multi-tier architecture and focussed application code on CICS and DB2 as the rdbms. They introduced "plans" in DB2, the equivalent of the "static" stored SQL procedures in Oracle. All this worked well until IBM decided to introduce DB2 on all platforms. Under MVS, DB2 could assume help from CICS, etc., but not on an NT, Linux or Unix platform. And so, with DB2/UDB they developed a DB2 product which followed the architecture of Oracle, Sybase, etc. At the same time, Microsoft employed redundant Ingres engineers and re-vamped their toy rdbms into the competitive SQLServer of today. There is even a "standard" language equivalent to Oracle’s PL/SQL, but the other development has been to add Java to the stored procedure environment, both in the stored procedures and in standards for calling procedures from a Java client (normally an Application server running EJB). Thus JDBC is still supported for "dynamic" SQL, but "static" (pre-compiled, stored) procedures should be called via a new EJB interface called SQLJ.
The problem still remains for the developer though, what should be coded in the Application Server and what should be in stored procedures. Increased use of Upper CASE design tools is the long term answer to this, because the databases will never be fully compatible! It is difficult to know where the application server ends and the database begins!
