• 53
• Networking
• Quick and Dirty Introduction to Networking
• Overview of Oracle Networking
• Host/Terminal Connections
• Client/Server Connections
• Database-to-Database Connections
• SQL*Net Version 1
• SQL*Net Version 2
• ODBC Versus OLE
• Other Middleware Vendors
• Some Sample Configurations
• Network Development Tips
• Summary

53

Networking

Welcome to the world of database networking. Some people come rushing to this world based upon promises that they have heard about magnificent performance or the capability to use their graphical user interfaces on personal computers for applications that access large corporate databases in a friendly and efficient manner. Others, however, are dragged into this world kicking and screaming. The architectures are so complex. You have to learn all those networking terms, and it takes forever for your support staff to get all those drivers loaded correctly so that you can access your databases. Finally, you have to rely on the network administrators in addition to the system administrators to keep the system up so that you can access your data.

The good news is that client/server and networking to access databases has moved beyond the point of radical, new technology and into the realm of the stable production environment. Sure, you have to pay some dues to learn the new terms and understand what hardware and software components you are using to get your information. I, for one, never want to go back to the days of the dumb terminal when it comes time to write a production application. Very few business users can be "wowed" by the traditional terminal interface where they have to learn to navigate through a series of menus or enter commands at the command line.

This chapter has an ambitious goal of providing you an understanding of networking as it relates to Oracle databases. Because many users are inexperienced in modern networking environments (using a Novell server to print your documents does not count), the terms serve as an initial stumbling block that you must overcome. The next layer of complexity comes from the fact that there are a large number of people out there designing network components. There are a few standards out there, but a number of vendors are competing with one another to set "the" standard. You need to be aware of the common products that are out there and how they work. Finally, Oracle itself presents a number of networking challenges. The challenge comes from Oracle's large customer base that has many different needs. As a result, Oracle offers a wide range of products that you might have to become familiar with, depending on what you need.

To approach these problems, I divided this chapter into the following sections:

• A quick introduction to some basic networking concepts.
  
  
• A discussion of the basic types of networking that you might encounter in your Oracle systems.
  
  
• Some coverage of the more common alternative database networking architectures.
  
  
• An overview of SQL*Net, Oracle's most basic networking product family.
  
  
• An introduction to some common middleware products (the software that enables your applications and databases to interface with your networks).
  
  
• An overview of how you can use gateways to connect Oracle to non-Oracle databases, such as IBM's DB2.
  
  
• A presentation of some sample database network configurations to give you a feel for some working environments.
  
  
• Finally, some tips on developing and implementing network database environments.
  
  

Because this is a challenging amount of material to cover in a limited number of pages, I should get started.

Quick and Dirty Introduction to Networking

Although it might seem too basic to some of you, I thought that I would define a computer network as a collection of hardware and software that enables multiple computers to communicate with one another. Network engineering types might think of more precise or elegant definitions, but I think that this is good enough for our purposes here. Figure 53.1 is a basic drawing that illustrates this definition. The concept is quite simple. You have two or more computers that are connected together in a manner to exchange information. Later in this chapter, I will go through the details of different network transmission standards and all of those annoying details. However, for now, focus in on the basic concepts of computers and a network that somehow connects them together.

Figure 53.1. Basic concepts behind a computer network.

As mentioned in the introduction to this chapter, part of the problem in setting up networks to support databases is dealing with all the components, versions, standards, and so on that exist in the networking market. The network folks have designed a very useful tool to diagram this process. They use a stacking diagram that shows various layers of functionality and how they stack on one another to build a complete network connection. They typically use a seven-layer model, which details more of the networking environment and also supports a wide range of application uses. Although their model makes an interesting discussion, in the interest of efficiency, I have simplified the model to deal more closely with the database networking models that you will come across. This database seven-layer model is shown in Figure 53.2. Networking purists might argue that I sometimes combine layers of the traditional seven-layer model and split a layer into two layers. However, this is how Oracle and other vendors tend to bundle their products, therefore it is an easier way for a DBA or developer to view networking.

Figure 53.2. Seven-layer database network stacking model.

The lowest layer is the networking transmission system. Typically, your local computer networking staff arranges this for you. The transmission equipment is designed to transmit the signals between computers. It usually transmits only a limited range of transmission formats (types of signaling) and protocols (the addressing and packaging of the transmission). It also tends to limit the types of physical connections that you can use to tap into the network with your network interface cards. Because this part of the network is usually arranged for you, it can actually help narrow down the large number of possibilities that you have to consider; as a result, the transmission formats and protocols are an excellent place to start when designing your systems.

The second layer up provides the physical and electronic interface between your computer and the network transmission system. This layer consists of some cabling and a network interface card that plugs into your computer. With servers, you typically do not have a lot of choices for a network interface card (usually one or two cards per transmission protocol). With PCs, you have a large number of choices for the various transmission protocols. The most important thing to ensure is that the card you choose is compatible with your operating system (especially when you are using newer operating systems such as Windows NT and Windows 95).

The next layer to consider is the transmission format. This layer tracks when your computer can make a transmission on the network and ensures that the signals are correct so that other computers can detect them. The transmission format is typically the domain of the electronics engineers who worry about signal voltages and such details. There are three types of common transmission formats:

• Ethernet: This is perhaps the simplest and most common signaling format. With this format, everyone transmits their signals onto a wire whenever they want to, and the systems detect and resolve any conflicts that arise. Typical speed is 10 million bits per second (bps), which is limited to about 3 million bps as a sustained transmission rate. A 100 million bps version is starting to appear on the market.
  
  
• Token Ring: With this network transmission format, everyone transmits signals when it is "their turn." Token-Ring networks are popular in IBM environments with their speed of either 4 or 16 million bps.
  
  
• ATM (Asynchronous Transfer Mode): This is a newcomer on the market. It is not common in local area networks (LANs) yet, but many manufacturers are starting to design and sell adapters using this technology. ATM relies on relatively high-speed burst transmissions.
  
  

The fourth layer (the transmission protocol) is similar to the envelope in which you send a letter by mail. This layer assembles the data you're transmitting into packets that can be routed through the network. Some of the more common transmission formats include the following:

• TCP/IP (Transmission Control Protocol/Internet Protocol): This is the basis of the Internet that you have probably read about. It started with a U.S. Government research project and turned out to be a standard that people could rally around. TCP/IP is the most common format that I have come across for client/server computing and connecting to UNIX-based computers.
  
  
• IPX/SPX: These are the transmission protocols used in Novell networking environments. Their use is typically limited to communications between PCs and Novell servers.
  
  
• NetBEUI: This is a protocol that some IBM and Microsoft networking products use for basic communications between PCs and LAN servers (such as Microsoft Windows NT). You will probably only encounter this protocol if you are running the workgroups version of Oracle.
  
  

I've had trouble using NetBEUI for client/server communications. I recommend that you consider using TCP/IP for client/server applications.

• SNA (System Network Architecture): This is actually more of an architecture than a protocol. It is the main environment for IBM mainframe shops.
  
  

The fifth layer is where the database-unique processing begins; I call it lower middleware. Middleware is a term that you encounter often in database networking. It refers to any supporting software that you need to connect your application or database management system to the networking utilities on your computer. I made up the term "lower middleware" to refer to products such as Oracle's SQL*Net that transmit database requests in a predefined format (such as TCP/IP) to the networking software on your host computer.

The next layer is what I call upper middleware. These products are designed to enable a variety of applications to interface with lower middleware products to interface with a database on a remote computer. Upper middleware is how I qualify Microsoft's ODBC (Open Database Connection) standard, which takes queries and transactions from products such as the Visual C compiler or the Microsoft Query product and formats them to interface with a specific lower middleware product such as SQL*Net. Some products, such as Oracle's SQL*Plus, already have a direct interface to the lower layer of middleware (SQL*Net) and therefore do not need a separate lower middleware software package.

Finally, at the top of my stacking model are the applications that most users really care about. Of course, on the server end is the database management system. On the client end is the financial accounting or sales forecasting system that the users interact with on a daily basis. As I discuss later in the chapter, you have the option of using the PC and network to emulate a dumb terminal. In this host/terminal scenario, the PC application that you run is a terminal emulator that connects directly to the network transmission utilities.

Why did I go through the previous discussion? One of the greatest challenges that I have faced in integrating computer systems is getting all the drivers, network interface cards, operating systems, middleware, and application packages to work with one another. Don't be too alarmed. They work very reliably once you purchase the right components. The trick is figuring out the right equipment and software and configuring them properly.

Figuring it out is where the database seven-layer model comes in handy. Each of the layers corresponds to a product that you have to purchase. Your trick is ensuring that whenever two layers touch one another, the products on either end are compatible. This compatibility has to be specific to your host computer environment (for example, a Hewlett-Packard H50 UNIX server or Packard Bell Force 101CD running Windows 95), and the exact versions of the products on either side must be compatible with one another (for example, SQL*Net TCP/IP Version 2.3 for Windows 95 and the Microsoft Windows 95 TCP/IP stack). Sales people tell you that the products work with a particular environment, but they might not know the "gotchas"—if you can only use a particular network interface card, for example. When you're picking products, I recommend that you draw out your database seven-layer stack and look at the product specifications to ensure that all components are compatible with one another.

Overview of Oracle Networking

How does Oracle fit into the networking picture? The good news is that Oracle provides a large number of networking options (unfortunately, for the systems folks and DBAs, that is also the bad news). Oracle started out as a host-based application. However, the company quickly recognized the advantages of splitting processing between the host computers and the increasingly intelligent workstations and PCs that were appearing on user's desks. This lead to client/server products such as SQL*Net and PC development tools such as Oracle Forms. The client/server environment evolved to include interfaces to non-Oracle development tools and databases through the neutral ODBC and OLE interfaces, which I discuss later in this chapter. Finally, databases have grown in number and size. With a single, huge database to process information, many organizations have elected to build a series of smaller databases that are connected to one another. This is where the Oracle SQL*Net, Distributed Options, and Gateway products come into play. I discuss each of these concepts in more detail in the next few sections. Figure 53.3 summarizes the Oracle networking environment.

Figure 53.3. Oracle networking overview.

Before I leave this discussion, I want to point out something that I always considered a little bit odd. If you are working with the Oracle Workgroups Server products on a Microsoft Windows NT server, you actually use networking software to communicate from your regular Windows applications (such as SQL*Plus) that are running on the NT server to your Oracle database, which is designed to run in native NT mode. This happens because regular Windows applications run under an NT subsystem knows as Windows on Win32 (WOW). Oracle linked the 16-bit and 32-bit sides of the Windows NT system via SQL*Net. If you use Named Pipes as your communications protocol, you do not need to have a network interface card installed, but if you use TCP/IP as the protocol, you must have a network interface card. It's just something to remember if you are working in this environment.

Host/Terminal Connections

A good place to start is where Oracle started—with databases located on host computers that are accessed using terminals (or PCs that were acting as terminals). Figure 53.4 shows the basic configuration of the architecture; it has the blessing of being very simple. The host computer system provides facilities to connect terminals and supports one or more terminal types (such as DEC VT-100) to which it can send output and from which it can receive input. This type of connection usually has the following characteristics:

• The interface is usually capable of displaying only text and not graphics.
  
  
• You typically control the interface through a series of menus or command-line inputs.
  
  
• You do most of the data entry via forms that rely on function keys or tabbing to special fields that confirm you are ready to enter data.
  
  
• The host computer performs all of the processing, which includes the display of information, the business calculations, and the database management system processing.
  
  
• You usually write the applications using form-generation applications for user interaction and report-generation utilities for printed reports.
  
  
• In many cases, the user interactive processing is supplemented with a series of batch reports that run at certain times of the day/week/month which users read through to obtain the information that they need.
  
  

Figure 53.4. Host/terminal networking.

Client/Server Connections

Do you hate the buzzwords that float through the computer industry? Client/server must be one of the most frequently used (and misused) words in the computer industry. Once upper information systems management gets a buzzword in its head about the ultimate solution to all problems in the computer industry, you can bet every salesman out there is going to scramble to find a way to say that his product is an implementation of that buzzword. Client/server is no exception to that rule of buzzwords.

The host/terminal architecture dominated the industry for a number of years and its use continues today; however, it ran into some limitations. Some argue that the big host computer vendors became a little lazy and stopped turning out new equipment at a rapid pace. Others argue that as the processing load continued to grow, it became impossible to build computer processors that were powerful enough to keep up with demand. Still others argue that the large computer vendors never produced enough units to keep the cost per unit down.

Whichever reason you prefer (or even if you prefer to think that it was a little bit of all these reasons), the client/server architecture has grown over the last decade to become a very popular alternative to the traditional host/terminal world. Oh, a lot of shops still do not consider anything other than mainframes, COBOL, and terminals; however, because you are reading this guide, you are probably not in this group. Now, I cover client/server in a little more detail.

Forming a definition is a good start to this discussion. Because I am not much of a theorist, I will stick to a simple definition. I define client/server architectures as systems where the computer processing load for a single application is distributed between multiple computers. In host/terminal computing, the terminal (or PC emulating a terminal) is only responsible for the presentation of the information. You can cite a number of examples that fit my definition which you might not want to call client/server, but it gives you the general idea. Figure 53.5 illustrates this general concept.

Figure 53.5. Client/server networking.

With a definition this broad, you could have a number of different distributions of labor that still qualify as client/server. Going back to my buzzword discussion, that is why you see a large number of different vendors with widely different products and architectures all claiming to be client/server. I guess that is part of free enterprise or something like that. What might be useful now are some sample distributions of labor that advertise themselves as client/server, as shown in Figure 53.6.

Figure 53.6. Sample distributions of labor in various applications.

As you can see, there are a number of ways to split the application. Some of your choices in designing your architecture are limited by the tools you choose (or vice versa). You might want to split up your processing based on the relative capacity (or costs) of your standard server and client platforms. You might also find that your users' demands for graphics and response time influence your decisions. For example, if you have a group of users who demand excellent graphical interfaces and fast performance, you could buy a moderately powerful server and some high-end PCs. As another example, if you already have an overloaded but paid-for host computer and reasonably powerful PCs, you could extend the life span of the host by converting some of your applications to perform the database processing. You could perform some of the calculations on the host server and then perform the rest of the calculations on the client. The following are some general characteristics of most client/server environments to consider:

• You use graphical user interfaces (where the users can access information using a mouse to click a button on the screen or select an item from a scrollable list) to interact with the users.
  
  
• You have a wide range of tools and connectivity products to choose from. This can be a blessing in that you have the capability to preview a number of different products to see which one is best suited to your individual needs. It can also make the task of integrating products a bit more challenging.
  
  
• The capabilities of the client workstation become an important factor in the overall application performance. Because most organizations have PCs that vary somewhat in computing and memory capacity, you might find that you have to upgrade certain PCs in order to run your new client/server applications.
  
  
• The capacity of the network (especially wide area network links used to connect different facilities) can be an important factor in determining the overall performance of the applications.
  
  

One final concept that you might need to consider is the difference between two-tier and three-tier client/server architectures. When client/server first started, you had just two machines (the client and the server). The client typically performed the display functions, and the server did most of the calculation work and database management. People liked the display capabilities of the PC but found that neither the PCs nor the server had sufficient processing capacity to perform complex calculations that might be necessary for detailed financial analysis or other such needs. These users did not want to move up to the more expensive larger computers (twice the processing capacity usually has much more than twice the price in the computer industry). Instead, developers came upon the idea of splitting the processing load for a given user and application among three different computers. The client machine still performed the user interface work; however, the other work was split between a database server and an application server. The database server focused on running the database management system, and the application server performed all the computations associated with the application. (See Figure 53.7.) The three-tier architecture is more complex to design and configure, but it can be the answer if you perform a lot of demanding computational work but do not want to invest in a single large computer.

Figure 53.7. Two-tier and three-tier client/server architectures.

Database-to-Database Connections

A logical extension of the concept of using multiple computers to perform the work of an application is the use of multiple servers to support the overall database processing capabilities for a large organization. For Oracle, these capabilities came in three phases. In the first phase, Oracle used SQL*Net to enable users to access data in tables that were in a remote database through a database link. Figure 53.8 shows the general concept of the database link. To summarize the general concept, for example, I am telling Oracle to "get me all of the rows in table X, which is located in database Y, that meet my criteria." You issue a command (create database link), which creates a simple alias (such as marketing) for the database that the users refer to. The create database link command captures all the details of the connection (addresses, user ID and password to connect with, and so on) and stores them for use when the users request this connection. One of the drawbacks is that a database link connects you to the remote database with whatever user ID and password are specified in the create database link command. The user accesses the data with the privileges of this user ID and not the ID of the user who is issuing the query or sending the transaction (which could cause security problems).

Figure 53.8. Database links.

Overall though, this first phase was a good start on splitting up the database processing work load; however, other issues soon became apparent. The biggest issue was that remote locations usually had to rely on a relatively slow wide area network communications link to access a database located on a centralized server. Another factor was that programmers had to be aware of all the database links and put them into the SQL statements they issued.

The next generation of database-to-database connectivity came with Oracle's distributed database option (option means that you had to pay extra for it). With remote field offices that are connected to the central office by relatively slow communications lines, you could implement smaller servers in the field that get a nightly download of data from the central server. You would then write batch routines that ran at night, figured out which records needed to be downloaded, and so on. You could do this yourself, but it is much easier if the database management system takes care of this stuff for you. That is the idea behind the distributed database option.

There are two general features of such a system. First, you can access tables located on remote databases as easily as you access tables located on the disks that are attached to your local server. Second, if you have multiple copies of the data to speed up processing at remote sites, you can set up the system so that Oracle synchronizes the data between the systems. Actually, quite a bit of logic goes into ensuring that if you make a change in a particular record, it is applied to all the copies of the database—especially when some of the changes might take minutes or even hours to finish. It is much easier to put that burden on Oracle rather than code it into your application. There aren't a huge number of installations out there using this option, but it could be very useful in certain cases. Figure 53.9 illustrates the Oracle distributed database concepts.

Figure 53.9. The Oracle Distributed Option.

Assume you have linked multiple Oracle databases that are located on different servers to distribute the database processing load of your organization. What more can you ask for? You now have the ability to take advantage of the large number of PCs sitting on people's desks, you can have multiple servers (located wherever you need them in your wide area network) performing the database management tasks, and you can even use a three-tier client/server architecture to distribute complex computational loads to a different server. Well, there is one other possibility that Oracle sales representatives usually do not like to consider. What if you have a lot of data on DB2 that you do not want to convert to Oracle, or what if you have a subsidiary or business partner that has adopted VAX RDB as its standard database management system?

The answer to this question comes in the form of gateways. Oracle makes a series of gateways that link Oracle databases to DB2 and other major database products. It seems reasonable that Oracle will continue to work in this area to increase the number of gateways to other vendor's databases. The key to these gateways is that they are conceptually similar to the distributed option. All you (or your DBA and system administrator) have to do is install this gateway product and provide all the configuration information about where the data is stored, what format it is in, how you network into the database, and so on. The gateway software then takes care of the details whenever your applications issue a query or transaction that affect data in these remote databases. As with many of the networking products discussed in this chapter, the gateways are not for everyone, but they can be a big help to those who need them.

Just for the record, many of the other database vendors and third-party developers have gateways that connect to Oracle databases. They have seen the same market opportunities to sell software to organizations that are not planning to convert all their databases into Oracle systems. The key to picking the right gateway is to ensure that it supports the versions of the databases you are using and that it fits into your computer and network architecture. These gateways are relatively new products, and I would suggest checking with other customers who are using the products or perhaps even setting up a test system in your organization before you commit to purchasing the gateway as the ultimate solution to all your problems.

SQL*Net Version 1

Now that I have covered the general concepts of database networking, it is time to get into some specifics. A good place to start is with SQL*Net Version 1, which was the first product in the Oracle environment to support networking. Designed to support client/server computing and database links, SQL*Net Version 1 was a good start on this process and was fairly reliable. In fact, I did not start using SQL*Net Version 2 until recently, so that is some indication that SQL*Net Version 1 was not a bad product.

SQL*Net is a product that I classify as lower middleware. It does not alter the format of the query to comply with Oracle standards or handle the details of transferring the results into a format that the applications can handle. Instead, it sends and receives data in the format that it understands and lets other middleware or the applications themselves get that information into its format. Some applications are designed to interface directly with SQL*Net. Oracle's database management system, SQL*Plus, Oracle Forms, and its other development tools are all designed to interface directly with SQL*Net. You can develop applications using Oracle's precompilers and products such as Oracle Objects for OLE that interface directly with SQL*Net. Figure 53.10 shows this direct interface.

Figure 53.10. Products interfacing directly with SQL*Net.

You might have noticed in the last paragraph that the word Oracle preceded most of the products that I listed. Although there are third-party products that are designed to interface directly with SQL*Net (such big names as PowerBuilder), many other developers yearned for an "open" standard that would enable them to write one application that could interface with a number of different middleware packages and database management systems. This is how the products that I call upper middleware were born.

The basic concept is simple. Design a product that has a neutral, published interface from applications and then design a neutral interface and connection software for a variety of lower middleware transport protocols. Figure 53.11 illustrates this concept. One of the ways that you can tell a good idea in the computer industry is when every big company jumps on an issue and creates their own "industry standard" to implement the concept. Such is the case with what I call lower middleware.

Figure 53.11. Upper middleware interfacing with SQL*Net.

The biggest name in this market that I have encountered in my experience is Microsoft and the Open Database Connection (ODBC) standard. It has the advantage of accessing a large number of clients (PCs that run Microsoft Windows, Windows NT, or Windows 95). It also came out early in the client/server evolution process and so there are already a number of applications written with this standard. Finally, Microsoft provides a series of drivers and utilities with most of the operating systems delivered, so you can keep the cost of an installation down (you would be amazed how fast the cost of a client workstation can go up when you start buying a large number of "optional" packages).

This is not to say that Microsoft is the only upper middleware vendor on the market. There are a number of vendors such as Intersolv and Openlink that have worked to produce drivers that are faster than the standard ODBC drivers or that work with more database management systems. Once again, you are faced with a number of choices. One is a low cost solution; the others might provide improved performance. Still others, such as Openlink, merge the functionality of SQL*Net with an ODBC driver to reduce the number of middleware products that you need to install and maintain.

I have one final note to add about middleware. Just when you were confused enough with the various options related to ODBC, Microsoft throws in another wrinkle. They incorporated the concept of upper middleware into a structure that enables you to dynamically share data between applications and even launch one application from within another. This standard is called Object Linking and Embedding (OLE—you know there had to be an acronym for it). Anyway, I discuss the differences between ODBC and OLE later in this chapter. For now, you should understand SQL*Net as a lower middleware product and how it interfaces either directly to SQL*Net-ready applications such as SQL*Plus or the Oracle database management system and upper middleware products such as Microsoft's ODBC.

Next on the agenda is a discussion of how SQL*Net is implemented. Once again, there is more than one answer. Remember: part of the sales appeal of Oracle is that it works on a wide variety of computer systems and is designed to do many things. For this discussion on SQL*Net, I split the discussion of implementation into two separate discussions. The first discussion is about SQL*Net on computers (such as UNIX or VAX VMS) that run multiple processes at the same time. The other discussion covers SQL*Net on computers that are basically single tasking (such as Microsoft DOS and Windows 3.1).

On multitasking computers (those running multiple processes), Oracle SQL*Net Version 1 is implemented as a listener process. Listener is a good name for this process. It merely sits there in background and listens for someone to send a signal into it. This signal could come from either the remote machine, in which case it picks up the signal from its interface to the network transmission protocol, or from the local application or database. When it is connected to the database, SQL*Net spawns (creates) a server process to handle the interface to the database management system and merely passes signals between this database interface process and the network drivers. Figure 53.12 illustrates the basics of this configuration.

Figure 53.12. SQL*Net Version 1 implemented as a server process.

Microsoft Windows 3.1 and DOS do not support multiple background processes. As a result, Oracle had to adapt its SQL*Net Version 1 product to fit within this environment. The problem is that memory is usually in short supply on PC clients, so you do not want to waste memory space by permanently loading the SQL*Net drivers. To get around this problem, Oracle uses a Dynamic Link Library (DLL in computer-speak). The software is loaded into memory when needed to facilitate communication between the applications and the network protocol drivers. This can actually be more convenient because you do not have to worry about starting and stopping the background SQL*Net listener process under this architecture. Figure 53.13 illustrates the SQL*Net DLL concept under Microsoft Windows 3.1.

Figure 53.13. SQL*Net Version 1 implemented as a DLL.

There is a lot that I could discuss regarding SQL*Net Version 1; however, because you might not even use this version of the software and it is usually transparent to developers and end users, I want to cover only one more topic: controlling and using the SQL*Net Version 1 listener process. As I mentioned earlier, if you are using SQL*Net under Microsoft Windows 3.1 and everything is set up correctly on your computer, you load the SQL*Net software automatically when needed. On computers where SQL*Net is a background process, you have to ensure that the background process is running before you use SQL*Net.

The good news is that Oracle provides relatively simple-to-use utilities to determine the status of this background process, start it, and stop it. The bad news is that you have to know the name of the process and the way to access it. To start off with, process is a term that works under UNIX. Under Windows NT, the more proper term is service. Novell refers to Novell Loadable Modules (NLMs). For the name of the process (service or whatever), you need to see what name your database administrator set up when installing the software. Which utility controls the background process is also important to know. Under Windows NT, you access the Services Utility under Control Panel to start up the service. Under UNIX, you use the tcpctl utility (with the start, stop, or status options) to work with the SQL*Net TCP/IP background process. The best advice that I can give you with the wide variety of configurations available is to check with your database administrator, system administrator, or whoever installed your SQL*Net software.

Finally, the topic that is most interesting to the average user is using SQL*Net Version 1 to access remote databases. This process is relatively simple; all you have to do is specify the database address in the manner appropriate to your application. The SQL*Net Version 1 address is composed of three parts. The first part tells SQL*Net which protocol you are using (T = TCP/IP, P = named pipes, and so on). The second part of the address shows which computer the desired database is on. In TCP/IP, for example, you can either use an alias defined in the hosts table (such as marketing) or the Internet address of the server (such as 10.15.20.25). Finally, you need to specify the SID (Oracle's system ID) for the database that you want. The SID is set up by the DBA when creating the new database, so that person has to tell you the name of the SID.

SQL*Net Version 1 enables you to create aliases for your remote databases. It also enables you to define default local and remote databases that are accessed when the user does not specify the desired database. The following is a sample call to the SQL*Plus utility that accesses the jdoe account in the MKT Oracle instance on the marketing server using the TCP/IP protocol:

$ sqlplus jdoe@t:marketing:MKT

I have one final note. If you are using an upper middleware product such as ODBC, you define the SQL*Net Version 1 address to the ODBC administrator and then use the ODBC alias when you reference the data source in your applications. Basically, your job is talking to ODBC. ODBC is then responsible for routing your request to the appropriate database.

SQL*Net Version 2

If you understand the basic concepts of SQL*Net Version 1, then you are well on your way to understanding SQL*Net Version 2. It is not really a revolution in the concept of how Oracle networks its products. It still interfaces directly with SQL*Plus and all the other Oracle tools (you do not even have to change any configuration files on those development products). It still interfaces to ODBC and OLE. The main differences between Version 1 and Version 2 are the following:

• In SQL*Net Version 2, the DBA has to prepare a series of files that contain all the connection information for the various data sources that are available. These data files associate a simple alias (such as main_sales) with all the information such as the SID, host computer, protocol to use for communication, and so on. All the users have to worry about, once they get copies of these files from the administrator on their computers, is what the aliases are and what information is stored in each of these databases.
  
  
• SQL*Net Version 2 can interface with a feature of the Oracle7 server known as the Multi-Threaded Server. In Version 1 of SQL*Net, you allocate a dedicated process for each user who connects to Oracle via SQL*Net. This process stays open until the user disconnects, which could be the end of the day for some users. This can become a problem when you have a large number of users accessing a system where they might be connected to their application all day even though they are not actively working with the database. The Multi-Threaded Server concept enables the SQL*Net users to share a series of server processes, each accessing the server process only when he has an active query or transaction.
  
  
• The later versions of SQL*Net Version 2 are much better at detecting when a user gets disconnected from Oracle without issuing a proper command to disconnect (for example, if they get bored and hit Ctrl-Alt-Delete in the middle of a long query). Under SQL*Net Version 1, this can cause a shadow process to hang around eating up large amounts of processor time (these processes run away and can take up the entire CPU). SQL*Net Version 2 has some time-out parameters that enable you to eliminate this problem.
  
  
• The names of the controlling processes are different between the two versions of SQL*Net so that you can easily tell which version is running.
  
  
• Finally, you really should use the Oracle network management tools to create the configuration files. You could edit the aliases in the old SQL*Net Version 1 files if you were ambitious (the default remote connection, and so on). However, unless you are really sure of what you are doing, use the Oracle utilities to make your network configuration files. They are somewhat complex in syntax and the names of the fields are not always obvious.
  
  

SQL*Net Version 2 is a sound product that provides the same basic services as SQL*Net Version 1. I caution you to allow adequate time to get SQL*Net set up properly. I have found that it always takes longer than you think it will to set up the SQL*Net configuration files correctly, start the background processes, and complete any upper middleware configurations such as ODBC. Even after you have done it several times, there seem to be a few quirks about each new type of server or each new network stack on the clients.

ODBC Versus OLE

Earlier in this chapter, I gave a brief introduction to ODBC and OLE, two standard upper middleware package standards created by Microsoft. There are other vendors who have created their own ODBC software packages because that is a utility that is separate from the basic operating system. I am not familiar with any users who have created their own version of OLE; however, Oracle has created Objects for OLE, which enables you to write software that uses OLE to interface with an Oracle database.

What are some of the differences between ODBC and OLE? The biggest, in my mind, is that Oracle supports OLE, and many of the other big database vendors do not. As a result, if you are writing an application that you want to sell to a broad audience with multiple database management systems, OLE might limit your options. Also, because ODBC is older, you might be able to find more third-party development software to support your development effort.

Most of the folks I have talked to indicate that they found OLE several times faster than ODBC. Some people on the ODBC side might counter that the initial Microsoft ODBC drivers were not as optimized as they could be and that you can increase ODBC performance by a good bit. Some of the OLE concepts (such as launching a small database application from within a word processor or spreadsheet) are very difficult to implement using ODBC. In summary, I see OLE as far more capable in the long run, and ODBC as more practical in many circumstances, at least for the next year or so.

Other Middleware Vendors

Another point I mentioned earlier is that there are other middleware vendors you might want to consider. Why? Perhaps you are working with an operating system such as Windows 95 right after it was released, and all the beta test drivers that you were shipped are buggy. You desperately want to get a product out the door, and you are searching for that one driver that will enable you to complete the porting of your software package to Windows 95. Also, you might be in a situation where you are writing an extremely demanding application, and you are pushing to optimize performance whenever possible. In this section, I present a brief overview of some of the middleware vendors on the market to give you a feel for some of the alternatives. After this guide goes to press, another vendor might release a great product that could be the one that is right for you. You should perform the usual research steps (vendor search, magazine reviews, and so on) to see what products are available to you.

I'll start the product review with the products from Oracle shown in Figure 53.14. The basic middleware that Oracle provides is the SQL*Net product, which I classify as lower middleware. Oracle also offers its own set of ODBC drivers to compete in the upper middleware market. The Oracle ODBC drivers provide an interface to Oracle databases, so if you are trying to write applications for a wide variety of databases, you might still need to purchase additional ODBC drivers for those other databases. One thing to consider when working with new releases of Oracle is that the Oracle drivers might be more up to date than some of the third-party drivers, although the ODBC standard shields you from some of the internal changes that occur in the databases themselves. Oracle also markets its Objects for OLE product to serve as upper middleware for those who want to use OLE.

Figure 53.14. Middleware products from Oracle.

Of course, Microsoft wrote both the ODBC and OLE upper middleware standards. The Microsoft development tools come with ODBC drivers and OLE support, in addition to development tools from other third-party vendors. These products compete with the Oracle ODBC drivers and Objects for OLE. Microsoft supports networking to its SQL*Server product as part of the operating system, so I guess you could call Microsoft's drivers lower middleware for a non-Oracle database.

Next on the list of representative vendors is Intersolv. What I have worked with is their ODBC drivers. They provide drivers for a range of databases, often before the database vendors come out with their releases. They also have the distinction of writing the drivers that are provided by some of the other database vendors. Intersolv owns the Q+E product line, which was one of the first tools that enabled you to connect spreadsheets such as Microsoft Excel to remote databases. Again, the main focus for Intersolv is the upper middleware ODBC market.

Last on this short survey of some of the vendors in the middleware market is a British firm called Openlink. They are somewhat unique in that they bundle together the functions of upper and lower middleware into a single package. You have to run their products on both the client and the server, but in return, you get to avoid installing and maintaining a number of lower middleware products. The basic architecture is shown in Figure 53.15.

Figure 53.15. Openlink combined middleware concept.

Before closing this section, I want to mention that there are other standards out there competing in the middleware market. Several vendors in the UNIX and Apple worlds favor different standards that are similar to ODBC and OLE. Each touts the benefits of their framework over the others. If you spend a lot of time working in one of these environments, you might want to consider these competing standards. However, if you primarily use PCs as clients, ODBC and OLE are the two standards that you will encounter the most. I view it as though I am living with the standards set by the industry and not making them. If a standard is adopted by the rest of the computer industry, it does not matter how many wonderful features the competitors have; they will probably fade away due to lack of sales in a few years. The only difficulty that you face is figuring out where this fickle industry is headed.

Some Sample Configurations

So far in this chapter, I cover most of the networking topics that you probably need as a developer or user of a networked Oracle system. What is missing is that one single drawing which shows you all the pieces you need to buy for this networked Oracle system. I can't provide that drawing because, as I mentioned earlier, there are too many different options. You can now use Oracle development tools with non-Oracle databases and vice versa. What I thought would be useful here is a series of sample configurations that might give you some ideas about what working networked database configurations look like.

I have one big note of caution. You have to look at each type of system configuration on an individual basis. There are so many unusual reactions between different versions of the various products that you really should test things in your environment before you can be certain that it will work. There are things that you might not anticipate—for example, if none of the wide area network transmission equipment in your company is capable of transmitting the TCP/IP protocol or something really strange such as that. Anyway, enough said; just test everything out using demonstration software or limited quantities whenever possible before you commit to rewriting your entire application architecture using a given set of products.

With that traditional cautionary note out of the way, look at the first sample configuration. Because I started my discussion of network access to database with the host/terminal architecture, I thought that I would present an example of this environment. (See Figure 53.16.) Note that even though I use PCs to access the VAX Oracle database, they are emulating dumb, non-graphics terminals. The applications developed use the traditional, character-based interface where you hit F3 to commit your changes or F4 to exit out from where you are. Note that all the application development software and completed applications reside on the Oracle server (in this case a VAX) and that the linkages from the application to the database management system are already set up (you do not need to configure any links as you would in SQL*Net).

Figure 53.16. Host/terminal example.

Next on my list of samples is a basic client/server system, illustrated in Figure 53.17. In this case, I show the Oracle tools set (which now goes by names such as Developer/2000 and Oracle Forms, but who knows what the marketing folks will call it next year) on PCs interfacing with an Oracle database on a UNIX server. Some key points to note about this architecture is that you do not have to use an upper middleware product on your clients (because these tools are all designed to interface directly with SQL*Net). Oracle tools have recently started to also support an ODBC interface. I guess Oracle wants to sell its development tools even to customers who use other database management systems.

Figure 53.17. Oracle development tools interfacing with an Oracle database.

Next, I cover a system that uses upper middleware—in this case, ODBC—to interface with an Oracle database located on a UNIX server, as shown in Figure 53.18. This environment uses the Microsoft C++ compiler with a set of third-party ODBC drivers from Intersolv. The database is Oracle 7.1, located on a Sun UNIX server. One key point to note from this example is that you must ensure that your ODBC driver is compatible with the exact version of networking software that you are using (you might have trouble, for example, if you move your application to Windows 95). Also, when using a development tool such as C++, you must be sensitive to details such as the data types that the ODBC driver is returning to your application (it does not map to character, VARCHAR, and NUMBER, as in the Oracle database). This detail work can take up some time, so you might want to run some tests up front to figure out the calls and returns that you will be dealing with.

Figure 53.18. Example of an ODBC environment.

Finally, I thought it'd be useful to include an example of a distributed database environment. I have not encountered a large number of these configurations, but that could change in the future as gateway and distributed database processing technology continues to improve. Figure 53.19 illustrates one such system. The basic concept behind this system is that the two databases (perhaps one is in Cleveland and the other is in Chicago) are linked together. Users interact with their local database and let the distributed option take care of ensuring that the remote database is updated. Delays in the transmission of the database between the systems do not delay the user's processing.

Figure 53.19. Example of a distributed database environment.

Network Development Tips

Here, I summarize some of my experiences dealing with networked database environments into a series of tips. Think of this information as highly condensed examples. Anyway, I urge you to at least consider the following directions:

• I first recommend that you do not cheat and try to implement the production environment late in the development cycle. It is tempting to say that you will develop everything on a stand-alone workstation and then convert it to the client/server environment the week before testing (you can certainly get it working in a week, right?). Anyway, I like to spend the time getting the true working environment set up in the beginning for several reasons. First, you might find that some of your drivers and products are incompatible. You might have to troubleshoot and then order replacement products, which can take an amazing amount of time. Also, your developers need immediate feedback during development when they write software that is not optimized for the end-user environment. For example, imagine a piece of code that pulls down to the PC all the data from a very large table (with lots of long text fields) located on the server just to calculate the sum of a single number field. This code would be extremely slow over a busy network. The developer could use a built-in Oracle sum function and only have to return a few bytes of data with the result.
  
  
• Now, I give you a recommendation where I do not always practice what I preach. I recommend that you do not deviate from common computer environments and tools. I like to live on the bleeding edge of technology, but that makes it extremely challenging to get those production applications out on time. Incompatibilities of drivers and tools with new operating systems such as Windows 95 requires that you have a fair amount of experience dealing with lower-level system details and a wide range of access to vendors. In an ideal world, you would have a lab environment where you could test and get new products working and then use them in your production and development environments.
  
  
• Know about your network monitoring and testing utilities. Many errors, especially those in the client/server environment, translate into something similar to "There is something, somewhere that is not right." I hate those error messages but must live with them. What you then do is use some of the low-level testing utilities to go step by step through the system to see where the problem lies. For example, if I am setting up the C++/ODBC to UNIX database via TCP/IP environment and encounter a problem, I have to use several utilities to test the connection. I typically start by using the ping utility that is common to most TCP/IP environments to ensure that I have basic networking connectivity between the two computers. I then use Oracle Net Ping to see if my two SQL*Net processes are communicating with one another. Next, I use an easy ODBC access tool such as Microsoft Query to see if I set up my ODBC configuration correctly. Finally, I create a new Visual C++ application using the App Wizard to see if I could access the database from a basic C++ environment (if I get a table list when selecting the data source in App Wizard, it is a good sign that ODBC is set up correctly). Anyway, as you can see, you might have to use a number of products to see where your problem lies. What you are trying to do is find a simple tool that tests each of the interfaces in the network stack one at a time, starting at the bottom.
  
  
• Another favorite tip of mine is that you should get familiar with the various vendor bulletin board systems, Web sites, FTP sites, and so on. Having a PC with a reasonably fast modem and an Internet connection can save you an enormous amount of time. In a few minutes' time, I have downloaded drivers from the U.K. that got the developers going—products that might have taken a week or more to get through the mail. Your problem could be as simple as needing a special patch from a specific vendor if you have a certain old model of PC.
  
  
• Have help lined up in advance when possible. This can take a number of forms. Perhaps you can have a local vendor or contractor who has done this before come in to help you set things up. You should also have support from the product vendors, at least during installation. It is very useful to keep all those guides and purchase order copies in a file so that when you have problems, you can get the answers you need quickly.
  
  
• Bring in all your local experts from the start. This list of people might include network administrators, system administrators, database administrators, and others. If they participate in the process when you are ordering and setting up the configuration, they can often save you a fair amount of time and money. They can tell you little things such as "We are on Novell 3 now, but we will be upgrading to Novell 4 next week so you'd better be ready for it." Most information systems shops are so busy that it is often impossible for any one person to be aware of all the things going on. Get input from the individual experts early and often.
  
  
• Start with simple applications when moving to a new environment. You could try to make your first client/server application the mission-critical executive information system that the CEO and all the vice presidents use every day. If so, you have a lot more guts than I do. One example of a good first system to develop in a new environment is the system that tracks the time spent on various projects in the information systems group. This has several advantages. First, it is relatively simple so you don't have to spend a lot of time on it. Second, your fellow information systems staff will probably be a lot kinder if there are performance problems or bugs than your company executives would be. Finally, if the system has a lot of problems, you are not affecting the company's bottom line while you work out all the bugs. It is a good place to learn some of the "how-tos" and work with issues such as performance.
  
  
• Always leave yourself time for problem resolution with the environment itself. I have seen setup take only a day with simple environments that have been done before with only a few vendors involved, and I've also seen setup take weeks or months for cutting-edge technology. An assurance from your sales representative that everything will work is not something I would bet the project on. That sales representative might be completely truthful about never having had a problem setting up the software, but he might not have tried it in an environment like yours.
  
  
• Finally, go through the database stack model and spec sheets when planning the system. You have to get down to the detail level of things—such as Windows NT 3.51 on the server with the NT 3.51 TCP/IP stack interfacing to Version 2.2 of SQL*Net and so on. Whenever possible, get evaluation copies of the software and hardware to test before purchasing the products. Another alternative might be to contact others who have the same environment that you do to figure out what worked and did not work for them. It is tempting to move quickly through the planning steps to get a project going, but it can cost you a lot of troubleshooting time later if you miss some things.
  
  

This list of tips could continue; however, I think that these basic suggestions should get you through most of the hurdles. Do not take this as the voice of doom. I have always been able to get the database network environments I work with operational. Sometimes, it was a challenge to get things set up, especially in leading-edge environments; however, things usually worked well once the process was finished.

Summary

This chapter took on an ambitious project. To try and describe the wide range of networking options that are available in Oracle in the space of a single chapter is somewhat like trying to summarize War and Peace in a single paragraph. The technical details of the various Oracle products fill up many different guides in their manual set with detailed configuration options and installations instructions. Obviously, with the wide range of products that Oracle provides and the number of different environments it supports, I could not even begin to look at all the options here. Instead, I chose to focus on providing an overview of database networking. The goal was to give you a feel for all the products that have to work together for that seemingly simple connection from your desktop to the database.

Along the way, I tried to provide some examples and share some of my experiences. This is a tough chapter to come up with detailed examples (the list of configuration options that I had to set on just one configuration would probably fill a chapter in itself). I did try to summarize some of the more common tricks that I have used in the section "Network Development Tips." As a final note, I urge you to consider giving yourself an adequate amount of time when setting up a networked database environment. It is easy to think that because everything is vendor-provided, all you have to do is open the boxes, load the software, and begin working. I found that it often takes days to get the settings of new drivers right and get all the kinks worked out of the architecture. Of course, if you already have dozens of similar installations and you are simply copying a working configuration, you might be able to get working right away. On the other hand, if you have a slightly (and I mean slightly) different configuration, you should leave yourself some time for testing and problem solving.