Ingres Enterprise Access Oracle Quick Start
From Ingres Community Wiki
Quick Start to Oracle Enterprise Access On Windows
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.
The following is used for this example:
- 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.
- Please ensure you read the Enterprise Access Getting Started Guide as this article is not intended to be a substitute.
- The installation code is assumed to be II
- The database name (SID) used is oracledb and Enterprise Access is installed on the same machine as the database
- The gateway alias used is oracledb
- 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:
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:
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.
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:
Issue a query:
/* Remember that to execute the query you need to terminate the SQL with "\g" */ select * from iidbconstants\g
+--------------------------------------------------------------------+ |USER_NAME |DBA_NAME |SYSTEM| +------------------------------+------------------------------+------| |INGRES |INGRES |INGRES| +--------------------------------------------------------------------+
/* 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.