INFORMIX Enterprise Gateway Manager

loyalsockvillemobΔίκτυα και Επικοινωνίες

27 Οκτ 2013 (πριν από 3 χρόνια και 9 μήνες)

309 εμφανίσεις



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




1






INFORMIX Enterprise Gateway Manager



1.

OVERVIEW

2.

INFORMIX OPEN (ODBC Driver)

3.

INFORMIX Enterprise Gateway Manager

4.

Reference






INFORMIX Korea.


INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




2

OVER
VIEW


Software Requirements

-

INFORMXI
-
Open

-

INFORMIX
-
Enterprise Gateway Manager

-

INFORMIX OnLine Dynamic Server

-

ORACLE Server

-

ORACLE SQL*Net

-

ORACLE Shared Libraries


Informix
-
Open 2.5 is available on the following platforms:


-

Solaris 2.4/2.5 for Sun SPARC


-

HP
-
UX 10.01, 10.1x, 10.2x


-

AIX 4.1


-

Windows NT 3.51 or 4.0


-

Windows 95


-

Windows 3.1


-

Windows for Workgroups 3.11


COMPATIBILITY

On Any of the Supported Wi
ndows or UNIX Platforms

-

5.0x INFORMIX
-
OnLine for UNIX

-

7.x INFORMIX
-
OnLine Dynamic Server for UNIX

-

7.x INFORMIX
-
OnLine Workgroup Server for NT

-

8.0x INFORMIX
-
OnLine XPS for NT
---

(subject to unimplemented

-

8.0x INFORMIX
-
OnLine XPS for UNIX
---

features of XPS)

-

9.x INFORMIX
-
Universal Server for UNIX

-

5.0x INFORMIX
-
SE for UNIX

-

7.x. INFORMIX
-
SE for UNIX

-

7.16 Oracle

-

7.2x Oracle

-

7.3x Oracle

-

10.x Sybase

-

11.x Sybase



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




3








INFORMIX
-
OPEN

V 2.5



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




4

What is INFORMIX
-
Open ?

The ODBC architecture consists of four components.



Component


Function



Database application


Calls functions in the ODBC API to access a data

source.


Driver manager


Provides information to an application


such as a

list of available data sources and drivers


loads

drivers dynamically as they are needed, and

provides argument and state transaction checking.


Driver



Each driver processes

ODBC function calls and

Manages exchanges between an application and a

data source.


Data Source


Contains the data that an application needs to

access. The data source includes the data, the

database management system (DBMS) in white the

data is st
ored. The platform on which the DBMS

resides, and the network (if any) used to access the

DBMS



INFORMIX
-
Open and ODBC Architecture















ODBC

interfac
e

Appli
-
cation

Driver

Manager

Informix

Driver

Oracle

Driver

Sybase

Driver

Informix

data source

Oracle

data source

Sybase

data source

INFORMIX
-
Open lib
raries



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




5


INFORMIX
-
Open consists of two ODBC components


the driver manager and a set of three dr
ivers as shown in Figure (INFORMIX
-
Open and ODBC Architectur
e). The driver manager and
each driver appear to an application as one unit that processes ODBC function calls.


Understanding the odbc.ini

The
odbc.ini

file is an initialization file that the INFORMIX
-
Open ODBC drivers and driver ma
nager use.

For UNIX u
sers
.odbc.ini

is a text file calles
.odbc.ini
. The file is located in your home direct
ory. In addition, a template .odbc.ini file resides in the
$INFORMIXDIR/cli

directory where yo
ur INFORMIX
-
Open software is installed. UNIX users are responsible for m
odifying their
.odbc
.ini

file using a text editor. For driver
-
specific
.odbc.ini

changes and information.


odbc.ini File Format

The odbc.ini file consists of the following sections.


Section



Function



ODBC Data Sources

Lists the name of each data source and describes

its associated driver.


Data Source


Each data source listed in the ODBC Data Sources

section


Specification


has a section that contains additional information

about that data source.

Defaut Data Source

Opt ional. Specifics the default dat a source t o use Specificat ion


when none is specified at connection time.


ODBC Options


Specifies ODBC options that can be enabled or

disabled.


EXAMPLE (odbc.ini)



[ ODBC Data Sources ]




Stores7 = Informix Driver




ODBC Data Source


ORASRV = Oracle Driver






[ Default ]


Driver = /usr/informix/cli/dlls/Ixinf708.so


Default Data Source Specification


Database = Agencies




INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




6


[ Stores7 ]






Driver = /usr/informix/cli/dlls/Ixinf708.so


Description = Informix 7.x ODBC Driver



Data Source Specification


Database = stores7


LogonID = marvin


Password =



[ ORASRV ]


Driver = /usr/informix/cli/dlls/Ixora708.so


Description = Oracle 7.x ODBC Driver


ServerName = T:mickey:ORASRV


LogonID =
marvin


Password =



[ ODBC ]


Trace = 1


TraceFile = /tmp/trace.log




ODBC Options


AraceAutoStop = 0




ODBC Data Source



Data_source_name=driver_description


data_source_name

identified the data source that the driver accesses. You


define th
e
data_source_name
. The name can be any


name that you choose.


Driver_description

describes the database driver that accesses the

data source. This field is optional.



Data Source Specification

Each data source listed in the ODBC Data Sources section has its own Data Source Sp
ecfication section.



[ data_source_name ]



Driver=driver_path



TranslationDLL=traslation_path



TranslatonName=translator_name



TranslationOption=translation_option



A
ttribute=attribute_value

data_source_name


is the name of the data source. As specified in the

ODBC Data Sources section of this file.

driver_path

is the full path to the driver shared library.

translation_path

is the full path of the translation shared l
ibrary. This filed is

optional.

translator_name


is the name of the translator as listed in the ODBC



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




7

Translators section of the
.odbcinst.ini

file. This field is optional.

translation_option


shows the ASCII representation of the 32
-
bit integer


translation option. This filed is optional.

attribute


specifies optional connection information. Each driver has

its own set of attributes.

attribute_value

specifies the value for the attribute.


Default Data Source Specification

The Default Data Sourc
e Specification, which is optional, contains information about the

default data source. This data source, called Default, has the same format as any othe
r Data Source Specification section. However, the default data source is not listed in t
he ODBC Data
Sources section.


O
DBC Options

The ODBC Options section indicates whether tracing is enabled or disabled. When trac
ing is enabled, all ODBC function call made from an application can be logged to the
specfied trace file.




[ ODBC ]



Trace=0|1



TraceFi
le=tracefile_path



TraceAutoStop=0|1

Trace


indicates whether tracing is enabled. If Trace is set to 0, tracing i
d

disabled. If Trace is set to 1, Tracing is enabled.

TraceFile

specifies the full path to the trace file that logs the ODBC function

calls
. If a t race file is not specified and tracing is enabled, loggin
g information is written to the
sql.l og

file located in your current di
rectory.

TraceAutoStop

indicates whether tracing is enabled or disabled when an

application calls the
SQLFreeEnv

funct
ion. If TraceAutoStop is set

to 0, tracing is not automatically disabled. If TraceAutoStop is set

to 1, the Trace attribute

in the
odbc.ini

file is set to 0.



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




8

Oracle Driver for UNIX


Setting Up the Oracle Driver

Th
e Oracle driver is installed during the driver
-
installation procedure. The name of the current d
river is
Ixor708
. The driver might have a platform
-
specific extension; for example, the driver fo
r solaris is
Ixor708.so
.

You must perform the following setup

tasks before you can use the driver:

-

Set the
ORACLE_HOME

environment variable to reflect the appropriate directory
path.

-

Build the Required Oracle SQL*Net Driver.


System Requirements

To use Oracle driver, you must have the following Oracle software

installed on your system or
network.

-

ORACLE7 Server

-

ORACLE SQL*Net ( Version 1 or Version 2 )


Setting Environment Variables

-

Set the ORACLE_HOME environment variable to the fully qualified path of the di
rectory where your ORACLE7 server software
is installed.

-

Set these variables in the
.open.csh

(C shell) or
.open.sh

(Bourne or Korn shells)
file that is installed whit INFORMIX
-
Open. Source or execute this file to set the
environment variables.


Adding a Data Source

Before you can connect to a

data source, you must add an entry for that data source in your
.o
dbc.ini

file.


A.

Required Data
-
Source Configuration information

When you add a data source, you must provide t wo pieces of informat ion in its Data So
urce Specification section


Driver


The full path to your driver shared library.


ServerName

The SQL*Net connection string that designates the database


server and database to be accessed If the SQL*Net

connection string contains semicolons, you must enclose it

in quotation marks.



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




9

* All
other connection information is optional.



The SQL*Net Connection String


The SQL*Net connection string designates the database server and database

to be accessed.

For local database servers, the SQL*Net connection string has the following

form



Serv
erName = database_name


In this case,
database_name

identifies your Oracle database.




To use SQL*Net, Version 1.x

The connection string for SQL*Net, Version 1.x has the following format:


ServerName = driver_prefix:computer_name[ :SID ]

Driver_prefix


A string that indicates the network protocol.




The driver prefix can be as follow
:P

(nameed

pipes),
X

(SPX),
B

(NetBIOS),
T

(TCP/IP),
D


(DECNet),
A

(Oracle Async),
AT

(AppleTalk), or
TNS


(SQL*Net 2.x). Check your Oracle documentation

for other prot
ocols.

Computer_name

The name of the Oracle Listener on your network.

SID



The Oracle System Identifier.




This string identifies the instance of Oracle running

on the host and is requires when an application

connects to system that support more than o
ne

instance of an Oracle database.



For example :
ServerName = T:mickey:employees



In this example, the SQL*Net string specifies that the remote database is


called
employees

and that it resides on the host called
mickey
. The string


spe
cifies the

TCP/IP

network protocol.



To use SQL*Net, Version 2.x

The connection string for SQL*Net, Version 2.x has the following format:


ServerName = oracle_tsn

For example.


ServerName = bcc_tsn

In this example, the SQL*Net string specifies the name of the Oracle service (
bcc


INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




10

_tsn
) that points to the remote database. The database server checks the
tnsname
s.ora

file to determine the connection database.


B.

Optional Data
-
Source Configuration Infor
mation

When you add a data source, you can define additional connection defaults. The following

table lists the configuration options that the Oracle driver supports. Some of the attributes

have default values.


Description

A long description that ident
ifies the data source


LogonID


Your user ID as specified on the Oracle database server


LockTimeOut

A value that specifies whether Oracle should wait for a lock

to be freed before raising an error when processing a

SELECT…For UPDATE statement:

-

0 = D
o not wait

-

-
1 = Wait. This is the default setting

ArraySize

An integer value from 0 to 65536 that specifies the number

of bytes

the driver uses for fetching multiple rows the default

is 60000. Larger values increase throughput by reducing the

number

of times the driver fetches data across the network.

Smaller

values increase time, as there is less delay waiting

for the database server to transmit data.

PacketSize

A value that controls the packet size for TCP/IP connections



The following integers

are the available values:

-

1024

-

2048

-

4096

-

8192

All other value are ignored. Use this option only when the Server
Name

Option designates
T

(TCP/IP) as the network protocol.

Catalog


A values that determines whether the driver returns the

contents of the Comments COMMENTS column for catalog

functions:

-

0 = Do not return comments. This is the default setting.

-

1 = Return comments. This setting might affect the performa
nce of your data catalog operations.



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




11

To

add a data source


1.


Edit your
.odbc.ini

file using a test editor such as the
vi

editor.

If you do not have this file in your home directory, copy the default
.odbc.ini

file from the

$INFORMIXDIR/cli

directory into your home directory.

2.


Under the

ODBC Data Source section, add an entry for your data source.

Each entry in this section lists the data source and a description of the driver that the data
source uses. Use the following format for data_source entry.



[ ODBC Data Sources ]



data_source
_name = driver_description …

3.


After the ODBC Data Source section, add an entry for each data source that you specified.
Each data source listed in the ODBC Data Source section of your
.odbc.ini

file requires a Dat
a Source Specification. Use the foll
owing format for Data Source Specification entries:



[ data_source_name ]



driver = driver_path



serverName = sql_net_string



attribute = value ….


data_source_name


is the name of the data source nam, as specified in

the ODBC Data Sources section o
f your
.odbc.ini


file.


driver_path



is the full path to the ORACLE shared library


sql_net_string


is the SQL*Net connection string that designates

the Oracle database server and database that you

want to access. For information on the format of

the

SQL*Net connection string.


attribute



specifies additional configuration information.


value



specifies the value for the
attribute.

The required fields in this section are
data_source_name
,
driver_path

and
sql_net_string
. The

attributs that you enter in this section become the default connection values for this data_sour
ce.



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




12

ODBC Conformance Levels

ODBC defines the API and the SQL conformance standards for drivers.
API conformance

refers

to th
e functions that a driver supports.
SQL conformance

refers to the SQL statements, express
ions, and data types that the driver supports.


API Conformance Level

The Oracle driver supports the core and level
-
1.

-

The Oracle driver supports the following opt
ions for the SQLGet ConnectOption and

SQLSetConnectOption level
-
1 functions.

SQL_ACCESS_MODE


SQL_TRANSLATE_DLL

SQL_AUTOCOMMIT


SQL_TRANSLATE_OPTION

QL_OPT_TRACE



SQL_TXN_ISOLATION

SQL_OPT_TRACEFILE

-

The Oracle driver supports the following opt ions for the SQLSetSt mt Option and S
QLSetStmtOption level
-
1 functions.

SQL_BIND_TYPE



SQL_NOSCAN

SQL_CONCURRENCY


SQL_RETRIEVE_DATA

SQL_CURSOR_TYPE


SQL_ROWSET_SIZE

SQL_KEYSET_SIZE


SQL_ROW_NUMBER

SQL_MAX_LENGTH


SQL_SIMULATE_CURSOR

SQL_MAX_ROWS

In additional, the Orale driver supports the following level
-
2 functions.



SQLBrowseConnect


SQLNumParams



SQLDataSource



SQLParamOptions



SQLDrivers



SQLPrimaryKeys



SQLExtendFetch (forward)

SQLProcedureColu
mns



SQLForeignKeys



SQLProcedures



SQLMoreResults



SQLSetScrollOptions

SQLNativeSql


SQL Conformance Level

ODBC extensions supported by the ORACLE Server, the Oracle driver supports the following O
DBC extensions to SQL:

-

Date, time, and time
-
stamp data

-

Outer joins

-

The following numeric functions.



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




13

abs


floor


power


sqrt

ceiling


log


round


tan

cos


log10


sign


truncate

exp


mod

-

The following date functions.

curdate


dayofyear


monthname

week

dayname


hour


now


year

dayofmonth

minute


quarter

dayofweek

month


second

-

The following string functions.

ascii


left


repeat


space

char


length


replace


substring

concat


locate


right


ucase

insert


locate_2


rtrim

lcase


ltrim


soundex

-

The following system functi
ons.

Ifnull


user


Mapping Data Types

The Oracle driver maps ORACLE7 Server data types to their appropriate ODBC SQL data types.

The following table lists the ORACLE7 Server data type and its corresponding ODBC SQL da
ta type.



ORACLE7 Server Data Type


ODBC SQL Data Type



CHAR



SQL_CHAR



DATE



SQL_TIMESTAMP



LONG



SQL_LONGVARCHAR



LONG ROW


SQL_LONGVARBINARY



NUMBER


SQL_DOUBLE



NUMBER (p,s)


SQL_DECIMAL



RAW



SQL_VARBINARY



VARCHAR2


SQL_VARCHAR



The Oracle driver does not support the FLOA
T or the MLSLABEL ORACLE7 dat
a types.


Supported Isolation and Lock Levels

The Oracle driver supports isolation
-
level2 (repeatable read) only. The driver supports record
-
lev


INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




14

el locking.


INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




15








INFORMIX Enterprise Gateway Manager

V 7.20



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




16

What Is INFORMIX
-
Enterprise Gateway Manager ?

INFORMIX
-
Enterprise Gateway Manager servers as a bridge or
gateway

between an Informix cli
ent application and the data on a target non
-
Informix data source. The Gateway Manager conne
cts the Informix environment whit the environment of any shared
-
library ODBC Level 2.5
-
compli
ant driver manager and driver on UNIX. For in
stance, you can use the Gateway Manager with
INFORMIX
-
Open products to access the SYBASE SQL Server 10 or 11 and ORACLE7 Server
products on UNIX platforms.


The Gateway Manager translates Informix SQL requests into ODBC
-
compliant function calls. It
emulat
es 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 c
an access data on the target DBMS using Informix client applications such as INFO
RMIX
-
ESQL/
C through a coordinating OnLine database server. Using Informix SQL, a user application can a
ccess the data source in distributed queries.


The OnLine emulation that INFORMIX
-
Enterprise Gateway Manager performs can be limited by
factors such as
the ODBC architecture, the specific ODBC driver used, and the imitations of the

target DBMS


Accessing the Gateway Manager

The Informix client application connects to an OnLine database server that coordinates a distribut
ed join between one or more INFORMIX
-
Online.


SYBASE Server 10 or 11,ORACLE7 or other

11,ORACLE7 or other

ODBC accessible server

er

ODBC

Level 2.
driver

manager

and

drivers

Gateway Manage


OnLine

OnLine

client

application

Role of the Gateway Manager



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




17

When the client connects to INFORMIX
-
Enterprise Gateway Manager from a coordinating OnLin
e database server, the Gateway Manager identifies itself as a database with characteristics identica
l to the characteristics of the OnLine coordinator.


If the coordin
ating OnLine database is a non
-
ANSI database with loggings, the Gateway Manager

identifies itself as the same type of database. When OnLine is logging database transactions, th
e Gateway Manager emulates the appropriate ANSI or non
-
ANSI transaction behavio
r.


In cases where the t ransaction
-

or isolation
-
level capabilit ies of the data source and coordinating
OnLine database server do not match, the Gateway Manager imposes appropriate restrictions (suc
h as rejected connection or rea
-
only access) on the user.


Distributed Queries

INFORMIX
-
OnLine Dynamic Server versions prior to 7.2 do not support heterogeneous commits.

On database servers prior to 7.2, if an update statement (UPDATE, INSRET, DELETE) is issu
ed against a data source during a distributed transact
ion, all the other sites must be read
-
only du
ring the transaction. That is, in one transaction, you can update only a single data source. Ho
wever, if all data sources in a distributed transaction are read
-
only, then any number of OnLine
database server c
an be updated within the same transaction.


Using the Informix
-
Style System Catalog

An Informix
-
style system catalog usually improves the performance of catalog
-
information request
s that are associated with distributed queries. Catalog informat ion usually

is retrieved more quic
kly with the Informix
-
style catalog than with the ODBC catalog information functions. This is b
ecause fewer table entries are present in the Informix
-
style catalog and, unlike ODBC catalog inf
ormation functions, no costly joins of d
ata
-
source system catalog tables are performed.


The Informix
-
style system catalog has one limitation: the information in the catalog is static. Th
erefore, the Informix system catalog must be repopulated if tables are altered or dropped.


Security Feature
s

INFORMIX
-
Enterprise Gateway Manager allows you to map the UNIX user ID used to connect
to the Gateway Manager to another user ID and password that are used to connect to the target

DBMS. If the target DBMS accepts the Gateway Manager authentication of a

user ID, the Gat
eway Manager can be configured to connect to the target DBMS without a password. This feat
ure serves to eliminate double password maintenance at the Gateway Manager and at the target


INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




18

DBMS.


You can use the following mechanisms to map user

ID and password pairs between UNIX and t
he target DBMS:

-

the
egmdba

utility

-

the
.netrc

file

The user function of the
egmdba

utility maps a UNIX user ID to a user ID and password pair
for a target DBMS. The system administrator of the Gateway Manager

can invoke the
egmdba

utility to view or update the user ID mapping. If any other users invoke the
egmdba

utility, th
e users can view and update only their own mappings. In either case, passwords can only be u
pdated, never viewed.


Passwords are protect
ed by an encryption algorithm. INFORMIX
-
Enterprise Gateway Manager us
es a different key for each installation of the Gateway Manager. This key ensures that if the fil
e that contains the encrypted passwords is storen, the file cannot be used an another IN
FORIX
-
E
nterprise Gateway Manager installation.


Hardware Resource Requirements

The INFORMIX
-
Enterprise Gateway Manager has the following estimated hardware resource requi
rements:

-

Disk space required for installation: 17 megabytes

-

Fixed memory required for the Gateway Manager process : 1.2 megabytes

-

Average virtual memory required per Gateway Manager user (varies by application)

: 180 kilobytes


Administrators Who Will Provide Help

Working with a relational or nonrelational da
tabase system in an environment that might involve
multiple computers, multiple operating systems, and multiple network
-
connection types demands p
atience and cooperation among the administrators of the different system. As you install and use

the Gateway
Manager, you need to talk to rhe following people:

-

The UNIX system administrator deals with questions of resource allocation and will

assist with the initial installation of the INFORMIX
-
Enterprise Gateway Manager s
oftware.

-

The UNIX network administr
ator takes care of the /etc/hosts, /etc/services, and /etc/
hosts.equiv files that are required for TCP/IP network connections.

-

The security or database administrator for the data source where the target DBMS


INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




19

resides can provide information specific to t
hat environment.




INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




20

Detailed Configuration Procedure


1. The Communication Protocol and Interface

Before you start the configuration process for the Gateway Manager, you need information about

the UNIX network and the target DBMS. You might need the help of the network administrat
or to find information about the UNIX network. You will use this inform
ation when you prepar
e the
sqlhosts

file.

-

What communication protocol will the client application use to connect to OnLine?

What protocol connects OnLine and the Gateway Manager? The following are p
ossible:

-

A TCP/IP network connection

-

An IPX/SPX

(NetWare) network connection

-

What communicat ion interfaces are used by OnLine and the Gateway Manager? T
he following are possible:

-

Sockets

-

TLI (transport
-
level interface)


2. The hostname Value

The name by which the Gateway Manager computer is
known to your UNIX network is the
hos
tname

value.

-

For a TCP/IP network connection, the host name is the name that is associated wit
h the network address of the Gateway Manager computer in the
/etc/hosts

file.

-

For a NetWare network connection, the host name is the name of the NetWare file

server.

Both the TCP/IP host
-
name entry in the /etc/hosts file and the NetWare fileserver name are custo
marily the same as the host name of the computer, but that is not a requ
irement. You can get
the host name of the computer by entering the command “hostname” at the UNIX prompt.


3. The TCP/IP servicename Value

If the Gateway Manager is on a TCP/IP network, the
servicename

value is an entry in the
/etc/
services

file. Ask th
e net work administrator what service name to associate with the Gateway
Manager daemon


The Target DBMS User ID and Password

To access the target DBMS, a user needs a user ID and password that the target DBMS recogni
zes. The user IDs that are valid on th
e Gateway computer might not be valid target DBMS use


INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




21

r IDs.

Obtain a user ID and password that are valid on the target DBMS system from the administrator

of the target DBMS system. The user ID and password are required for the initial configuratio
n of you
r system. You need only one target DBMS user ID and password for initial configurati
on.


4.


Set up the Gateway sqlhosts File

The
sql hosts

file specifies the server name and location of the Gateway Manager and the protoc
ols used by the coordinating On
Line database server to communicate with the Gateway Manager.

Each set of Informix products that is installed within a particular
$INFORMIXDIR

directory has

only one
sql hosts

file. That one file contains connection informat ion for all the Informix prod
uct
s in that
$INFORMIXDIR
. This manual discusses only connections that involve INFORMIX
-
Enterprise Gateway Manager



Example of an sqlhosts File


dbservername


nettype


hostname

servicename



ifx



onsoctcp

valley


sqlexec


to_oracle



onsoctcp

hill


to_ora
cle


ifx2



onsoctcp

river


ifx2_soc



dbservername

The
dbservername

field contains the name of a database

server, as specified by the
DBSERVERNAME

and

DBSERVERALIASES

configuration parameters in the

ONCONFIG

configuration file. Ea
ch database server across

all of your associated networks must have a unique

dbservername.


nettype


The
nettype

field describes the type of interface/protocol

combination that should be made between the client

application and database server.


o
ntlitcp … Network connection using TLI with TCP/P


protocol


onsoctcp … Network connection using sockets with


TCP/IP protocol


ontlispx … Network connection using TLI with IPX/SPX


protocol


hostname

The third field of the
sqlh
osts

file is termed the
hostname




INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




22

field because n many configurations it contains the name of

the computer where the database server resides


servicename

When you use the TCP/IP connection protocol, the

servicesname

must correspond to a
servicename

entry

in the

/etc/servicesfile
. The port number in the
/etc/services

file

tells the network software how to find the database server on

the specified host.


5.


Define the Gateway User ID Mapping

You must configure the Gateway to use the target DBMS u
ser ID and password obtained. Use
the
egmdba

ut ility User option to add a mapping from your UNIX user ID and data source na
me to the target DBMS user ID and password.


6.


Make the ODBC Driver Manager Dynamically Loadable

Make the ODBC driver
-
manager sh
ared
-
library object dynamically loadable by the Gateway

The ODBC driver manager is a component of your ODBC product. The ODBC driver
-
manager
shared library is named
li bodbc.xxx
, where
xxx

is the suffix for shared libraries on your operati
ng system (typica
lly,
so
,
sl
, or
o
). you should be able to locate this file in one of the directori
es of your ODBC product installation.

The Gateway dynamically loads the ODBC driver manager. When the Gateway makes the call t
o the operating system to load the driver mana
ger, the operating system must be able to find the

driver
-
manager shared
-
library object file.

Usually, an operating system locates a shared library in t wo ways: by searching its default share
d
-
library directories and by searching directories specified by a
n operating
-
system environment var
iable.

However, because the Gateway is a
root setui d
program, some operating systems (such as Solari
s and AIX) will not search the shared
-
library directories specified in the path environment variab
le for the operating
-
sys
tem shared libraries.

If your operat ing system uses its shared
-
library path environment variable for
root

setui d

progra
ms, add this variable to your Gateway daemon environment file, and set it to include the directo
ry that contains your ODBC driver
-
manager

shared
-
library object.


7.


Start the Gateway Daemon

Follow the steps in this section to start the Gateway daemon.

A.


Log in as
root
.

B.


Set the Gateway
-
daemon environment variables.



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




23

C.


Start the Gateway daemon. Issue the following command:

$ egm
d
gateway_server_name

-
s egm
-
l
logfile_pathname



The
gateway_server_name

is the
dbservername

field of the Gateway


sqlhosts

entry. The
logfile_pathname

is the full pathname of the Gateway


daemon log file.

D.


Check the Gateway
-
daemon
log file to make sure that the Gateway daemon started

successfully.

8.


The Test
-
Connection

Use an Informix client applicat ion such as DB
-
Access to test the end
-
to
-
end connection. Issue t
he following SQL statement:


SELECT *


FROM
Data_Sourc
e
_Name@Gateway_Server_Name:

owner

.tablename



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




24

Advances Configuration Topics


Using Multiple Gateway Daemons

In most cases, a single Gateway Manager daemon is sufficient in a local UNIX network. You
might want to
start multiple Gateway Manager daemons for the following reason:

-

To allow an OnLine instance to connect to multiple data sources. Informix recom
mends a Gateway Manager daemon for each data source

-

To provide differently configured Gateways for differ
ent types of applications

-

To provide extra capacity when many users are connecting to the Gateway Manage
r at the same time


Improving Performance

In order to improve the performance of an application that uses the Gateway, the Gateway admin
istrator need
s to understand the effects of the following factors on performance:

-

Network speed

-

Accuracy of statistical information

-

Informix
-
style system catalog


A.

Effect of Network speed on Performance

Certain network characteristics affect network speed. These characteristics can be o
bserved and possibly improved by the Gateway administrator. The following factors

affect the performance of SQL queries that transfer data over a network from a da
ta sour
ce to the Gateway Manager.

-

The raw transfer rate of the network

-

The number of messages that are required to transport the data

-

The software and hardware components that are involved in the transfer

-

Concurrent network traffic

-

Network mode tra
nsfer time to the client application or OnLine

B.

Effect of Accuracy of statistical Information

Much of the query optimizat ion that can be applied in distributed queries depends o
n the correctness of table statistics that are available from the system cat
alog inform
ation on a server. The INFORMIX
-
OnLine Dynamic Server or INFORMIX
-
OnLine
database server, acting as the transaction coordinator, handles optimizat ion issues for

the Gateway Manager

OnLine has an efficient cost
-
based network
-
query optimizer, but

it must be given co
rrect table statistics to perform properly. For example, depending on the system
-
cat


INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




25

alog information that it receives, OnLine might use indexed columns to access data
or it might choose to do a join remotely instead of locally

The Gate
way Manager administrator must ensure that table
-
statistics information on
both the target DBMS servers and OnLine database servers is kept up
-
to
-
data for th
e tables that Gateway Manager applications use.

C.

Effect of Informix
-
style system Catalog

When th
e Informix
-
style system catalog is uninstalled and populated with the object

used in SQL queries, the Gateway uses the catalog to obtain column, procedure, a
nd statistical informat ion about the objects. Otherwise, the Gateway uses the ODBC

catalog
-
informa
tion function calls to obtain this informat ion for the OnLine coordin
ator.

Obtaining this information from the ODBC catalog
-
information function calls is usua
lly slower than obtaining it from the Informix
-
style system catalog. This is because

the ODBC fun
ction calls collect much more informat ion than in needed by OnLine

and often perform complex joins of the target DBMS system
-
catalog tables to obtai
n this information. In contrast, the Informix
-
style system
-
catalog queries obtain exac
tly the information r
equired by OnLine and perform no joins.

Using the Informix
-
style system catalog should result in higher performance. The di
sadvantage of using the Informix
-
style system catalog is that it is statistic and the o
bject information within ot needs to be refre
shed periodically. In addition, new obj
ects need to be added and the dropped objects deleted. Use the
egmdba

ut ility to
add, delete, and refresh object in the catalog.


INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




26

Summary of SQL Statement Syntex and Semantics for the Gate
way Manager



SQL Statement


Works

Work with


Does not ODBC Driver Target
DBMS

of Concept



Restrictions Work

Dependent Dependent


ALLOCATE DESCRIPTOR



ALTER FRAGMENT





ALTER INDEX






ALTER TABLE






BEGIN WORK




CHECK TABLE









CLOSE cursor




CLOSE DATABASE





COMMIT WORK




CONNECT






CREATE AUDIT









CREATE DATABASE





CREATE INDEX






CREATE PROCEDURE





CREATE PROCEDURE FROM




CREATE ROLE






CREATE SCHEMA






CREATE SYNONYM





CREATE TABLE






CREATE TRIGGER






CREATE VIEW






DATABASE






DEALLOCATE DESCRIPTOR

DECLA
RE









DELETE










DELETE WHERE CURRENT OF






DESCRIBE




DISCONNECT






DROP AUDIT











INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




27

DROP DATABASE







SQL Statement


Works

Work with


Does not ODBC Driver Target
DBMS

of Concept



Restrictions Work

Dependent Dependent


DROP INDEX






DROP PROCEDURE





DROP ROLE






DROP SYNONYM






DROP TABLE






DROP TRIGGER






DROP VIEW






EXECUTE




EXECUTE IMMEDIATE




EXECUTE PROCEDURE






FETCH





FLUSH





FREE





GET DESCRIPTOR




GET DIAGNOSTICS



GRANT







GRANT FRAGMENT





HOLD cursor







INFO










INSERT










INSERT cursor









LOAD










LOCK TABLE









OPEN










OUTPUT




PR
EPARE





PUT




RECOVER TABLE









RENAME COLUMN





RENAME DATABASE







INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




28

RENAME TABLE






REPARE TABLE











SQL Statement


Works

Work with


Does not ODBC Driver Target
DBMS

of Concept



Restrictions W
ork

Dependent Dependent


REVOKE







REVOKE FRAGMENT





ROLLBACK WORK


ROLLFORWARD DATABASE







SCROLL cursor



SELECT









SET










SET CONNECTION





SET CONSTRAINTS








SET DATASKIP









SET DEBUG FILE TO








SET DESCRIPTOR



SET EXPLAIN









SET ISOLATION









SET LOCK MODE









SET LOG










SET OPT
IMIZATION








SET PDQPRIORITY








SET ROLE









SET SESSION AUTHORIZATION







START D
ATABASE









START VIOLATIONS TABLE








STOP VIOLATIONS TABLE








UNLOAD




UNLOCK TABLE









UPDATE










UPDATE cursor







UPDATE STATISTICS








UPDATE WHERE CURRENT OF







INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




29

WHENEVER




INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




30

Data Type Mapping


Oracle to Informix Database Data Types


Oracle Data Type in


Is Represented to Informix Client Applications a
s a Column


Oracle Table Column

with Informix Database Data Type



CHAR



CHAR


DATE



DATETIME YEAR TO SECOND


FLOAT



FLOAT


LONG



TEXT


LONG RAW


BYTE


NUMBER


DECIMAL


RAW



BYTE


VARCHAR2(n<=255)

VARCHAR2


VARCHAR2(n>255)


CHAR(n)





INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




31



Informix Data Types to Oracle SQL Data Types



Value in Informix Column

Can be Represented in


With Informix Data Type

an Oracle SQL Data
-
Type Column



BYTE



LONG RAW


CHAR(n<=255)


CHAR(n)


CHAR(n>255)


VARCHAR2


CHAR(n>
2000)


LONG


DATE



DATE


DATETIME


DATE


DECIMAL


NUMBER


FLOAT



FLOAT


INTEGER


NUMBER(10,0)


INTERVAL


CHAR(n) of a length equal to the number of characters





Required to express an interval of the specified precision





Using
the ANSI
-
standard SQL INTERVAL character

representation, plus 1


NUMERIC


NUMBER


SMALLFLOAT


FLOAT


SMALLINT


NUMBER(5,0)


TEXT



LONG


VARCHAR


VARCHAR2





INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




32









Reference

ORACLE SQL*Net



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




33


( Ora
cle SQL*Net Architecture )


















Oracle RDBMS

or Application

SQL*Net V
SQL*Net Drive
For Protocol A

Oracle RDBMS

or Application

SQL*Net V2

Oracle Protocol

Adapter for A

TNS

Protoco
Protocol

Architecture Changes Between

SQL*Net V1 and V2

(Protocol A)

A)

(Protocol B)

B)

Transparent Network Substrate (TNS)

Adapter for Protocol A

Adapter for Protocol B

TNS

Listener

Dedicated

Server

Shared

Dispatcher

TNS Listener

Request

Spawn a Process

and Connect

Hando
ff to an Ex
isting Process

Connection request from any TNS community



INFORMIX_Enterprise Gateway Manager







INFORMIX Korea.




34