Identify and connect to Informix database servers and databases
A UNIX® or Windows® computer can have several IDS instances running on it simultaneously. An IDS instance, or database server, can have several databases within it.
To see details of the instances running on a computer, run the following command:
onstat –g dis
Listing 1 provides an example output from
dis. This output shows
information about a single IDS instance.
Listing 1. Example output from
onstat –g dis
Server : mm11fc3hdr Server Number : 78 Server Type : IDS Server Status : Up Server Version: IBM Informix Dynamic Server Version 11.50.FC3 Shared Memory : 0xa000000 INFORMIXDIR : /space/ifmx/11.50.FC3 ONCONFIG : /space/ifmx/11.50.FC3/etc/onconfig.mm_HDR1 SQLHOSTS : /space/ifmx/11.50.FC3/etc/sqlhosts Host : swanners
It is possible to connect to either a remote or a local database server.
A local connection occurs when the connecting application and the database server are on the same computer. A remote connection occurs when the connecting application and the database server are on different computers; the connection from the client to the server must be made over a network.
Local connections between a client application and a database server can be made in three ways:
- Through a shared memory message system. In this case, the client application and the server attach to the same segment of shared memory. The application leaves messages for the server and picks up messages left by the server.
- Through a stream-pipe connection. This is a local inter-process communication method that uses UNIX streams.
- Through TCP/IP using the sockets or TLI programming interface. When the client application and the database server are on the same computer, IDS uses local loop back.
Remote connections between a client application and a database server can only be made through TCP/IP.
To communicate with TCP/IP, IDS supports two types of interfaces:
- TLI (Transport Layer Interface)
Check the release notes in the $INFORMIXDIR/release directory to find the interface supported by IDS on your platform. The network programming interface for Windows is WINSOC (sockets programming Windows interface).
To specify the method the application uses to connect to the database server, you must add sqlhosts information and also set some configuration parameters and environment variables.
When an application attempts a connection to a database server, some basic information is needed to make the connection. This information is held in the $INFORMIXDIR/etc/sqlhosts file in a UNIX environment. For Windows, the sqlhosts information is kept in the registry. The services file is: C:\WINNT\System32\drivers\etc\services.
The user running the application must set the
INFORMIXSERVER environment variable to a
key name that points to the entry in the sqlhosts file.
For a server connection, the key name is the same value as the
DBSERVERALIASES configuration parameter
found in the onconfig file. The key name can be any unique name within
the sqlhosts file.
When the application finds the correct entry in the sqlhosts file, it has the information necessary to connect to the database server.
If the client application and database server are on different computers, you should include the sqlhosts file on both computers. This is necessary because the database server requires the sqlhosts file for initialization processing.
Clients can optionally set the pathname of the sqlhosts file with the
INFORMIXSQLHOSTS environment variable to
allow groups to have different views of the servers available for
connection. For example, the IT staff might have an sqlhosts file with
entries for both the production and development systems.
Figure 1 illustrates an example sqlhosts file for shared memory connections:
Figure 1. An example sqlhosts file for shared memory connections
Dbservername is set to the value of the
DBSERVERNAME configuration parameter or one
of the values of the
configuration parameter found in the onconfig configuration file. The
DBSERVERNAME configuration parameter can
only have one value. The
configuration parameter can have up to 32 server names.
It also corresponds to the value of the
INFORMIXSERVER environment variable set
before starting the client application. The
INFORMIXSERVER value must be specified in
DBSERVERNAME or the
Nettype is set to onipcshm for a shared memory connection.
Hostname is set to the hostname of the local computer.
Servicename can be a unique or non-unique name within the sqlhosts file. The servicename is used as the name of a pointer into shared memory where data is exchanged. Usually, the same name is used for both the servicename and the dbservername.
The second column in the sqlhosts file is for the nettype. The nettype column contains information about the type of database server and how the connection is to be made.
Figure 2. The nettype column
The nettype consists of eight letters divided into three categories. The first two letters represent the database server product:
- on or ol is used for IDS.
- se is for Standard Engine, another IBM Informix database product.
- dr is used with for Informix Enterprise Gateway with DRDA.
The second three letters refer to the programming interface used for the connection:
- ipc is for Inter-Process Communication and is used for local connections only.
- tli is for Transport Layer Interface, a programming interface for TCP/IP communication.
- soc is for sockets, a programming interface for TCP/IP communication.
The last three letters refer to the specific protocol or IPC mechanism:
- shm is for shared memory connection.
- str is for the stream-pipe connection.
- tcp is for the TCP/IP protocol.
- spx is for the IPX/SPX protocol.
- imc is for IBM Informix MaxConnect.
Table 1 provides some example sqlhosts file entries for various communication methods:
Table 1. sqlhosts file entries for various communication methods
The sqlhosts registry contains information that enables a client application to find and connect to any Informix database server on the network. When you install the database server, the setup program creates the following key in the Windows registry: HKEY_LOCAL_MACHINE\SOFTWARE\INFORMIX\SQLHOSTS. The name of the database server is a key on the HKEY_LOCAL_MACHINE\SOFTWARE\INFORMIX\SQLHOSTS branch.
For each key (that is, each defined database server), the following sub-keys are provided:
- HOST: The name of the computer where the database server resides.
- OPTIONS: Provides additional flexibility in specifying connections.
- PROTOCOL: Identifies the protocol used for connectivity.
- SERVICE: Provides the detail required for the specified protocol to function.
Location of the sqlhosts registry:
When you install the database server, you can specify the name of the computer where you want to store the sqlhosts registry. You have two options:
- The local computer where you are installing the database server
- Another computer in the network that serves as a central, shared repository of sqlhosts information for multiple database servers in the network.
You can specify which machine holds the sqlhosts file for the database
server by using the
environment variable. If you specify a shared sqlhosts registry, you
must set the
variable on your local computer to the name of the Windows computer
that stores the registry.
Editing or adding to the sqlhosts registry:
You can use the SetNet32 application to edit existing entries or add new entries to the sqlhosts registry:
- Launch SetNet32. If you cannot find SetNet32 from your Start menu, you can launch the application directly from %INFORMIXDIR%\bin\setnet32.exe.
- Click on the HOSTS tab.
- Enter or modify the information as required.
- Click on Apply to enter the information in the sqlhosts registry.
- Click on Make Default Server to set INFORMIXDIR to this database server for your user.
Note: This modifies the HKEY_CURRENT_USER\Software\Informix\Environment registry.
You can also use the registry editor to create or change an entry in the sqlhosts registry.
A local database is one that resides within an IDS instance running on the host you are logged in to.
You can connect to a database locally using the DB-Access utility that is built in to the server.
To connect to a local database:
- Log onto the server containing the IDS instance you wish to access.
- Set environment variables as you did in the previous section.
- Enter the command
dbaccessto start the DB-Access utility. You will see the DB-Access top-level menu:
Listing 2. Start the DB-Access utility
DBACCESS: Query-language Connection Database Table Session Exit Use SQL query language. ------------------------------------------------ Press CTRL-W for Help --------
- Use the left-right arrows to select the Query-language option. You will then see the Select Database sub-menu, where you can see all the databases that reside in this particular IDS instance:
Listing 3. Select Database sub-menu
SELECT DATABASE >> Select a database with the Arrow Keys, or enter a name, then press Return. ------------------------------------------------ Press CTRL-W for Help -------- stores_demo@mm11fc3hdr1 sysadmin@mm11fc3hdr1 sysha@mm11fc3hdr1 sysmaster@mm11fc3hdr1 syspgm4gl@mm11fc3hdr1 sysuser@mm11fc3hdr1 sysutils@mm11fc3hdr1
- Use the arrow keys to select the database you wish to access. In
this case, there is only one user-created database (stores_demo).
You can see the database name (stores_demo) and the IDS instance you are connected to (mm11fc3hdr1), as defined by the
Listing 4. Database name and IDS instance
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit Enter new SQL statements using SQL editor. ----------- stores_demo@mm11fc3hdr1 ------------ Press CTRL-W for Help --------
By choosing from amongst the various menu options, you can perform activities in relation to this database including:
- Viewing a list of tables within the database (Info option)
- Dropping the database (Drop option)
- Opening the vi editor to write and execute SQL
A remote database is one that resides within an IDS instance running on a computer other than the computer you are logged in to.
Suppose you are connected to a computer named swanners and you want to query the stores_demo database on a computer called millie that has a database server named tcp_1001. The sqlhosts file on the computer swanners must have an entry for the database server tcp_1001 that looks like this:
Table 2. sqlhosts entry
Note: As an alternative to the servicename, you can enter the port number that corresponds to the dbservername in the /etc/services file.
From DB-Access, the syntax to query a database on another computer is:
Listing 5. Example syntax to query a remote database in DB-Access session
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit Run the current SQL statements. ----------------------- my_db2@mm11fc3hdr1 ----- Press CTRL-W for Help -------- select * from stores_demo@tcp_1001:state;
The SQL statement in Listing 5, above, will select from the state table in the stores_demo database on the server with dbservername tcp_1001.
You need to install drivers on the client to communicate with the IDS server. The easiest way to do this is to install IBM Informix Client SDK (CSDK) on the client. You can then test connectivity using the ILogin utility built into CSDK.
Here are example steps to install and test connectivity on a Windows XP client:
- Install CSDK on the client computer.
- Run the Setnet32 utility (installed with CSDK).
- Select the Environment tab.
You will see a screen similar to that in Figure 3:
Figure 3. Setnet32 – Environment tab
- Ensure that the
INFORMIXDIRenvironment variable is set to the location where CSDK is installed on the client computer.
If necessary, you can edit
INFORMIXDIRin this step. To do so, open the Control Panel, and select System > Advanced > Environment Variables.
- Ensure that the
INFORMIXSERVERenvironment variable is set to a database server name that is a value for the
DBSERVERALIASESconfiguration parameter in the onconfig file on the server. Ensure also that this name corresponds to a tcp connection (for example, ontlitcp) in the sqlhosts file on the server.
Table 3 includes sample sqlhosts entries on the server:
Table 3. Sample sqlhosts entries
If you have incorrectly set
to a shared memory connection (such as ids11uc2_cdr1 in Table 3
above), you will get a 25596 error: The INFORMIXSERVER value is not
listed in the sqlhosts file or the Registry.
- Add this same entry in the services file on the server and client.
On a Windows XP client the services file is:
So in this case, this file needs to contain the line:
- Enter the following fields on the Setnet 32 Server Information
- IBM Informix Server (ids11uc2_cdr1tcp, for example).
- HostName: The computer on which the server resides (swanners, for example).
- Protocolname: Any tcp connection protocol. The ontlitcp protocol is not an option in the drop-down list; however, onsoctcp works.
- Service Name: Should correspond to the entry in the services file on the client and server (ids11uc2_cdr1tcp) or the corresponding port number.
Figure 4. Setnet32 – Server Information tab
- Enter the following fields on the Host Information tab:
- Current Host: (swanners, for example)
- User Name: informix
- Password Option: Password
- Password: [password for informix]
Figure 5. Setnet32 – Host Information tab
You can now use the ILogin utility to test connectivity:
- Open ILogin, and choose File > Run. You will
see a screen similar to that in Figure 6, where you can enter the
following login parameters:
- Server: ids11uc2_cdr1tcp
- Stores Database: stores_demo
Figure 6. Login Parameters
- Click on OK. You should then see a list of customer records
found, as shown in Figure 7:
Figure 7. ILogin - Output
You have now confirmed that the client can connect to the database.
For more information on using the Setnet32 utility, refer to the manual "Configure IBM Informix Client products on Windows systems" (see Resources).
Listing 6 provides an example of a JDBC connection string required within your Java technology code to connect to an IDS database:
Listing 6. Example JDBC connection string
Connection conn = DriverManager.getConnection ( "jdbc:informix- sqli://millie:8003/test:INFORMIXSERVER=tcp_940;user=informix;password=mypassword");
- millie is the hostname you are connecting to.
- 8003 is the port.
- tcp_940 is the database server name.
- informix is the user you are connecting as.
- mypassword is the password.