From Ingres Community Wiki

Contents 1 Quick Start to Oracle Enterprise Access On Windows 1.1 Abstract 1.2 Caveats/Assumptions 1.3 Definitions 1.4 Quick Start Steps 1.4.1 Install the Ingres Enterprise Access software 1.4.2 Create a Oracle Database 1.4.3 Create an "ingres" database user in Oracle 1.4.3.1 Command line option 1.4.3.2 Enterprise Manager option 1.4.4 Add the required Oracle Database permissions to the "ingres" user 1.4.5 Define the Gateway Alias using the "Gateway Alias Utility" 1.4.6 Create Required Enterprise Access catalogs 1.4.6.1 Command line mode 1.4.6.2 GUI mode 1.4.7 Verifying the Gateway Connection Setup 1.4.7.1 Verifying using the sql command (Terminal Monitor) 1.4.7.2 Verifying using VDBA

Quick Start to Oracle Enterprise Access On Windows

Abstract

A quick start step by step guide to set up a working Enterprise Access installation on Windows, providing connectivity from your Ingres® applications such as Ingres® OpenROAD®, JDBC, ODBC, Python, .Net, embedded SQL, and OpenAPI to your Oracle Database, incorporating the set up of Visual DBA to manage the installation.

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

Caveats/Assumptions

The following is used for this example:

1. The example used here is an installation of Oracle 10.0.2.0.1.0 Enterprise Edition Release and Ingres Enterprise Access EA 2.7/0810 (int.w32/103) in a Windows XP environment.
2. Please ensure you read the Enterprise Access Getting Started Guide as this article is not intended to be a substitute.
3. The installation code is assumed to be II
4. The database name (SID) used is oracledb and Enterprise Access is installed on the same machine as the database
5. The gateway alias used is oracledb

Definitions

• Oracle Database – Commonly referred to as Oracle RDBMS which is a relational database management system (RDBMS) from Oracle Corporation.
• Gateway Alias Name - Used by the gateway to connect to the target DBMS. It specifies the connection information necessary to access a target DBMS.

Quick Start Steps

Install the Ingres Enterprise Access software

Follow the instructions from the Enterprise Access Setup Wizard

Choose the Express mode to use default instance name, character set, and time zone

The Enterprise Access installation will be running after the installation completes. This article assumes you have left the installation up and running. If not, please start prior to configuring Enterprise Access.

Create a Oracle Database

Creating a new Oracle database is optional as creating a database in Oracle not as simple compared with other databases systems (like Ingres, Microsoft SQL Server, etc.) where creating multiple databases is common.

To create a Oracle Database, follow these steps:

1. Start->programs->Oracle Home->Configuration and Migration Tools->Database Configuration Assistant

2. Go through the 12 steps and create the new database “oracledb” if the starter database created during Oracle installation is not available for use

Note: Leave the “Configure the Database with Enterprise Manager” checked in step 4

Create an "ingres" database user in Oracle

To create an "ingres" user in Oracle, follow these steps:

Command line option

Use Oracle’s command line utility, sqlplus, to create the “ingres” user, assuming that you have sufficient privileges to do so:

  sqlplus ingres/<password>

Issue the following in sqlplus:

  SQL> Create user ingres identified by <password>;   

Enterprise Manager option

Start->Programs->Oracle Home->Database Control - oracledb

1. Enter User Name and Password

2. Click on Administration tab

3. Click “Users” under “Users and Privileges”

4. Click the “create” button

5. Enter “ingres” in Name

6. Enter Password, Confirm password

7. Click on the “Roles” tab on top

8. Click the “Edit List” button

9. Move the “DBA” role on the left to the “selected Roles”

10. Click OK

11. Click OK

12. User “Ingres” is now created with DBA role

Add the required Oracle Database permissions to the "ingres" user

If the Enterprise Manager option is used to create the "ingres" user with DBA role, continue to Define the Gateway Alias using the "Gateway Alias Utility".

Issue the following in sqlplus to grant the DBA privilege:

SQL> Grant DBA to ingres;

Define the Gateway Alias using the "Gateway Alias Utility"

To define the Gateway Alias, follow these steps:

1. Start\Programs\Ingres\Enterprise Access II\Gateway Alias Utility

2. Select "Oracle" as the Server Class

3. Enter "oracledb" in Alias Name

4. Enter "oracledb" in DB Name.

5. Enter User ID and Password

6. Click Add/Update button to create the gateway alias to access the "oracledb" database

The following entry, with your actual hostname substituted for %II_HOSTNAME%, will be inserted into the %II_SYSTEM%\ingres\files\config.dat file:

ii.%II_HOSTNAME%.gateway.oracle.gwalias.oracledb:oracledb,ingres,<password>

NOTE: Specifying the password in the gateway alias is insecure as local users with read permissions can now see the ingres password in Oracle. For more secure options refer to the Enterprise Access Administrator Guide.

Create Required Enterprise Access catalogs

The target database must have the Enterprise Access metadata defined. Examples are provided for using both Command Line and GUI utilities.

Command line mode

For the purposes of this example, an example utilizing the default command line variables is shown. For additional configuration options, refer to Enterprise Access Administrator Guide, Chapter Gateway Catalog Setup. To define the gateway catalogs using the command line catalog utility (iigwcatora), follow these steps:

1. In a new Command Prompt window type:

iigwcatora oracledb -o ingres -m decimal -n opensql

If the gateway alias does not contain the Oracle DBMS password for the Ingres user, this will be prompted for.

GUI mode

Optionally, the GUI based catalog utility, iigwcat, can also be used. When using the iigwcat utility, default options are selected. If you wish to modify any of the available options, use the command line utility iigwcatora.

To define the gateway catalogs using the iigwcat utility, follow these steps:

1. Start->Program->Ingres->Enterprise Access II->Gateway Catalog Utility

2. Click "Execute action" button to create the catalogs

Verifying the Gateway Connection Setup

The gateway connection can be verified for proper setup using either via the sql command (Terminal Monitor) or using VDBA.

Verifying using the sql command (Terminal Monitor)

Start the sql Terminal Monitor:

sql oracledb/oracle

Issue a query:

/* Remember that to execute the query you need to terminate the SQL with "\g" */
select * from iidbconstants\g

Sample result:

+--------------------------------------------------------------------+
|USER_NAME                     |DBA_NAME                      |SYSTEM|
+------------------------------+------------------------------+------|
|INGRES                        |INGRES                        |INGRES|
+--------------------------------------------------------------------+

Quit:

/* Quit out of Terminal Monitor: */
\q

If your "select * from iidbconstants" query is successful, it means that your setup is complete and you are good to go.

Verifying using VDBA

The following steps assume you will be using VDBA on a machine where the gateway was installed. To connect using VDBA, follow these steps:

1.Start\Programs\Enterprise Access II\IngresVisual DBA

2. Click on the "+" signs next to "Nodes" in left pane

3. Expand "(local)" and then "Servers" icons to see the servers running on the installation. These may include DB2UDB, INGRES, MSSQL, and ORACLE.

4. Highlight "ORACLE" server

5. Click the "Node" menu on top and select "Connect"

This will open the right pane of the window with a databases icon which can be explored

If you can successfully browse your Oracle Database(s), it means that the setup is complete and you are good to go.