From Ingres Community Wiki

Enterprise Access Frequently Asked Questions

What is a gateway and what is the relation to Enterprise Access?

Enterprise Access and Gateway reference the same underlying product. Enterprise Access was once known as Gateways. The terminology Enterprise Access and Gateway will be used interchangeably.

OK, so what is Enterprise Access?

Enterprise Access is an Ingres server that makes other databases look like an Ingres database. In other words, it is an Ingres connectivity option that allows Ingres applications to run against non-Ingres databases such as Oracle. Enterprise Access allows a single application to run against multiple databases without needing to know or use host specific syntax or data types.

Is it middle-ware?

Yes.

Is Enterprise Access OpenSource?

No, Enterprise Access is not OpenSource.

What is the current release?

Enterprise Access 2.7 for Solaris AMD, Solaris Sparc and Windows.

For other Unix/Linux platforms, Enterprise Access 2.6 (i.e. 2.6/0605).

What databases does Enterprise Access support?

At this time EA 2.6 and EA 2.7 supports:

• Oracle
• Microsoft SQL server
• IBM DB2 UDB

What Operating Systems does Enterprise Access support?

At this time EA 2.7 has servers for:

• Microsoft Windows (2003, XP, Vista (UAC off))
• Solaris AMD
• Solaris Sparc

At this time EA 2.6 has servers for:

• Microsoft Windows (2000, 2003, XP)
• Solaris Sparc
• HP-UX
• IBM AIX
• Linux

How to access Enterprise Access on a platform without an Enterprise Access server on that platform?

Enterprise Access is a regular Ingres server and can be accessed remotely just like Ingres. For example, if your environment has an old SGI machine with Ingres Net, it can be used to remotely access gateway. Microsoft SQL Server can be accessed from non-Windows environments.

Does Enterprise Access work with Open Source Ingres ?

Beginning with EA 2.7, Enterprise Access can be installed into either an Ingres 9.1.x or 9.2 instance.

For EA 2.6, Enterprise Access works with OpenSource Ingres (9.x) across Ingres Net.

At this time it is not possible to install Enterprise Access 2.6 into the same installation as the Open Source Ingres as Open Source Ingres contains a different version of GCA.

Open Source Ingres and Enterprise Access may be installed on to the same machine provided they are installed into different II_SYSTEM directories.

At this time it is not possible to install Enterprise Access 2.7 into the same installation as Ingres 10 due to features being implemented including a different version of GCA.

What Ingres versions can Enterprise Access be installed in?

Enterprise Access 2.7 can be installed into Ingres 9.1.x or 9.2. At this time it is not possible to install Enterprise Access 2.7 into the same installation as Ingres 10 due to features being implemented including a different version of GCA.

Enterprise Access 2.6 can only be installed into Ingres 2.6 SP5 or later 2.6 Service Packs installation.

How does Enterprise Access let a single application run against multiple databases?

Enterprise Access performs a number of operations but broken down simply it:

• Translates Ingres OpenSQL (that the application uses) into the native SQL. This is performed for all DDL and DML statements. For example, a SELECT without a table (which is valid SQL) is translated into a form that the native database can handle.
• Translates all OpenSQL functions into native functions calls. For example, each and every database has a different string concatenation function. In Ingres OpenSQL this is the concat() function (not +).
• Translates all data types to/from Ingres OpenSQL and the native database.
• Handles metadata inquiries the gateway system catalogs..

What does Enterprise Access look like?

Enterprise Access is a regular Ingres server, you can "see" it in iinamu, or vdba. Connections to gateways are just like connections to an Ingres DBMS server, except that a server class is used, e.g.:

sql mydatabase/oracle

The example above connects the Oracle gateway to the Oracle instance pointed to by mydatabase.

What is EDBC?

EDBC is a Mainframe based product similar to Enterprise Access that offers access to mainframe databases (both relational and non-relational databases).

What are fat gateways?

Fat gateways are gateways that offer access to databases that may not be relational and in most cases are not server based. Enterprise Access currently does not have any fat gateways as the Ingres RMS gateway is now available with the Ingres DBMS.

Can the same data/tables be accessed with Enterprise Access and regular native applications?

Yes, as with any relational database application don't forget to commit or rollback to release locks on rows/tables.

How do I access a remote Enterprise Access server?

Enterprise Access is a regular Ingres server, you can use Ingres Net (that is, a vnode).

How do I send the username and password to use to connect to the host database?

Use the -u connection flag, e.g.

sql mydatabase/mssql -ufred/secret

Will connect to Microsoft SQL Server as the user fred with the password secret.

May also use "-u" with just user name and pass password in via "-p" flag.

Do I have to create an Ingres vnode to connect to a remote gateway?

Not if you are using Ingres 2.5 or later, you can use a vnode-less or dynamic vnode.

Usage:

@machine_name,protocol,installation_id;[os_username,os_password]::database_name

Windows (client) example:

 sql @mymachine,wintcp,II[fred,secret]::iidbdb

Unix (client) example:

 sql "@mymachine,tcp_ip,II[fred,secret]::iidbdb"

NOTE: Quotes ("[" are special in Unix shell) + change of protocol

How do I send an SQL statement through the gateway withOUT it going through translation?

Use DIRECT EXECUTE IMMEDIATE, this is different from EXECUTE IMMEDIATE.

NOTE: DIRECT EXECUTE IMMEDIATE can not be used with selects or any query that returns results.

Can I create OpenSQL views with scalar functions?

Yes!

Can I use database procedures?

Yes! Enterprise Access will allow procedure calls to native database procedures.

Can I create Ingres database procedures via Enterprise Access?

No, you can only call native procedures.

Are empty strings supported?

Yes. Oracle doesn't support empty strings so they are mapped to single space " ". Current versions MS SQL Server support empty strings but the Enterprise Access currently does not take advantage of this feature. The MS SQL Server gateway maps empty strings to a single space. IBM DB2 UDB supports empty strings and they are handled as expected.

Are empty dates supported?

Yes. However, when looking at an empty date with a native tool a "zero" date is displayed. The value that can be stored in the native database for an empty date is configurable using CBF (or VCBF).

Can REPEATED queries be used?

Yes.

My Microsoft SQL Server sessions are slow through Enterprise Access. Is there anything I can do to improve performance?

The gateway expects applications to open many concurrent cursors. If your application does not need to have multiple cursors open at the same time this behavior can be changed in CBF. Set multiple_open_cursors to off. This will significantly improve performance.

If you are not sure, you can set to off and MS SQL Server will tell you if you open multiple cursors at the same time.

Can I use copydb or unloaddb?

No, but you can use COPY TABLE in binary mode.

VDBA can also be used to copy tables definitiions and data between Ingres and Enterprise Access. For example, you can copy tables from Oracle to Ingres.

What kind of clients can I use?

Any Ingres OpenSQL compliant client:

• sql / isql
• OpenROAD runtime, development and Server.
• Python Ingres driver
• JDBC
• ODBC
• .NET Data Provider
• OpenAPI
• Visual DBA

NOTE: Some of the above clients do NOT ship with Enterprise Access 2.6 or 2.7; however, as Enterprise Access is a regular Ingres server you can use the clients from an Ingres installation across Ingres Net.

Does Enterprise Access support double-byte and Unicode?

Enterprise Access 2.7 supports usage of Unicode datatypes (nchar, nvarchar, nlongvarchar) except where noted in the documentation.

Enterprise Access 2.6 only supports single byte strings.

All trademarks, trade names, service marks and logos referenced herein belong to their respective companies.