Informix Dynamic Server 11.50 Fundamentals Exam 555 certification preparation, Part 3: DBMS instances and storage objects

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 onstat –g dis. This output shows information about a single IDS instance.

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:

• Sockets
• 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 DBSERVERNAME or 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.

The sqlhosts file on UNIX/Linux

Figure 1 illustrates 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 DBSERVERALIASES configuration parameter found in the onconfig configuration file. The DBSERVERNAME configuration parameter can only have one value. The DBSERVERALIASES 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 the DBSERVERNAME or the DBSERVERALIASES configuration parameter.

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 nettype column

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.

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:

Windows – The sqlhosts registry

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 INFORMIXSQLHOSTS environment variable. If you specify a shared sqlhosts registry, you must set the INFORMIXSQLHOSTS environment 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:

1. Launch SetNet32. If you cannot find SetNet32 from your Start menu, you can launch the application directly from %INFORMIXDIR%\bin\setnet32.exe.
2. Click on the HOSTS tab.
3. Enter or modify the information as required.
4. Click on Apply to enter the information in the sqlhosts registry.
5. 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.

Back to top

Connect to a local database

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:

1. Log onto the server containing the IDS instance you wish to access.
2. Set environment variables as you did in the previous section.
3. Enter the command dbaccess to start the DB-Access utility. You will see the DB-Access top-level menu:

DBACCESS:   Query-language  Connection  Database  Table  Session  Exit
Use SQL query language.

------------------------------------------------ Press CTRL-W for Help --------
                

4. 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:

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
                

5. Use the arrow keys to select the database you wish to access. In this case, there is only one user-created database (stores_demo). Select it.

   You can see the database name (stores_demo) and the IDS instance you are connected to (mm11fc3hdr1), as defined by the INFORMIXSERVER environment variable:

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

Back to top

Connect to a remote database

A remote database is one that resides within an IDS instance running on a computer other than the computer you are logged in to.

Connecting with DB-Access

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:

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: database@server:[owner.]table.

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.

Connecting to a remote database from a Windows client

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:

1. Install CSDK on the client computer.
2. Run the Setnet32 utility (installed with CSDK).
3. Select the Environment tab.

   You will see a screen similar to that in Figure 3:

   
   
   Figure 3. Setnet32 – Environment tab
   
   
   
   
   
4. Ensure that the INFORMIXDIR environment variable is set to the location where CSDK is installed on the client computer.

   If necessary, you can edit INFORMIXDIR in this step. To do so, open the Control Panel, and select System > Advanced > Environment Variables.

5. Ensure that the INFORMIXSERVER environment variable is set to a database server name that is a value for the DBSERVERNAME or DBSERVERALIASES configuration 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:

If you have incorrectly set INFORMIXSERVER 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.

6. Add this same entry in the services file on the server and client. On a Windows XP client the services file is: C:\WINDOWS\system32\drivers\etc\services.

   So in this case, this file needs to contain the line:

   ids11uc2_cdr1tcp 32012/tcp

   
   
7. Enter the following fields on the Setnet 32 Server Information tab:
   • 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
   
   
   
   
   
8. 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:

1. 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
   
   
   
   
   
2. 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).

JDBC connection string

Listing 6 provides an example of a JDBC connection string required within your Java technology code to connect to an IDS database:

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.

2 of 7 | Previous | Next

Comments

Back to top

Table of contents

• Before you start
• Identify and connect to Informix database servers and databases
• Create and configure database storage objects
• System databases and system catalog tables
• Conclusion
• Resources
• About the author

---

2 of 7 | Previous | Next

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers