Introduction

The Informix Enterprise Gateway Manager (EGM) serves as a bridge between an Informix client application and data on a target non-Informix data source. The Gateway Manager connects the Informix environment with the environment of any shared-library ODBC level-compliant driver manager and driver on UNIX. For instance, you can use the Gateway Manager with INFORMIX-Open products or the Standard Engine to access the Sybase SQL Server or Oracle server products on UNIX platforms.

These data sources appear to Informix clients as if they were actually Informix online instances. The communication and data conversion is transparent to the client, and the client needs to know only the Gateway Manager service and data source names in order to use this data (when authorized to do so). When the Enterprise Gateway Manager has been set up and client authorization mappings have been created, direct or distributed queries are handled in the same way they are with one or more Informix data sources.

There are two types of gateways that Informix Enterprise Gateway Manager provides:

• Informix Enterprise Gateway with ODBC
• Informix Enterprise Gateway with DRDA

Informix Enterprise Gateway with ODBC

Informix Enterprise Gateway with ODBC supports access to non-Informix data sources through ODBC drivers such as DB2, SolidDB, Oracle, Sybase, Microsoft SQL Server, and more.

Informix Enterprise Gateway with DRDA

Informix Enterprise Gateway with DRDA supports access to DRDA-compliant IBM data sources such as DB2 for z/OS®, DB2 for i®, and DB2 Server for VSE and VM .

Back to top

Gateway architecture

The Gateway Manager translates Informix SQL requests into ODBC-compliant function calls. It emulates an Informix online dynamic server and makes the underlying ODBC target DBMS appear to both client and server products like a database on that online database server. You can access data on the target DBMS using Informix client applications such as Informix ESQL/C through a coordinating online database server. Using Informix SQL, a user application can access the data source in distributed queries.

Informix Enterprise Gateway Manager provides SQL92 entry-level support. Tools and applications can use SQL to read from and write to Oracle, Sybase, and other data sources. The EGM supports ANSI-standard SQL statements.

Back to top

Configuring the Gateway Manager with ODBC

Figure 2, below, illustrates a typical Gateway Manager configuration:

Before you begin configuration, make sure the following conditions are met:

• An installed target DBMS (like IDS, DB2, SolidDB, Oracle, Sybase, or SQL Server) should be available on any of the target machines.
• An Informix database server is installed and running. It does not need to be on the same machine where the Gateway Manager is installed.
• Informix Gateway Manager is installed.

After installing the gateway, start the daemon using the following command:

 % Userid root egmd <daemon name> –s egm –l <daemon name>.log

For example:

egmd xyz –s egm –l xyz.log

The sample odbc.ini file comes with the Gateway Manager. It should be modified to contain the information needed for the target DBMS.

The egmdba or gwdba in the case of DRDA provides the GUI to add the target DBMS name and userid/password, and the connectivity can also be tested using this.

The Main Menu, shown in Figure 3, allows the user "informix" to choose one of the following tasks:

1. Enter User ID mapping entries (Figure 4)
2. Install and maintain the Informix catalog
3. Test the connection to the application server (Figure 5)

The User tab in Figure 3 allows you to add the user of the data source:

The Test-connect tab from the Main menu (Figure 3) lets you to test the connection:

If the connection is successful, the following message is displayed:

Connected to Data Source. Choose Select-count-test for further test.

This message indicates that the gateway has connected to the data source. If you want to count the rows for the table at the data source, you can choose the Select-count-test option, illustrated in Figure 6 below:

Prepare the sqlhosts file to connect to target DBMS from the Informix Server:

% cat $INFORMIXSQLHOSTS 

Output:

ids_xyz ontlitcp 9.181.159.33 1232
egm_xyz ontlitcp 9.181.159.33 3424

<ids_server> <protocol like ontlitcp> <ip or hostname> <port number/servicename>

<gateway daemon> <protocol> <ip or hostname> <port/servicename>

When the front-end/IDS are run, the gateway daemon (specified in the sqlhosts) is listed, through which the target DBMSs that are connected using this gateway can be accessed.

Back to top

Informix Enterprise Gateway with DRDA

The Distributed Relational Database Architecture (DRDA) is defined by IBM as a set of protocols that software manufacturers can follow to develop connectivity solutions between a heterogenous relational database management environment. DRDA defines what you exchange and how you must exchange it to coordinate communication between an application requester (AR) and an application server (AS). DRDA provides structure and guidelines so that a properly constructed external application can interact with any database that follows the DRDA protocols.

Informix Enterprise Gateway with DRDA acts as a bridge, or gateway, between an Informix client application and a DRDA-compliant database server. The gateway allows you to access information from a DRDA-compliant database with an Informix client application. Informix Enterprise Gateway with DRDA supports access to DRDA-compliant IBM data sources such as:

• IBM DB2 for MVS and OS/390
• IBM DB2 for OS/400
• IBM DB2 for VM (SQL/DS)

The gateway performs the following functions:

• Manages connection to the application server
• Uses formats and protocols that are acceptable to the application server to repackage SQL requests from Informix client applications
• Receives responses from the application server and repackages them in formats that are acceptable to the Informix client applications
• Handles implicit character code-set conversion when it encounters dissimilar code sets

Informix client applications use CONNECT or DATABASE statements to connect to an application server. The gateway name and an alias for the database name are used to specify the connection to the application server. The alias name (alias_RDB_name) refers to a unique configuration pair made up of the name of the DRDA-compliant database (the real_RDB_name) and a network communications mode. Each Informix client application that connects to an application server is served by a separate gateway process. For network connections, the gateway administrator starts a daemon that spawns a separate process for each connection. For local connections, the process is initiated by the connection request from the client application.

With respect to Data Type Compatibility, the gateway maps Informix data types to DRDA data types as well as DRDA data types to Informix data types so that the application handles database data types as it normally would and the data on the application server side is handled correctly.

In the latest IDS versions, DRDA is supported with in the IDS server and can be used to connect to DB2 directly.

Back to top

Configuring the Gateway Manager with DRDA

The following steps help you to configure the gateway with an application server:

1. Gather information, such as the communication protocol, application server, locale, and so on
2. Prepare the Sqlhosts file
3. Start the gateway daemon
4. Use the gwdba utility

Gather information

The following information has to be known before you can start to configure the Informix Enterprise Gateway with DRDA:

• The communication protocol used to connect to the gateway - the connection can be a local connection with unnamed pipes, a TCP/IP network connection, or an IPX/SPX network connection.
• The communication interface - three interfaces are possible — interprocess communication, sockets, and transport-level interface (TLI).
• Hostname - the name by which the gateway computer is known to the network.
• Information about the application server - the real_RDB_name (the database server name), the globally unique name for an application server, the RDB_user_id for the gateway administrator, and the RDB_user_id for users of client applications.
• Collection_id - on a DB2 application server, a collection is a logical grouping of bound packages. On an OS/400 application server, a collection specifies a group of tables and other database objects. The Collection_id is the name of a specific collection.

The system administrator of the application server must choose or create a collection to contain the packages that the gateway uses and give you its collection_id.

Prepare sqlhosts file

The sqlhosts file contains information about local and network connections. A client application uses the information to connect to a database server. Database servers use the information to connect to other database servers in distributed transactions. For Informix Enterprise Gateway with DRDA, the sqlhosts file also includes information the gateway requires in order to establish a connection to the application server. Each entry in the sqlhosts file is one line composed of four fields: the dbservername field, the nettype field, the hostname field, and the servicename field.

Example for a local connection:

Db_vish onipcpip vish gw

Example for network connection based on nettype:

db_vish ontlitcp vish servicename

db_vish onsoctcp vish servicename

Start the gateway daemon

If any client applications use a network connection to the gateway, you must start the gateway daemon gwd. The gwd daemon enables the gateway to receive a network connection request from a client application. It spawns a gateway process, attaches the client to the new gateway process, and detaches itself. The following command starts the daemon:

 % gwd gwservername –s gw

Example:

gwd xyz –s gw –l xyz.log

Use the gwdba utility

You use the gwdba utility to perform the following administrative tasks:

• Bind and drop packages at the application server
• Specify RDB_User_IDs and alias_RDB_name
• Install Informix catalogs
• Test the connection to the application server.

The gwdba utility

To execute the gwdba utility:

1. Log in as user informix.
2. Set INFORMIXDIR environment variable to point to the directory where the gateway product is installed.
3. Set PATH environment variable to include $INFORMIXDIR/bin.
4. Set INFORMIXSERVER to the gateway server name.
5. Set DBPATH environment variable to include $INFORMIXDIR/forms.

After you source your environment configuration file, enter the following command at the system prompt to execute the gwdba utility:

% gwdba

The gwdba utility creates the $INFORMIXDIR/gw/sysinfo directory the first time gwdba is executed. The user option creates gwuser files. The bind-package option creates gwbind files. The bind-package option creates gwaplpkg files if apl-to-pkg option is selected.

The main menu allows the user informix to choose one of the following tasks:

1. Enter User ID mapping entries
2. Manage package binds
3. Install and maintain the informix catalog
4. Install the schema
5. Test the connection to the application server

Add the user of the Data source

The User tab (see Figure 8) allows you to add the user of the data source. You can add an association between any UNIX user id, real_RDB_name pair, and any RDB_user_ID and RDB_password pair to gateway lookup table:

Bind the database server with the gateway

The Bind-Package tab (see Figure 9) allows you to bind the database server with the gateway. You can set up the packages at each application server as part of the gateway configuration process. (A package is the control structure that is produced when SQL statements in an application are bound to a relational database in a DRDA-compliant server environment.) Package binding is the process of preparing the package.

Test the connection between the gateway and the target DBMS

You can use the Test-Connect menus and screens to verify that the connection to the application server is working. This feature eliminates the need to test the connection with a client SQL API. Choose the Test-Connect option from the gwdba main menu. The Test-Connect option calls up the Connection screen (see Figure 10), which prompts you to supply the alias_RDB_name and the gateway server name to use for the connection. The gateway then connects to the application server.

Once you're connected to the application server, the Test-Connect menu appears. It has two options — Select-count-test and Exit. To test the connection, we have to choose the Select-count-test option. The Select-count-test screen appears. This screen prompts for owner and name of an existing table or view and issues a SELECT COUNT(*) FROM owner.objectname statement.

Back to top

Using the Enterprise Gateway Manager

Easy access to target database management system

Informix Enterprise Gateway Manager translates Informix SQL requests into ODBC-compliant function calls. Using emulation, it makes the underlying target database management system (DBMS) appear to both client and server applications as an instance of IBM Informix Dynamic Server. Users can access data on the target DBMS directly from Informix client applications written with products such as Informix ESQL/C and Informix 4GL or by using an Informix Dynamic Server-based engine to coordinate a distributed join.

Distributed access mode

Distributed access means that the Informix client application connects to Informix Dynamic Server, Version 7.x or later. This server coordinates a distributed join between one or more Informix servers, or other data sources using Informix Enterprise Gateway Manager or Informix Enterprise Gateway with DRDA.

When the client connects to Informix Enterprise Gateway Manager in distributed-access mode from a coordinating Informix Dynamic Server, Informix Enterprise Gateway Manager identifies itself as a database with characteristics identical to those of the coordinating database server.

Distributed join capability

Using Informix Dynamic Server, you can perform distributed joins with data from sources such as Informix, DB2, Oracle, Sybase, or SQL Server in a single SQL statement. In fact, you can transparently join data between Informix Dynamic Server and any data source accessible through Informix Enterprise Gateway Manager or Informix Enterprise Gateway with DRDA. With this capability, data from various locations within your company can be integrated with data in an Informix database.

Direct access to heterogeneous data

Direct access means that any Informix client application such as ESQL/C, 4GL, ISQL, or DB-Access connects directly to the Informix Enterprise Gateway Manager and thereby accesses heterogeneous data. When the client connects to Informix Enterprise Gateway Manager in direct-access mode, the gateway identifies itself as a database server with each data source from the gateway odbc.ini file appearing as a database on the server.

Unrestricted connection support

The ODBC interface allows Informix Enterprise Gateway Manager to connect to a wide range of data sources that might have varying levels of support for transactions or different isolation levels. Combined with the appropriate ODBC driver, unrestricted connection support can provide access to no relational data sources such as file systems that do not provide transaction- or isolation-level support.

Back to top

Summary

This article has explained the need for Informix Enterprise Gateway Manager in the database world where it is required to connect to other heterogeneous databases. It has also briefed you on how to configure the Gateway Manager to make use of its features. It has given an overview of some utilities and has also listed the advantages of the Informix Enterprise Gateway.

Resources

Learn

• Informix Enterprise Gateway Manager: Get more information about Informix Enterprise Gateway Manager.
  
  
• Informix Unleashed (Sams Publishing, June 1997) by John McNally, Glenn Miller, Jim Prajesh, and Jose Fortuny: Using a hands-on approach, this guide serves as a high-level tutorial if you are either new to Informix or know the product but need additional tips, tricks, and workarounds.
  
  
• IBM Informix Dynamic Server 11 Information Center: Learn more about Informix. Find information that you need to use the IDS family of products and features.
  
  
• developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Technology bookstore: Browse for books on these and other technical topics.
  
  

Get products and technologies

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

Deepa Nadagi is a system software engineer working in IDS Development team. She is involved in IDS development activities (CISAM, Enterprise Gateway Manager, Informix-4GL). Deepa holds a Bachelor's degree in Computer Science from PDIT college Hospet, affiliated to Visveshwaraiah Technological University, Karnataka.

Vishwas T Mahadevaiah has worked in the IBM IDS Team as a software engineer for one year. Vishwas was also involved in implementing UTF8 support for Informix 4GL and SOA support to Informix 4GL. Vishwas holds a Bachelor's degree in Computer Science from RV College of Engineering, Bangalore, affiliated to Visveshwaraiah Technological University, Karnataka.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Gateway architecture
• Configuring the Gateway Manager with ODBC
• Informix Enterprise Gateway with DRDA
• Configuring the Gateway Manager with DRDA
• Using the Enterprise Gateway Manager
• Summary
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers