"Swiss Army Knife" Servers
SQL DBMS vendors such as IBM, Microsoft, Oracle and Sybase have been in competition since the 1980s. The competition benefits the SQL community because vendors and open source initiatives continue to add new capabilities with each new server release.
|Microsoft enhanced SQL Server for OLAP by adding cube queries and multidimensional expressions (MDX) in Transact-SQL queries. IBM, Oracle, Sybase and others embraced object-relational technology and Java in the database. Microsoft added the Common Language Runtime (CLR) to SQL Server 2005, much as other DBMS vendors integrated the Java Runtime Environment (JRE) into their servers.
With the emergence of XML, there was a growing expectation that SQL servers would support both data processing and document processing. Open source projects developed tools for XML and web services development, but open source database management systems (DBMS) lagged behind commercial products in XML integration. The major SQL vendors, including IBM, Microsoft and Oracle, implemented the SQL:2003 XML data type and query processing with XPath and XQuery.
Server Plug-Ins and Object-Relational Technology
IBM, Informix, Oracle and Sybase adopted object-relational technology to augment traditional types such as numbers and characters. The traditional SQL server evolved into extensible platforms with an architecture that accepts plug-ins. Servers plug-ins provide user-defined types and new access methods. As a result, a database is not longer simply a passive data container. Modern databases contain logic. For an overview of this concept, read "New Servers, New Architectures, and Logic in the Database". It explains universal databases, extenders, and logic in the database. For a look at this subject from a Java perspective, read "Java in the Database, Objects in the Middle Tier".
The Server Watch follows emerging database technologies and discusses server-side programming technologies. These technologies include IBM DB2 Extenders, SQL Server Extended Stored Procedures, and IBM Informix DataBlades. Check here from time to time for updates about server capabilities and server programming.
The extensible architecture and object-relational technology have enabled SQL servers to operate with Java-enabled and XML-enabled databases.
Several major SQL providers have embraced Java in the database as a preferred technology for creating database plug-ins. Installing Java classes in databases adds types and behavior. Oracle JServer and IBM DB2 UDB use the Java Runtime Environment to execute Java database plug-ins. A database containing Java plug-ins is known as a Java-enabled database.
SQL providers who offer Java-enabled databases support the use of JDBC and SQLJ in database plug-ins. Servers that operate on Java-enabled databases use an internal JDBC driver for executing server-side JDBC programs. Programmers writing Java classes to plug into a database can use dynamic SQL (JDBC) or static SQL (SQLJ).
"Java Objects in the FirstSQL/J Database" by Lee Fesperman explains how to program with Java objects and FirstSQL/J. It includes a money class example.
"Java in the Database" (Java Pro, March 1999) explains how IBM, Oracle, Sybase, Cloudscape, and Informix database management systems support embedded Java. It explains SQLJ, and programming Java classes to install in SQL databases. The example source code uses JDBC to do a SOUNDEX search of a Sybase Adaptive Server database.
"Java, JDBC, Stored Procedures, and Server-Mania" discusses IBM Informix Dynamic Server and plug-ins such as the Web DataBlade (Web Integration Option). It explains the use of stored procedures in JDBC programs and includes downloadable Java source code.
"Understanding Java-Enabled Databases and Adaptive Servers" explains how to program Java classes to install in databases. It includes example code for doing JDBC queries and Metaphone phonetic searches with Sybase Adaptive Server. An earlier version appeared in the Database Developer column of Web Techniques.
This article in Dr. Dobb's Journal (August 1999) discussed techniques for extending IBM DB2 Universal Database (UDB).
Extenders, UDFs, and Stored Procedures
Universal databases for rich data types
Database managers have long been used as building blocks for online transaction processing, decision support, and operational systems. Traditional SQL database management systems (DBMS) have done well in managing tables of numbers and characters, but the database development landscape is changing, and influences such as object-oriented programming and the World Wide Web have raised expectations. Now both users and developers expect to store behavior and use richer data types.
Clearly, the emerging generation of feature-rich applications require multimedia, geospatial data, and types that are more complex than traditional SQL rows and columns. Consequently, developers require sophisticated database managers for complex data, User-Defined Functions (UDFs), and User-Defined Types (UDTs). The need for richer types and custom behaviors is a primary reason developers are moving to universal databases -- that is, tools for storing all types of data in a database -- that have object-relational characteristics. Universal databases also store behavior such as methods, functions, triggers, constraints, and procedures.
DB2 Universal Database
When traditional SQL DBMSs evolved into Object-Relational DBMSs (ORDBMS), IBM was an early supplier of object-relational technology. IBM consolidated several DBMS tools to produce its DB2 Universal Database (UDB). UDB supports active databases that contain rules and logic, making it possible for you to rely on the database manager to enforce rules about data. UDB offers an open-ended architecture for developing custom types and behaviors, and lets you create both distinct and structured UDTs.
"Tool Suites, MDX and SQLJ" discusses embedded SQL for Java.
Use an object-relational database to store a complete Web site? How? Read "Dynamic Servers and Web Pages in a Database" in the December 1998 issue of Web Techniques. It discusses Informix Dynamic Server's Web Integration Option, Data Director for Web, DataBlades, and dynamic generation of HTML pages from Web sites stored in Informix databases.
"IBM DB2 Goes from Glass House to Global Village" (Internet Computing, May 1998) describes IBM DB2 Universal Database, DB2 Extenders, and content-based queries (QBIC).
"APIs for Universal Database Programming" (Web Techniques, August 1998) explains IBM DB2 programming using ESQL, CLI, administrative APIs, DB2 Extenders, Java UDFs, and Java Stored Procedures.
"Sybase Puts Java on Enterprise Pedestal" (Internet Computing, March, 1998) describes Sybase Adaptive Component Architecture, Java in the database, jConnect, and enterprise Java developer tools.
"XA, Java Ease Distributed TP" (PC Week, December 1998) discusses Java extensions for distributed transaction processing.
From the Database Developer column by Ken North (Dr. Dobb's Sourcebook, September 1997)
Java in the Database, Objects in the Middle Tier
Database developers have numerous options for laying out the architecture of an application, so many that we often face a classic decision about where to put logic. Sometimes the best solution to a major problem is to apply a small bit of discrete logic at the right place and time. During the Apollo program, the assignment of improving task termination and error recovery for NASA's Goddard Real-Time System (GRTS) fell into my lap. GRTS was part of the network that tracked lunar spacecraft. The software ran on IBM mainframes as mission-critical, real-time extensions to IBM's OS/360 operating system. GRTS used a main task/subtask architecture and executed in an unprotected address space. My challenge was to keep problems such as disk I/O errors during subtask loading from bringing down the system. No one wanted to say "Houston, patch around us," or worse yet, "Apollo, we have a problem!"
The error-handling solution involved the installation of a transient SVC (Supervisor Call), a discrete bit of logic in a small module that the operating system loaded when needed. Today, operating systems such as UNIX and Windows NT routinely use a similar technique in the form of loadable device drivers. What was once true about operating systems is now true about database software -- adding extensions can solve problems or supplement core functionality. Many early DBMS products were closed systems that supported only a query language interface.
Oracle, Microsoft and IBM provide extensions for XML processing with their database servers. These servers can store and retrieve XML data as entire documents (native XML) or map documents to multiple columns in tables (shredding).
XML-enabled database servers can execute SQL queries that return XML documents. They support the use of XPath expressions in queries and Extensible Stylesheet Language (XSL) transformations.
What makes this paper of significant importance to the SQL/XML industry is it proves how standard SQL can perform full multi-leg hierarchical processing. It explains how the relational Cartesian processing engine automatically and inherently performs Lowest Common Ancestor (LCA) logic that's required to perform hierarchical processing. This is original material from Michael David.
"Center of the Universe" (Intelligent Enterprise, May 2003) discusses how IBM, Microsoft and Oracle are marrying SQL, XML, web services and grid computing in their race to offer the best database product.
"Text Indexing, XML Searches, And Other Database Tricks" (Web Techniques, February 2000) discusses database capabilities for indexing and searching XML documents.
"Oracle: Powered by XML and Java " (XML, Winter 1999/2000) discusses support for XML in Oracle 8i and Oracle's tool set.
"Center of the Universe" (Intelligent Enterprise, May 2003) discusses integrated XML messaging, XQuery and XML schema support provided by IBM, Microsoft and Oracle.
"XQuery and SQL: Vive la Différence" (DB2 magazine) discusses XQuery, SQL and the XQuery API for Java.
"Schema, query: Is it database or XML?" (searchDatabase, February 2001) discusses the XML Query Language.
" XML and Databases: Too Much Confusion" (XML-Journal) discusses XML/SQL integration. Download Volume 1, Number 2.
Databases and XML: Different Worlds? is an interview with author Akmal Chaudhri. Requires Real Video or Real One player.