Anyone who is considering ODBC as a connectivity tool should also be aware that ODBC does not support all of the SQL extensions that a database server may offer. In order to accommodate these nonstandard features, ODBC offers a back door that the programmer can use to send native API commands directly to the target database. Again, this procedure is not recommended unless the feature is absolutely necessary to the application. Mixing ODBC calls with native API calls creates a confusing jumble of access methods and makes the application much more difficult to support. Another obstacle with this approach to ODBC is maintaining the portability of the application. As new releases of the database add new extensions to the SQL, the ODBC component must be changed to accommodate these enhancements.

Some people argue that the “least common denominator” approach to ODBC SQL is too limiting. They state that learning the common syntax and facilities of SQL is too time consuming, and that a generalization of SQL would remove the most powerful features, making the system far less functional.

Intersystem Connectivity

In systems that allow cross-database access, a very common method of distribution uses the idea of horizontal partitioning. For example, customer service organizations commonly allow their remote sites to maintain customer information while still maintaining a location-transparent access mode to every customer, regardless of their physical location. The horizontal partitioning is achieved by taking a subset of each remote site’s customer table, and populating a master lookup table that is accessible from any node in the distributed system, as shown in Figure 8.2.

Figure 8.2  Horizontal data partitioning.

In a Unix-based distributed system, the cron utility could be used to schedule a periodic refresh of the master table. The cron utility is a time-dependent task activation utility, and starts tasks at predetermined dates and times. An SQL script would automatically extract customer_name from the remote site and repopulate the master customer table, leaving the customer details at the remote site. The Oracle SQL might look like this:

/*  Delete remote rows in the master table... */

DELETE FROM customer@master
WHERE
LOCATION = :OUR_SITE;

/*  Repopulate the master table...  */

SELECT customer_name, ':our_site'
FROM customer@:OUR_SITE
AS
INSERT INTO customer@master
VALUES customer_name, site_name;

Once populated, the master look-up can be accessed by any node and used to redirect the database query to the appropriate remote database for customer detail, as shown in Figure 8.3.

Figure 8.3  Dynamic location transparency.

Because of the dynamic substitution in the SQL, a common application can be made to access any customer in the federation regardless of the location—without any changes to the application code.

Dynamic transparency is especially useful for situations where remote locations have “ownership” of the data, while a corporate entity requires access to the data at a central location.

The Internals Of Oracle’s SQL*Net

In its most basic form, SQL*Net is a software tool that allows a network of Oracle clients and servers to communicate transparently on top of any underlying network topology or protocol using SQL. Although SQL*Net is a very robust and sophisticated tool, you must appreciate the inherent complexity that goes along with the flexibility of SQL*Net. This section provides a no-nonsense overview of the SQL*Net architecture. All of the examples are based on Unix.

Due to the sophisticated architecture of SQL*Net, it is not trivial to install on the client or the server. For Unix systems, the following files are necessary to operate SQL*Net 2.0:

•  /etc/tnsnames.ora—Used for outgoing database requests, this file contains all of the database names (sids) running on the processor, as well as the domain name, protocol, host, and port information. When a new database is added to a box, you must update this file (changes to tnsnames.ora become effective instantly). Note: The SQL*Net version 1.0 equivalent is /etc/oratab.

•  /etc/listener.ora—This file contains a list of local databases for use by incoming connections. When you add a new destination database to a Unix host, you must also add it to this file.

•  /etc/hosts—This file lists all of your network addresses.

•  /etc/services—This file lists all of the SQL*Net services.

In version 2.0, Oracle has added several important enhancements to SQL*Net. Aside from the badly needed bug fixes, SQL*Net now allows multiple community access. A community is a group of computers that share a common protocol (such as TCP/IP to LU6.2). In addition, the Oracle7 database engine now defines a multithreaded server (MTS) for servicing incoming data requests. In the MTS, all communication to the database is handled through a single dispatcher. In SQL*Net version 1, a separate process is spawned for each connection. These connections are easily viewed by using the Unix ps command.

When upgrading from SQL*Net 1.0 to SQL*Net 2.0, you should be aware of subtle differences between how the two versions handle communications. (See Figure 8.4.) SQL*Net version 1.0 uses an orasrv component on the destination database to listen for incoming requests, while SQL*Net 2.0 uses a process called tnslsnr (TNS listener). In addition, SQL*Net 1.0 cannot use the multithreaded server.

Figure 8.4  The two versions of SQL*Net.

When a connection is made to SQL*Net, it passes the request to its underlying layer, the transparent network substrate (TNS), where the request is transmitted to the appropriate server. At the server, SQL*Net receives the request from TNS and passes the SQL to the database. “Transparent network substrate” is a fancy term meaning a single, common interface to all protocols that allows you to connect to databases in physically separate networks. At the lowest level, TNS communicates to other databases with message-level send/receive commands.

On the client side, the User Programmatic Interface (UPI) converts SQL to associated PARSE, EXECUTE, and FETCH statements. The UPI parses the SQL, opens the SQL cursor, binds the client application, describes the contents of returned data fields, executes the SQL, fetches the rows, and closes the cursor. Oracle attempts to minimize messages to the server by combining UPI calls whenever possible. On the server side, the Oracle Programmatic Interface (OPI) responds to all possible messages from the UPI and returns requests.

No UPI exists for server-to-server communication. Instead, a Network Programmatic Interface (NPI) resides at the initiating server, and the responding server uses its OPI.