Remote Connections over TCP/IP
On UNIX systems, the task of connecting the client and server together was strictly a manual task. The DBA had to manually edit two ASCII files, one of which needed system administrator privileges to modify (/etc/services). The manual editing usually occurred only on the UNIX system, with the GUIs making the setup on Windows machines a little bit more automated. Since most UNIX machines support some type of Windows clients, most DBAs have worked with the GUIs.
In order to understand the relationships between all of the files used for TCP/IP communications, refer to the following diagram.
Files Used in TCP/IP Remote Connections
There are several ASCII files that must have proper entries to allow the clients and servers to communicate. If any of the entries are wrong, the client and server will not communicate correctly.
Onconfig
The onconfig file is located at $INFORMIXDIR/etc/$ONCONFIG if $ONCONFIG is set in UNIX system or $INFORMIXDIR/etc/onconfig if the $ONCONFIG environmental variable is not set. Onconfig is the default filename unless overridden by $ONCONFIG. The onconfig file (by whatever name it is called) is the file used to store all of the instance's tuning and configuration parameters, so it should be quite familiar to DBAs. There are a few lines of interest from the communications viewpoint:
# System Configuration DBSERVERNAME ol_server #Name of default server DBSERVERALIASES ol_localhost #List of alternate servernames NETTYPE onsoctcp,1,10,NET #Override sqlhosts nettype
The DBSERVERNAME parameter is the name by which the server is identified. This name is not the name of the host machine. It is the name of the actual Informix server. The DBSERVERALIASES is used to provide different server names for different connectivity methods.
Services
In both UNIX and NT systems, the services file is an operating system-owned file called /etc/services on UNIX systems and %WINDOWS%system32driversetcservices on pre-WindowsNT 4.0 systems and %WINDOWS%services on NT4.0 systems.
The services file is located in /etc/services on most UNIX systems. It may or may not be found on WindowsNT systems depending upon whether or not another application has used it. Its location also seems more variable on Windows systems, as there does not seem to be a standard with file locations in third-party TCP stacks. If you do not find a services file on NT, look for the sample file, services.sam, and copy it to a file named services in the same directory.
The services file is a TCP/IP file that provides information about how and where commonly used TCP/IP communication functions are handled. Don't confuse this with the concept of services that run on Windows NT systems. NT systems may provide both a TCP/IP service and an NT service that work together. An example would be the NT ftp service, which monitors and communicates over the TCP/IP port reserved for ftp. A port is simply an area of memory which that connections will connect to. Ports are also known as sockets or services. Actually, socket communication is more complicated than just talking over one port, but that's only interesting to people who are programming the sockets programs. You'll be safe enough if you just understand the basics. TCP/IP provides built-in ports for such things as:
telnet
ftp
finger
nameservers
time servers
talk
Each of these services is assigned a port. For example, the telnet program usually connects over port 23 and ftp usually connects over port 21. Informix needs ports to allow the clients to communicate with the servers. These ports are not included in the "standard" ports that come with TCP/IP, so they have to be inserted. On the server end, the services file is usually modified as part of the Informix install process. Here's a sample from an NT services file:
sta7_sfs_jx2 3616/tcp # Informix on HP/UX system svc_crm2_tli 1599/tcp # Informix on UNIXWare system turbo 1526/tcp # IDS - Personal Edition turbo2 1527/tcp # second port for local connect srv_agent 1530/tcp # INFORMIX-OnLine Server Agent infconn 4000/tcp # Informix on Stratus Polo
The format is service_name, white_space, port number/tcp. Anything after the hash symbol (#) is a comment. You will see services in the services file that use "/udp" instead of "/tcp." These are datagram ports. They are useful to somebody, but I don't think 1 know anybody who really understands (or cares) what they are. All of the ports we are interested in are "/tcp" ports.
You can set up as many services for Informix as you want. Windows NT Informix installs usually default to the "turbo" service on port 1526/tcp. The "turbo" is a holdover from early (4.X) Informix engines that went by the name of "Informix Turbo." As an aside, this is the source of the "tb" prefix in the OnLine utilities. There are three limitations:
The port numbers must be unique.
The port numbers must be greater than 1000. Ports numbered less than 1000 are reserved for "standard" TCP/IP services.
The last line of the services file should be blank. This last item is purely superstitious behavior on my part. There are several systems that have problems with the last line of the file, and if you make it blank, it will never cause you problems.
In the example file above, the first four entries are different services for different machines. The last entry is a service for the GUI administration tools on NT, which needs its own unique service port to connect with the engine for maintenance activities.
Why would you want to have different services for different host systems? For one thing, systems that have more than one ethernet card can control which card to use for particular remote connections by assigning them different IP addresses and giving them different service names and ports for each connection. This would allow the DBA or the system administrator to balance the loads across multiple cards. Another reason to do this would be to simplify the monitoring of network communications. The database administrator can use the netstat command to view TCP/IP communications at the operating system level. Different options to netstat can show the port number over which the TCP/IP conversation is taking place. If different machines use different ports, it will be easier to decipher the entries and it will be easier to tell which TCP/IP sessions are talking to which systems. There could also be some performance gains in a heavily loaded system by spreading the work across multiple ports, but the system would have to be pretty heavily loaded to make significant gains.
The services file is an indexed lookup file. Given a service name, the system internally performs a lookup into this file in order to find which port number to use. IDS engines beginning with 7.10UD1 allow you to completely bypass the services file by including the port number rather than the name in the Informix configuration files. This saves the time needed for the lookup and can make it easier to understand what is going on. This can also be a useful debugging trick if you having problems getting the services name to match to the right port. If the connection works with the port number but not with the port name, you know that you must have a problem somewhere in the services file.
Hosts
The hosts file is used by TCP/IP to associate a computer's system name to an IP address used by the ethernet board that provides the networking connection. In UNIX systems, the hosts file in usually found at /etc/hosts. In Windows NT systems, it is an ASCII file located at %WINDOWS%system32driversetchosts. In Windows 95 systems, it is located at %WINDOWShosts. The format of the file is simple. An IP address followed by a hostname followed by an optional alias, all separated by white space.
# IP Address Hostname Alias # 125.95.12.01 the_client 125.95.12.02 the_server
TCP/IP uses the host file to allow a user to communicate with a system by name rather than IP address. This is the only thing that Informix uses it for. As with the services file, IDS systems beginning with 7.10UD1 can bypass the hosts file by using the actual numeric IP address rather than the hostname.
The best way to test whether the hosts file is set up properly is to use the ping command. In many UNIX systems, the ping command is not in a typical user's path. If you get a "ping not found" error, try using the full pathname for ping. This is usually /etc/ping in UNIX systems. First, try pinging the host with the following command:
ping the_server (using the appropriate name, of course)
If you don't get a response from this command, try pinging the server using its numeric IP address:
ping 125.95.12.02 (using the correct IP address)
If both ping commands fail, you'll never establish connectivity until you make it work. See your system administrator or network administrator and fix your networking before going any further. If the ping by number works but the ping by name doesn't, you have a problem with your hosts file. Either fix the file or simply use the IP numbers rather than the name if your are on an IDS system later than 7.10UD1.
This pinging needs to work both from client to server and from server to client. Make sure that both sides of the communication are able to ping each other. The following flowchart illustrates the process of testing your underlying network communications using the ping command:
Sqlhosts
The sqlhosts file is the linchpin of Informix connectivity. It is in this file that we tie together the data from the onconfig, services, and hosts files. It is here that we instruct the engine how to connect to the servers in various ways, what transport mechanisms to use, and how to behave in specific instances by using options in the file.
We will refer to this as the sqlhosts file, although on NT systems and on Windows-based clients there is no ctual sqlhosts file. On these systems, the sqlhosts information is stored in the Windows registry, where it can be shared with other systems on the network. It is set up using the Informix program setnet32 (or setnet for the older 16-bit Informix-Net systems).
IDS 7.30 for NT has an interesting program in %INFORMIXDIR%demo. This program is called "ershed.exe," and it may be helpful if you are more comfortable dealing with the UNIX-style sqlhosts file. Ershed can read the registry from any machine you designate and presents you with a UNIX-style sqlhosts file. You can make your changes in this file and save it back to the registry of the original or different machine. This can be a valuable utility if you prefer the UNIX-style sqlhosts manipulation.
In UNIX systems, this the sqlhosts file resides in $INFORMIXDIR/etc. Here is a sample from a 7.22 version of IDS:
#server nettype hostname service #------------------------------------------------------------------------------ crm2 ontlitcp s2radC3 svc_crm2_tli ol_server ontlitcp the_server turbo ol_localhost onipcshm the_server turbo2 ol_ipnumber ontlitcp 125.95.12.02 turbo ol_bothnumber ontlitcp 125.95.12.02 1527
The first field is the name of the IDS or OnLine instance. If you are using an NT system, the IDS install program suggests an instance name by prepending "ol_" to the hostname of the system. Be sure that you differentiate between the servername and the hostname. The second field is an eight-character code called the nettype. This code identifies the server type, interface type, and network or IPC protocol in use. Codes are:
First two characters: Database server type
on = OnLine
ol = OnLine (equal to on)
se = Informix SE
dr = Informix Gateway (with DRDA)
Chars 3,4,5: Network interface
ipc = IPC (interprocess communications or streams)
soc = sockets
tli = TLI (transport level interface)
Chars 6,7: IPC mechanism or network protocol
shm = shared memory connection
tcp = TCP/IP protocol
spx = IPX/SPX protocol
str = Stream Pipe
In the sqlhosts sample file above, the first two servers, crm2 and ol_server, use the same nettype, ontlitcp. This decodes to OnLine systems communicating over TCP/IP protocol using the TLI interface. The third server, ol_localhost, has a nettype of "onipcshm," meaning an OnLine system communicating via shared memory locally using interprocess communications. This is the same physical database as ol_server, only with a different means of communication between the client and the server (both client and server are local to the machine, the_server).
Note that this is one time in which the term "OnLine" does not strictly apply to versions 5.X. When Informix changed the name from "OnLine" to "Informix Dynamic Server," it did not change the server types in nettype. To be totally consistent, it probably should create a fourth server type, "id," for IDS systems, just to alleviate confusion. Here, the "OnLine" term refers to versions 5.X, 6.X, 7.X, 8.X, and 9.X. If it's not an Informix SE engine, it will be "OnLine" in this area only.
The third field in the sqlhosts file is the hostname of the target system. This must correspond either to a hostname in the hosts file or for versions later than 7.10.UD1, to the actual IP address of the server. The final field is the service to be used for the connection. This entry must correspond to a valid service name in the services file. Both the client and the server must be using the same ports for the service, although the names do not necessarily have to be the same, although not having them the same seems needlessly complicated. For engines after 7.10.UD1, the services file may be bypassed by placing a service number rather than a service name in this field. The last two entries in our sample sqlhosts file illustrate doing this.