database.sarang.net
UserID
Passwd
Database
ㆍDBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
DBMS Tutorials 56 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 56
Data Access Via ODBC and JDBC - 영문원본
작성자
정재익(advance)
작성일
2001-11-04 19:13
조회수
4,873

Data Access Via ODBC and JDBC

 

원본 출처 : http://www.networkcomputing.com/netdesign/odbc1.html

원저자 : Barry Nance

 

ODBC (Open Database Connectivity) and JDBC (Java Database Connectivity) are types of database access middleware. From a network administrator's point of view, they consist of client and server driver software (i.e., program files). From a programmer's point of view, they are APIs that the programmer inserts in his or her software to store and retrieve database content. While a system analyst perceives ODBC or JDBC as a conceptual connection between the application and the database, database vendors regard ODBC and JDBC as ways to entice customers who say they want to use industry standard interfaces rather than proprietary ones. And managers of data processing departments view ODBC and JDBC as insurance--interfaces that offer managers some measure of flexibility should they find it necessary to replace one database product with another.

In this chapter of Network Computing's Network Design Manual, you'll look at ODBC and JDBC from each of these perspectives. You'll learn why ODBC and JDBC are important, understand how these interfaces work and determine how your organization can best use ODBC and JDBC.

 

To access a database, applications can choose from a confusing array of methods-ODBC, JDBC, OLE DB, ADO, DAO, RDO, Oracle Objects for OLE, gateways, proprietary native call APIs, such as OCI or DBLib, data-aware components and a variety of other solutions. Because ODBC and JDBC are platform- and database-neutral standards many vendors support, this chapter focuses just on ODBC and JDBC.

 

Database Access Middleware

 

Database access middleware provides a stylized connection between an application computer program and a database. By stylized connection, we mean a link having a formal, published definition. This definition identifies the interface that application programs must use to issue SQL and receive database content through the link. In the case of ODBC and JDBC, the definitions are common to all vendors because ODBC and JDBC are industry-standard database access middleware. ODBC and JDBC are important because they are available on many disparate platforms and they provide common interfaces to several different database products.

 

Primarily, database access middleware shuttles SQL and database content to and from application programs. In addition, it helps ensure security, and it insulates the application from having to deal directly with the database server. Generalized query tools can interface with database access middleware (rather than the database server itself) to provide query services for multiple types of database products. Computer programs written in Visual Basic, C, C++, Pascal, COBOL and many other languages can perform database operations via ODBC. Similarly, programs written in Java can use JDBC to perform database operations. Programs using ODBC or JDBC can run directly on the database server computer, but, more typically, they run on client computers networked to a database server.

 

Vendor support for ODBC is pervasive. JDBC support isn't quite at the level of ODBC support, but JDBC is growing and flourishing. Database product vendors and several third-party software houses offer ODBC and JDBC drivers for a variety of databases and operating environments. The Appendix contains a list of vendors for ODBC and JDBC middleware.

 

The following discussion material covers the basics of database access middleware and provides specific information about ODBC and JDBC. You'll learn how the redirection of SQL to and from a database server works; explore different types of data access; gain an understanding of ODBC's goals; learn the considerations for using ODBC in your organization; and find out how to use ODBC effectively. In addition, you'll read about JDBC's goals; explore the considerations for choosing JDBC; delve into the four different JDBC driver types; and learn how to use JDBC effectively.

 

Database Access Middleware Basics

The SQL standard identifies the statements that application programs can use to store or retrieve database content, but it doesn't specify the steps involved in connecting to the database, how to deliver the statements to the database server, or how to transfer database content to and from the database. Before the advent of ODBC, computer programs used the delivery mechanism supplied by the database product vendor-a mechanism proprietary to the vendor and always quite different from the delivery mechanisms of other vendors. With ODBC and JDBC, applications have a database-neutral means of delivering SQL to the database server. Whether via a proprietary or standard interface, database access middleware's basic approach to providing a pipeline between a client and the database server is the same. And it works in a fairly simple manner.

 

Delivery mechanisms for both SQL requests and database content replies, when used in a networked environment, are part of the network protocol stack. Application programs connect to the database, issue SQL, and retrieve results by interacting with the database driver software within the network protocol stack running on each client machine.

 

The delivery mechanism-the database access middleware-accepts the SQL from the application program. As it accepts the SQL, the delivery mechanism presents a database server interface to the application, just as if the application were running on the database server computer and dealing directly with the database software. However, the delivery mechanism acts only as an agent, transmitting the SQL to the actual database server and receiving the replies. For each reply, the delivery mechanism again presents a database server interface to the application software as it delivers the results to the application. When a software agent intercepts information by pretending to be the recipient and sends the information over the network to be processed by a server, the agent is using redirection.

 

On the database server, programs listening for SQL request messages from client machines turn each received request message back into a SQL database call, invoke the database server software to process the SQL, and return the database software's result through the network to the client. Thus supplied with database content, the application continues its processing.

 

Turn for a moment from the inner workings of database access middleware to the administrator's perspective. The database access middleware running at each client computer must be configured into the protocol stack by an administrator, and typically all clients should run the same version of the middleware. Once the administrator has installed the database vendor's software (Oracle Corp.'s, for instance) on a server computer, he or she then visits each client computer to install SQL delivery drivers on those clients. Thereafter, each time the network gains or loses a client machine, the administrator performs maintenance tasks to install or remove driver software. New releases of driver software from the database vendor call for distributing the driver software to each client in a planned, scheduled update effort. Vendor claims of quality and bug fixes notwithstanding, an administrator always makes sure the organization tests the new release of driver software before replacing the old drivers with the new ones.

 

 

Different Types of Database Access

 

Application designers have used many different approaches to connect client computers to database servers. These approaches include a custom-written (home-grown) design, database vendor (proprietary) database access drivers, ODBC, JDBC and object-oriented technologies that let application programs forego the issuing of SQL to treat database content as persistent objects that do not require explicit retrieval or storage.

 

The most rudimentary and labor-intensive approach is a home-grown one that completely avoids the use of ODBC, JDBC or other commercially-available database access middleware. Using this approach means client machines need a protocol stack containing a transport layer and nothing else. The in-house programming staff develops both client and server software for transferring requests and responses over the network. Application programs send and receive network messages via a transport layer (TCP/IP, IPX, SNA or NetBEUI) protocol, using the programming interface (perhaps WinSock or NetBIOS) appropriate for that protocol. An application system designer specifies the layout and meaning of each different network message, and all the message specifications together form a dialog. Within the framework of the dialog, a client application sends network messages to the server-side application. The server component performs work on behalf of the client and, as spelled out by the specification, returns a response to the client. Applications that follow this approach usually run a bit faster and consume less memory because they work directly with the network-and avoid using general-purpose middleware. On the other hand, though simply sending and receiving messages via a transport layer such as TCP/IP isn't particularly difficult, the design of the dialog and the extra work to incorporate the send/receive operations into the application are tedious and error-prone. Network administrators generally favor the home-grown approach because it entails less work distributing driver files and setting client configurations.

 

In a more typical approach, application programs use the database vendor's call-level interface to issue SQL and retrieve database content, and the vendor-supplied database access middleware shunts the SQL and database content to and from the database server. As an example, Oracle's call-level interface is its Oracle Call Interface (OCI), while its best-known database access middleware delivery mechanism is SQL*Net. Other popular vendor interfaces and associated database access middleware products are Open Client for Sybase, I-Net for Informix Software, Ingres Net for CA-OpenIngres, Progress Client Networking for PROGRESS, DBLibrary for Microsoft Corp.'s SQL Server, and DDCS for IBM Corp.'s DB2.

 

Database access APIs that did not (or have not yet) become a standard include IBM's Distributed Relational Database Architecture (DRDA), Microsoft's Data Access Objects (DAO) and Remote Data Access (RDA), and Oracle's Objects for OLE (formerly Oracle Glue). DAO and RDA are high-level, object-oriented APIs that transform requests and responses into ODBC calls. Oracle Objects for OLE is a middleware product that allows native access to Oracle7 and Oracle8 databases from client applications via the Microsoft OLE standard. It consists of three components: an OLE Automation (InProcess) Server, which supplies an OLE Automation interface to applications that support OLE automation scripting, such as Visual Basic; an Oracle Data Control, implemented as a VBX (Visual Basic custom control); and two C++ Class Libraries, one for Microsoft Foundation Classes (MFC) and another for Borland International's OWL. Oracle Objects for OLE is a layer of middleware the application interfaces with and, in turn, uses SQL*Net to communicate with the database server.

 

Sometimes the best way to concurrently connect a variety of clients to Oracle, Sybase, SQL Server, and DB2 databases running on different servers, without loading multiple drivers, is a generic database access middleware driver (termed by some a data access tool). A subset of middleware designed to overcome ODBC's limitations in multiple database environments, data access tools distribute queries to different databases and provide additional APIs besides just ODBC. Two such tools are OpenLink Software's Data Access Driver Suite and ISG's (International Software Group's) Navigator.

 

With object-oriented databases, such as Computer Associates International's Jasmine, each database entity is simply a program object whose lifetime persists beyond that of running of the application. In other words, Jasmine-based computer programs can operate directly on the database content without issuing SQL to retrieve or store that content. Jasmine achieves this by closely following the current ODMG (Object Database Management Group) object model, ODMG-93, which proposes a direct relationship between objects within computer programs and objects in a database. The result is a reduction in data-type mismatch as programs retrieve, manipulate, and store database content. Mismatch is the extra work a program does to convert database entries to and from its own format (dates, for example). This extra work can sometimes add 30 percent to 40 percent to the bulk of a program. The reduction in application size and programming effort comes at the expense of loading a slightly larger than usual database access middleware driver that Computer Associates supplies with Jasmine.

 

ODBC

 

ODBC is a multidatabase API for programs that use SQL statements to access data. An ODBC-based program can access heterogeneous databases without needing source code changes-one program can retrieve and store content in different vendors' databases via the ODBC interface. ODBC thus provides database-neutral delivery of both SQL and database content. Be aware, however, that you must load ODBC driver software for each vendor's database you want to access. Contrast this situation with that of the generic data access tools mentioned earlier.

 

ODBC Basics

 

Microsoft created ODBC in 1992 by extending the CLI (call level interface) from SAG (SQL Access Group, now part of X/Open). It gained acceptance over Borland's Integrated Database Application Programming Interface (IDAPI). The ANSI and ISO (International Standards Organization) adopted an updated version of that CLI as part of the SQL-92 standard, and ODBC version 3.0 aligns with that standard. ODBC drivers give applications a number of methods they can use to access databases, and these drivers also give tools the ability to retrieve and update metadata-the higher level of information in the database that specifies table names, column names, column attributes and other management data. However, because they're designed for a particular vendor's database product, vendor-specific database access middleware drivers give applications and tools an even greater ability to manage metadata within the database. ODBC is a lowest-common-denominator approach to database connectivity.

 

Database neutrality, multiple platform support and its adoption as a standard are the reasons data processing managers, system designers, and programmers like ODBC. The database product vendors as well as several third-party vendors give away or sell ODBC middleware drivers for a variety of operating environments and databases. ODBC is, by far, the most popular database access middleware in use today.

 

ODBC as a Layer of Middleware

 

ODBC defines the client side of database connectivity but not the server side, and ODBC middleware drivers typically rely on the underlying presence of a vendor's proprietary driver (SQL*Net, in the case of Oracle). ODBC drivers transform ODBC calls into vendor-specific access requests and responses. As a result, network administrators must install and configure not only an ODBC driver on each client but the underlying vendor-specific proprietary driver. Another result of ODBC's transforming requests and responses into vendor-specific data access calls is a lack of identity for ODBC network messages to and from the database server. If you used a protocol analyzer to capture database server network traffic, you'd find distinguishing ODBC network messages from vendor-specific driver messages difficult.

 

ODBC's addition of an extra layer of insulating middleware is both its strength and its weakness. ODBC presents a common, standard interface no matter what the vendor-specific middleware might look like, but ODBC consumes some memory and, in its earliest incarnations, slowed data access noticeably. Current offerings from companies like INTERSOLV, Visigenic Software, and Information Builders exhibit considerable performance improvements over previous versions and have consistent feature support across all major database products.

 

ODBC's network utilization depends more on the underlying vendor-specific layers of middleware, such as Oracle's SQL*Net, than on the ODBC driver itself. In particular, Oracle's layer of database access middleware beneath ODBC causes the least network traffic, while Microsoft's DB-Library connection to its SQL Server database causes the most. Network protocols can make quite a difference, too. NetBEUI is the "chattiest" protocol, IPX/SPX considerably less chatty, and TCP/IP the most frugal user of the network.

 

 

ODBC Conformance Levels

 

The ODBC standard specifies conformance levels for both the programming interface and SQL statements delivered to the database via ODBC. These levels are 0, 1, and 2 for the API, and minimum, core, and extended for the SQL grammar. The conformance levels for the API and the SQL grammar are independent, making it technically possible for a driver to provide level 2 API support while supplying extended-level SQL grammar support.

 

ODBC conformance level 0 for the API supplies basic, rudimentary support for connecting to databases, executing SQL statements, retrieving data, and committing or rolling back a transaction. Level 1 adds the ability to access part or all of a result column value (useful for long data-i.e., a large amount of data), retrieve information about driver and database capabilities, and manipulate metadata. Finally, level 3 augments ODBC with scrollable cursors, further metadata access (including information about privileges), and parameter arrays.

 

For SQL grammar support, the minimum SQL conformance level specifies common, basic DDL (Data Definition Language) and DML (Database Management Language) support. SQL commands such as select, update, delete, insert, create table and drop table are examples of those at the minimum SQL conformance level. The core SQL conformance level adds further DML grammar, such as alter table, create index, drop index, create view, drop view, grant and revoke. It also specifies a wider range of data types, including decimal, numeric, smallint, integer, real, float and double precision. A driver offering minimum SQL conformance must allow expressions, such as sum and min. The extended SQL conformance level says an ODBC driver must support more esoteric DML, such as for outer joins, positioned update, positioned delete, select for update and unions. It also calls for the driver to offer scalar functions, such as substring, abs, date, time and timestamp as well as data types, such as bit, tinyint, bigint, binary, varbinary, long varbinary, date, time and timestamp. Extended level drivers also allow client programs to execute stored procedures within the database.

 

 

ODBC's Usefulness

 

ODBC drivers provide access to more than just multiuser relational databases. ODBC drivers exist for Microsoft's Access and FoxPro, Borland's Paradox and other desktop PC data sources. Many word processors and spreadsheets have ODBC support, as does Lotus Notes. Even some document management systems have ODBC interfaces. These examples notwithstanding, probably the most common use of ODBC is in client/server applications.

 

A typical client/server approach has program and data files on a file server, accesses a relational database and often has a two-tier architecture. Both presentation and business logic occur on the client, and data storage occurs on the database server. In contrast, a Web-based approach puts presentation software at the client (browser), business logic on an application server and data storage on yet another server-a three-tier architecture. While not all designs are as simple as this characterization portrays, you might think of the client/server versus Web approaches in terms of two-tier versus three-tier. Client/server applications frequently rely on ODBC for database connectivity, primarily because development environments such as Visual Basic and Delphi provide easy-to-use programming templates and easy-to-understand interface descriptions. If the computer programs running on an application server in a Web-enabled environment are written in Visual Basic, Delphi, or other non-Java language, they likely also use ODBC for their database accesses. Web-enabled applications written in Java, on the other hand, almost always use JDBC.

 

Configuring ODBC

 

Application designers and programmers like ODBC's simple interface, but its Achilles' heel is the difficulty of setup and configuration. Though giving a single client computer ODBC access to a database isn't onerous-Microsoft and other vendors provide a series of graphical windows for entering configuration data-setting up a large number of client computers can be quite tedious.

 

Two methods exist for configuring ODBC on a client computer. The first uses the ODBC entry in the control panel of a Windows 3.1, Windows95, or Windows NT Workstation machine. The other method involves editing INI files (Windows 3.1) or registry files (Windows95 and NT) directly.

 

Using either method commonly requires taking all or substantially all of the following steps: An administrator assigns a name to the connection between the client and a particular database server (i.e., its DSN [Data Source Name]), identifies the database server computer and the database on the server, provides the network address of the database server and specifies the account ID and password that will authenticate the connection to the database server.

 

In Windows95 or NT registry files, the keys relevant to ODBC settings are similar to the following two examples:

 

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\Pubs

 

 

The following example illustrates an ODBC.INI file for a Windows 3.1 machine that accesses a Microsoft Access data source:

 

 

[ODBC Data Sources]

MS Access Databases=Access Data (*.mdb)

MS Access 2.0 Databases=Access 2.0 for MS Office (*.mdb)

 

[MS Access Databases]

Driver=C:\WINDOWS\SYSTEM\SIMBA.DLL

FileType=RedISAM

SingleUser=False

UseSystemDB=False

 

[MS Access 2.0 Databases]

Driver=C:\WINDOWS\SYSTEM\ODBCJT16.DLL

DBQ=FLDBEHAV.MDB

DefaultDir=F:\ACCESS

FIL=Microsoft Access

JetIniPath=MSACC20.INI

UID=Admin

 

JDBC

 

JDBC is a collection of database access middleware drivers that provide Java programs with a call-level SQL API. Java applets and applications can use the drivers' API to connect to databases, store and retrieve database content and execute stored procedures, thus making JDBC a Java-enabled delivery mechanism for SQL. JDBC is to Java programs what ODBC is to programs written in languages other than Java. In fact, JDBC's design is based on ODBC's.

 

JDBC, developed by Sun Microsystems to allow Java programs to issue SQL statements, was initially a separate set of classes a Java program could load. To the developer, these classes abstract the database access process for the application designer and the programmer. At the end of 1996, Sun released version 1.1 of the JDK (Java Developer's Kit), and this latest JDK includes JDBC as a set of core (not separate) functions. Microsoft's and Netscape's latest browsers incorporate the JDK 1.1 standard.

 

Sun Microsystems has several goals for JDBC in addition to its role as a SQL API Java programs can use. Sun wants JDBC to conform to SQL standards, work through common database interfaces and be an easily-used, speedy interface.

 

Both ODBC and JDBC rely on the concept of a DSN to identify a named connection to a database. JDBC additionally uses a URL to express the type of connection (JDBC), the driver, the host, a port number and the DSN. The following is an example of a URL that an application might use to connect to a database. Note that the host sends the JDBC driver to the client. Administrators do not have to individually configure clients for JDBC access; all that's required is a Java-enabled browser.

 

 

jdbc:openlink://jupiter:5000/DSN=Accounts

 

JDBC Driver Types

 

The JDBC standard defines four types of drivers. These drivers differ in how much Java code each contains as well as the protocol each uses to communicate with the database server. As you learn about the JDBC driver types, keep in mind one of Java's precepts: Java security forbids the execution of native-code software by a browser-environment Java program (applet). This implies, among other things, that Java applets must forego the use of DLLs (Dynamic Link Libraries) and other local native code approaches. Applets cannot invoke ODBC or other vendor interfaces directly. They must instead use JDBC to access the database.

 

Driver type 1 is a JDBC-ODBC bridge that provides JDBC connectivity via ODBC drivers. ODBC program code and therefore database-specific client code must be loaded on each client machine that uses a JDBC-ODBC bridge. Note that Sun defined driver type 1 for expediency's sake until software vendors had created drivers of the other three types. It requires that administrators install and configure ODBC drivers on client machines.

 

Driver type 2 consists of an interface, written partly in Java, between Java programs and the vendor-specific database access middleware (for instance, Oracle SQL*Net or Sybase Open Client). A type 2 JDBC driver is typically a direct bridge to the proprietary call-level API of the database product (Oracle OCI, for example). It doesn't require the presence of ODBC. It does, however, require that administrators install and configure the database vendor's proprietary database access middleware.

 

Driver type 3 is a pure Java driver that, at the client, translates JDBC calls into a database-independent network protocol. At the server, a separate driver component translates the database-independent JDBC requests into database-specific, native calls. Type 3 drivers are thus able to connect Java-based clients to whatever types of databases a separate server-side driver has been developed for. Type 3 drivers require basic network connectivity at the client (a TCP/IP protocol stack), but they do not rely on the presence of vendor-specific middleware or ODBC. Note that type 3 drivers need to be sophisticated enough to handle a variety of networking situations, including firewalls. Also note that a Java program using a type 3 driver can claim to be highly generic-it will run on any Java-enabled platform with a TCP/IP connection to a database server. Among its other advantages, a type 3 driver is well-suited for use over the Internet.

 

Driver type 4 is a pure Java driver that converts JDBC requests into a database vendor's particular network protocol. It thus allows access by a Java client to that vendor's database product. Type 4 drivers are better suited for intranet use than Internet use. Because it depends on database vendors' proprietary database access protocols, a type 4 driver is more likely to come from a database vendor than from a third party.

 

JDBC Performance

 

An elegant and useful language, Java offers several benefits: portability, less likelihood of programmer errors, reusability of objects and integration with intranet/Internet technologies. However, Java is an interpreted language, meaning that you run a computer program (the Java Virtual Machine or browser), which in turn executes the instructions within a Java program. Because Java is interpreted, it sometimes presents performance problems to application designers. JDBC drivers written in pure Java, or partly in Java, sometimes operate slower than you might like. JIT (Just-In-Time) compilers, Java run-time optimizations and even Java-specific CPU chips are on their way to solve the problem. Java's performance will get better over time, and the current state of affairs is often more than adequate for user interface (data entry), where the computer running a Java Virtual Machine is several times faster than the user's ability to enter data via the keyboard. Nonetheless, for drivers written in Java, JDBC performance can be an issue.

 

When a JDBC-based applet first starts, it downlinks JDBC classes and drivers from the Web server. Full Java applications have wider latitude than applets in their use of native code. For an applet, the sizes of the class files, JDBC driver manager and JDBC driver affect performance at load time.

 

JDBC adds a number of modules to the Java run-time environment-a class file, the driver manager and the JDBC driver itself. If you choose to use the JDBC-to-ODBC bridge, the ODBC driver is another layer. Extra layers of code suggest extra processing and longer initialization times. You might choose to make the Web server computer also a database server, an approach that lets you connect an applet (or application) directly to the database via a JDBC driver written in pure Java. Alternatively, you might configure the Web server with a proxy connection to a separate database server computer, a solution that gives you more leeway in platform choices.

 

 

Problems with JDBC

 

Database and driver vendors need to make some improvements in JDBC before it becomes really useful. The first enhancement is scrollable cursors-at present, you can fetch a series of table rows in a forward direction, but you cannot move backward in a table to rows you've previously fetched. Reissuing a query just to reload a row takes a great deal of time, not to mention awkward program code.

 

JDBC drivers also need to be able to handle the deblocking of multiple rows returned in a single response message. Suppose the result set for a query is 20 rows of a table. Sending each row, one by one, can be a slow process. The database should be able to send multiple rows in a single response, and the JDBC driver should be able to deliver the next row to the application by deblocking the response.

 

 

Summary

 

 

In this chapter of NETWORK COMPUTING's Network Design Manual, we've examined database access middleware in general and explored ODBC and JDBC in particular. We've explained why ODBC and JDBC are important, how these interfaces work and how your organization can best use ODBC and JDBC.

 

Database Performance

 

You may be pointing the finger in the wrong direction when you accuse your data access middleware of causing performance problems.

 

The design of your database governs performance to a much greater degree than your choice of database access middleware. 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 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.

Normalizing the database, along with using SQL that's as simple in construction as possible, helps the SQL compiler portion of the database server process faster. 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.

 

The SQL compiler processes each SQL statement in five 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 QGM (Query Graph Model). 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.

 

An understanding of normalization and the SQL compiler's functions is key to making your database access as fast as possible. Look first at these considerations, as well as the tuning parameters for your database, before you blame your database access middleware for slow performance.

 

Appendix - Sources for ODBC and JDBC Drivers

 

 

ODBC

IBM

Oracle

Sybase

Informix

Microsoft

Information Builders

Cross Access

INTERSOLV

Visigenic

 

JDBC

Agave Software

Asgard Software

Avenir

Borland

Bulletproof JAGG

Connect Software

DataRamp

dbANYWHERE

IBM

IDS Software

INTERSOLV

JavaSoft

JetConnect

Microsoft

NCSA Decibel

OpenLink Software

Oracle Web Server

Reese's mSQL

Sybase

WebLogic

 

 

You can visit the Web site www.javasoft.com/marketing/collateral/jdbc_ds.html to get an up-to-date list of JDBC driver vendors.

[Top]
No.
제목
작성자
작성일
조회
98ODBC 와 JDBC 를 이용한 데이터로의 접근 (II)
정재익
2001-11-28
7421
86Modeling, Metadata, and XML (영문)
정재익
2001-11-19
4002
58ODBC 와 JDBC 를 이용한 데이터로의 접근 (I)
정재익
2001-11-04
6633
56Data Access Via ODBC and JDBC - 영문원본
정재익
2001-11-04
4873
26The Entity-Relationship Data Model
정재익
2001-10-20
5620
25The world of database
정재익
2001-10-20
6861
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.054초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다