Home


About Network Testing Labs

Contact Network Testing Labs

Independent Reviews of Network Hardware and Software

 

NETWORK TESTING LABS REVIEW

Relational Database Managers

 

 

Three relational database managers you can run your business on
BYTE Magazine, 1996

By Barry Nance


For transacting with and storing core business data entities such as customers, products, orders, billings, and payments, an RDMS (Relational Database Management System) is an essential part of a business automation strategy. Putting the RDMS on a networked desktop PC is a key ingredient of client/server technology because you put the data (perhaps replicated to and from a central site) close to the people who use and own that data. Selecting the right database server to support your business can contribute greatly to your company's bottom line.

We look at new versions of three relational database management systems (RDMS) this month--DB2/2 2.1, from IBM, Oracle 7.1, from Oracle, and SQL Server 6.0, from Microsoft. Each RDMS is an excellent product in its own right, differing in features and architectural philosophy from the other two. Which one is appropriate for your organization? We found Oracle the overall winner; Oracle is the most consistent as you scale it across multiple platforms and Oracle gives you more platform choices than DB2 or SQL Server. DB2/2 scales almost as well as Oracle, but not with the choices and consistency of Oracle. SQL Server runs on NT, which means it scales only from 486 PCs through DEC Alpha platforms and eventually PowerPC. But all three products are high quality software that you can trust your business with, and all three companies offer good support and are likely to be around for a while. And all three offer such useful features as stored procedures, triggers, and constraints. A stored procedure is a set of processing steps that execute on the database

server PC rather than on the client. A trigger is a stored procedure that fires when a specified event (row insertion, deletion, or updating) occurs. A constraint is a business rule in the database that specifies acceptable values or relationships among data fields. Database constraints let you enforce referential integrity relationships among your database tables; you can ensure, for example, that no one can delete a customer's account in one table if billing records for that customer still exist in another table. Stored procedures, triggers, and constraints are big-league features that, along with higher capacity and performance, help distinguish these database managers from such products as Access, Approach, and Paradox.

Performance is certainly a consideration when you're buying an RDMS, but one that's overshadowed by issues such as features, reliability, support, price, and even vendor financial health--you want a database vendor that's going to vigorously support and enhance its product for a long time to come. Performance takes a back seat for two reasons. First, the design of the database itself, rather than the database software, is by far the most important factor governing transaction and query processing times. Next, you can pick and choose different hardware on which to run your database software and thus appropriately scale the database according to the needs of each site that will run the application.


DB2/2 2.1
IBM continues to make DB2 more consistent across PCs (OS/2), RS/6000s (AIX), AS/400s (OS/400), and mainframes. IBM has targeted other platforms for DB2 as well, including HP-UX, Solaris, SINIX (Siemens Nixdorf Unix), and Windows NT. But DB2 is not yet one database product with a single code base for all these platforms. Slight differences exist among the different versions of the product, most notably in the Data Definition Language (DDL) statements you use to allocate and initialize a new database. You sacrifice a small amount of portability with DB2.

DB2/2 runs on OS/2 and supports Symmetric MultiProcessing (SMP) OS/2. DB2/2 supplies programming interfaces for a wide variety of computer languages, including C, C++, COBOL, and Fortran. IBM's own VisualAge and many third-party products (such as VX-REXX, from Watcom) are excellent add-on tools you can use with DB2/2. Clients for DB2 can be any combination of DOS, DOS-plus-Windows, OS/2, and Macintosh System 7 LAN-attached computers. DB2/2 works with IPX/SPX, NetBIOS/NetBEUI, APPC, and TCP/IP network protocols. Computer programs can use embedded SQL, DB2/2's callable programming interface, or Microsoft's Open DataBase Connectivity (ODBC) to issue Structured Query Language (SQL) statements to query or update the database.

You can configure DB2/2 to replicate data among a set of database servers, and DB2/2 can work through Distributed Database Connection Services (DDCS) or other middleware product to interface with a mainframe DB2 database. DB2/2 administration is easy, either locally or remotely, through GUI administration software. For remote sites that don't have their own database administrator, DB2/2 offers remote administration tools. A separate product, Visualizer, lets you build, update, or query your database (IBM previously shipped a Query Manager component with DB2/2). IBM supplies its Explain software with DB2/2; Explain is an optimization tool that reveals the detail of the steps DB2/2 uses during a database query or update.


DB2/2 2.1 subjects SQL statements to one of nine levels of optimization just prior to processing those statements. The nine levels, configurable by a database administrator or settable by application software, allow precise tuning of database response times. You'd use level 0 or 1 for SQL that's already optimized by the programmer. Higher levels let DB2/2 examine and reformat SQL submitted by, for instance, a front-end query tool such as Microsoft Access.

DB2/2 supports both text and image large objects, such as pictures, songs, and books. IBM uses Gradient Technology's license-tracking software in DB2/2 to help businesses manage the number of concurrent DB2 licenses they've purchased.

A programmer can design a program to emit either "dynamic" or "static" SQL to DB2. DB2 "compiles" dynamic SQL at run time. Dynamic SQL can be a string of text, for instance, that someone types into a program at a command line prompt. In contrast, static SQL consists of statements, embedded directly in the program, that are fully known at program compile time. Pre-compile and post-compile steps, in a process IBM calls "binding", store the static SQL statements in files with a .BND extension. In general, static SQL executes much faster than dynamic SQL.








The following is an example of dynamic SQL in C. The "EXEC SQL PREPARE" statement causes DB2 to dynamically compile the SQL statement in the variable "string". The sequence of CONNECT, PREPARE, DECLARE, OPEN, FETCH, and CLOSE statements shown in the code are typical of DB2/2 programs.


EXEC SQL BEGIN DECLARE SECTION;
   char  server[9];
   char  userid[9];
   char  passwd[19];
EXEC SQL END DECLARE SECTION;

EXEC SQL CONNECT TO :server USER :userid USING :passwd;
printf("Enter SQL statement: "); gets(string);
EXEC SQL PREPARE statement1 from :string;
EXEC SQL DECLARE pcurs CURSOR FOR statement1;
EXEC SQL OPEN pcurs;
EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer;
while (SQLCODE == 0) {
   display_da (sqldaPointer);
   EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer; }
EXEC SQL CLOSE pcurs;

The following shows an example of static SQL. The entire SQL statement is known at compile time.

EXEC SQL SELECT FIRSTNME INTO :firstname
         FROM employee
         WHERE LASTNAME = 'JOHNSON';


Oracle 7.1
Oracle runs on over 90 platforms (about 60 of which are Unix environments). The database software is essentially the same code for each platform; the consistency in Oracle's behavior on the different platforms is amazing. Moving an Oracle database from one platform to another is a simple matter of using the supplied Export and Import utilities. Database administration and design are also consistent among platforms. The same DDL for creating an OS/2 Oracle database can also create a NetWare NLM database, an NT database, or a Unix database.

Oracle takes advantage of SMP in environments that offer multiple CPUs (OS/2, NT, AIX, SCO). While the previous version of Oracle provided only "strict" data replication through two-phase commit or unsynchronized table snapshots, version 7.1 adds loose, time-delayed replication from a primary database site and optimistic replication that allows any one database site to update without waiting for other sites to catch up. You can configure Oracle to replicate table updates onto another (remote) Oracle database or a DB2 mainframe database.

Like DB2/2, Oracle offers user-defined datatypes and programming interfaces for a wide variety of computer languages, including C, C++, COBOL, and Fortran. Oracle clients can be any combination of DOS, DOS-plus-Windows, OS/2, and Macintosh System 7 LAN-attached computers. Oracle's SQL*NET over-the-wire SQL statement delivery system works with IPX/SPX, NetBIOS/NetBEUI, and TCP/IP transport-layer protocols. Computer programs can use embedded SQL, Oracle's callable programming interface (OCI), or Open DataBase Connectivity (ODBC) to issue SQL statements.

Administering Oracle is easy; Oracle for the OS/2 and NT platforms come with native GUI tools. You administer the NetWare NLM version of Oracle through an over-the-wire Windows interface. You can design an Oracle database to hold both text and image large objects. Oracle, through a separately-available product, offers remote administration tools for remote sites that don't have their own database administrator.


Oracle supports the use of triggers, stored procedures, and database constraints through its PL/SQL database server programming environment.

In an effort to improve the processing of SQL statements, Oracle uses an optimizer to pre-digest SQL. As long as statistics exist in the database's data dictionary for at least one of the tables referenced by an SQL statement, the optimizer uses cost-based algorithms to consider the available access paths and determines the most efficient execution plan for that access. The optimizer offers a tuning feature, called HINTS, for those database administrators who want to tweak Oracle's performance. However, the optimizer doesn't offer as many tuning settings as DB2/2.

Oracle supports dynamic and, for embedded SQL, a form of static SQL. However, unlike DB2/2, there is no separate "bind" step the programmer performs during development.







The following code fragment is an example that uses Oracle's OCI function call interface. Oracle header files establish new data types, such as "text" (representing strings) and "sword" (two-byte word integer), to ensure portability across Oracle-supported platforms. In the code, a call to "orlon()" performs a logon operation, establishing the link between the program and the database. The "oopen()" function opens a specific database cursor. And "odefin()" sets up a buffer to receive the results of a SELECT statement.




text *username = (text *) "SCOTT";
text *password = (text *) "TIGER";

text *insert = (text *) "INSERT INTO emp(empno, ename, job, sal, deptno) \
    VALUES (:empno, :ename, :job, :sal, :deptno)";
text *seldept = (text *) "SELECT dname FROM dept WHERE deptno = :1";
text *maxemp = (text *) "SELECT NVL(MAX(empno), 0) FROM emp";
text *selemp = (text *) "SELECT ename, job FROM emp";

orlon(&lda, hda, username, -1, password, -1, 0)
oopen(&cda1, &lda, (text *) 0, -1, -1, (text *) 0, -1)
odefin(&cda1, 1, (ub1 *) &empno, (sword) sizeof(sword),
    (sword) INT_TYPE, (sword) -1, (sb2 *) 0,
    (text *) 0, -1, -1,
    (ub2 *) 0, (ub2 *) 0)

if (oexfet(&cda1, (ub4) 1, FALSE, FALSE)) {
     if (cda1.rc == NO_DATA_FOUND)
         empno = 10;
     else {
         err_report(&cda1);
         do_exit(EXIT_FAILURE); } }


SQL Server 6.0
Microsoft initially licensed SQL Server for OS/2 from Sybase. In 1993, Microsoft switched SQL Server from OS/2 to its NT operating system and subsequently terminated its business relationship with Sybase. This latest version of SQL Server continues to look much like the Sybase database management products (which run on Unix platforms), but Microsoft says that from 1993 to now about 60 percent of SQL Server has undergone changes and enhancements. We found that SQL Server's underlying architecture has changed considerably from prior versions. The programming interface and configuration of SQL Server, on the other hand, are very similar to both earlier versions of Microsoft SQL Server and the Sybase product.

One of the best-known ways to connect a SQL Server database to a mainframe DB2 database is MicroDecisionWare's MDI Gateway product. Interestingly, Sybase bought MicroDecisionWare in 1994. Microsoft SQL Server customers who use MDI hope that Microsoft and Sybase relax their strained relationship just enough to allow Sybase to maintain and enhance the MDI product.

SQL Server, which runs only on NT, has a new GUI administration tool called SQL Enterprise Manager (SEM). While previous versions of SQL Server used a text-mode Interactive SQL (ISQL) interface, SQL Server 6.0 has GUI administration tools for DBA (Database Administrator) tasks. The new features Microsoft added to version 6.0 include scrollable cursors, distributed management objects, and extended stored procedures. Before, you coded your SQL Server stored procedures in Microsoft's (Sybase's) Transact-SQL language, and you were limited both in performance and function to the design of Transact-SQL. Extended stored procedures allow SQL Server to use external programs to handle database events. SQL Server, like Oracle, uses a cost-based optimizer to examine and reformat SQL statements.

Microsoft has tightly integrated SQL Server into the NT environment. SQL Server is consistently the same database manager, from both architectural and user interface viewpoints, on Intel, MIPS, or DEC Alpha PCs. NT schedules individual SQL Server threads on different CPUs if your database server is an SMP machine.


SQL Server, like DB2/2 and Oracle, can automatically replicate database changes onto other remote databases. SQL Server 6.0 replication uses a publish-and-subscribe metaphor. A distribution server hosts a distribution database, which holds rows from published tables until SQL Server can copy the rows to the databases which subscribe to the published tables. A publication database can define publication tables and a subscription database can subscribe to those published items. Through SQL Server's SEM interface, replication is particularly easy to set up and configure. However, changes made on a remote SQL Server by means of a remote procedure cannot be rolled back (undone).

SQL Server offers user-defined datatypes and programming interfaces for several computer languages, including C, C++, COBOL, and Fortran. SQL Server clients can run DOS, Windows, or NT. Customers who use OS/2, Unix, or Macintosh System 7 can purchase ODBC connectivity software from Visigenic Software (San Mateo, CA). You can link to SQL Server through IPX/SPX, NetBIOS/NetBEUI, or TCP/IP transport-layer protocols.

SQL Server supports declarative referential integrity, domain integrity (range validation), entity integrity (row uniqueness), constraints, triggers, and column-level rules and defaults. You can program database queries and updates through Microsoft's DB-lib function call interface or ODBC. Microsoft made substantial improvements to its Transact-SQL language for version 6, and SQL Server integrates especially well with Microsoft's Excel, Access, and Visual Basic products.




The following code fragment shows how to allocate a logon buffer with dblogin() and open the database with dbopen() and dbuse(). The code invokes dbcmd() to assemble an SQL statement, then uses dbsqlexec() to tell SQL Server to process the SQL. dbresults() begins the process of retrieving data, while dbrows() discovers how many rows in the table satisfy the SELECT statement. Calls to dbbind() connect program variables to fields in the database, and dbnextrow() lets the program access each returned row.



LoginPtr = dblogin();
dbsetlname(LoginPtr, DBLOGINNAME, DBSETUSER);
dbsetlname(LoginPtr, DBLOGINPW,   DBSETPWD);
dbsetlname(LoginPtr, DBLOGINAPP,  DBSETAPP);

dbproc = dbopen(LoginPtr, SERVERNAME);
dbuse(dbproc, "DatabaseName");

dbcmd(dbproc, "select CUST_NO, ");
dbcmd(dbproc, "CUST_NAME, ");
dbcmd(dbproc, "STATE_CD, ");
dbcmd(dbproc, "ZIP_CD, ");
dbcmd(dbproc, "from VCWS_TRIGGER ");
dbcmd(dbproc, "where TRANS_CD = \"1\" ");
rc = dbsqlexec(dbproc);
rc = dbresults(dbproc);
rc = dbrows(dbproc);
dbbind(dbproc,  1, STRINGBIND,  0, CustomerNumber);
dbbind(dbproc,  2, STRINGBIND,  0, CustomerName);
dbbind(dbproc,  3, STRINGBIND,  0, StateCode);
dbbind(dbproc,  4, STRINGBIND,  0, ZipCode);

result = dbnextrow(dbproc);
while (result != NO_MORE_ROWS)
  /* process retrieved data */


Conclusion
DB2/2 is the easiest to program and most reliable RDMS; we used multithreaded database access software, running directly on the database server PC, to saturate each product to the point of failure. DB2/2 failed later and more gracefully than the other products as we increased the database access activity. However, the next higher gradation of DB2, which runs on the RS/6000, lacks much in the way of third-party support.

SQL Server's tight integration with NT helps make it a secure database, and SQL Enterprise Manager gives SQL Server point-and-click ease of use. But SQL Server isn't yet ready for the enterprise. If you use OS/2 or Macintosh PCs, you'll have to purchase ODBC connectivity from other than Microsoft. And the rift between Microsoft and Sybase will be a difficult issue for prospective SQL Server customers who want to use MDI Gateway or perhaps want to mix Sybase and Microsoft SQL Server implementations.


Oracle isn't quite as easy to program as DB2/2, and doesn't have tight, thread-oriented integration with the host operating system. On the other hand, Oracle enjoys a great deal of support from third-party software vendors and SQL*NET is an excellent, multiplatform-oriented over-the-wire SQL delivery mechanism.

You could run a business on any one of these three database software products. We recommend Oracle for enterprises with many remote sites of diverse sizes; Oracle scales in fine gradations from the PC through the mainframe. Oracle is feature-rich, mature, and a market leader. For companies downsizing to client/server and that already have DB2 on the mainframe, DB2/2 is an excellent desktop solution. DB2/2 is also a good choice for organizations that need the utmost in reliability--it's an industrial-strength product. SQL Server doesn't scale upwards as well as DB2 or Oracle, but offers excellent integration with Visual Basic applications, Microsoft Access, and Gupta's SQL Windows and Quest.


About the Products

DB/2 Client/Server 2.1
$1,495 (includes 5 client licenses) plus $85 per additional client
IBM
Armonk, NY 10504
(800) 342-6672
(914) 765-1900
fax: (313) 225-4020

SQL Server 6.0
$999 plus $119 for 20 client licenses
Microsoft
Redmond, WA 98052
(800) 426-9400
(206) 882-8080
fax: (206) 936-7329

Oracle Server 7.1
$199 per server; $99 per client
Oracle
Redwood Shores, CA 94065
(415) 506-7000
fax: (415) 506-7200



Technology Focus - SQL Compilers

The database engine's SQL compiler is arguably the most important part of an RDMS. The SQL compiler has to recognize and understand natural language (SQL), then turn the SQL statements into "instructions" that it gives to the database engine's retrieval and update processes. The SQL compiler's job is compounded by the fact that it has to operate in real time as quickly as possible.

An SQL compiler process an SQL statement in 5 basic steps. The first step parses the SQL, examines the SQL for syntax errors, then converts the SQL parse tree into an internal representation (IBM, which invented SQL, calls the internal format the Query Graph Model, or QGM). The second step examines the reformatted SQL to ensure that executing the statement won't violate referential integrity. The second step also notes whether the database engine should process a constraint or trigger for the SQL.

Next, the SQL compiler rewrites the SQL statement, replacing view references with actual column names and transforming the SQL for processing by the optimizer. The transformation eliminates redundant joins, adds implied predicates, and converts INTERSECT clauses to EXISTS subqueries. The optimizer itself, the fourth step, uses cost-based algorithms to determine the most efficient execution method for the SQL. The optimizer finds the best join order, for example, and it decides whether the execution of the SQL statement will be CPU- or I/O-bound. The optimizer chooses an execution path for the SQL statement that will result in the quickest response from the database engine. The fifth step "remembers" the essence of the SQL for later comparison with other SQL statements -- the SQL compiler keeps a history of how well it optimizes statements so it can "learn" the fastest ways to access the database. Finally, the SQL compiler's fifth step delivers the compiled, optimized SQL statement to the retrieval and update processes in the database engine.


Database Performance
The design of your database governs performance to a much greater degree than your choice of database software. Most databases can benefit from changes in table design, choice of index columns, and query design. If you focus first on these areas, you'll reap the most rewards from your efforts to make your database perform faster.

Normalizing the design of a database entails the elimination of redundant data, the avoidance of repeating data groups, and the creation of separate tables to hold different categories of data. A properly normalized database (i.e., one in third normal form) generally has many tables with relatively few columns in each. Normalization can improve database performance for the following reasons:

Fewer columns in a table imply faster sorting and faster index creation.

Indexes can be clustered because there are more tables.

Indexes can be more compact.

Fewer indexes per table suggest INSERT, UPDATE, and DELETE statements will process faster.


Choosing indexes and keys that help normalize the data can help performance. Simplifying long-running queries through the use of appropriate keys and indexes can reduce response times, and you might consider running complex queries that look at entire tables during times of the day when transaction volume is low.




Copyright 2012 Network Testing Labs


  
Home

About Network Testing Labs

Contact Network Testing Labs