Hi all, I am back once again with latest update detailing integration of ODI with Ingres database. I am using ODI 12.2.1 JDK 1.8+ Ingres 10.2 pre-requisites: please download Ingres DB download Ingres workbench which is a GUI for Ingres. Download Ingres JDBC drivers By default ODI wil prompt for ODBC drivers however we have option to use JDBC drivers to communicate with ingres DB. @NOTE: if we are using ODBC connection we must use JDK less than 1.8 version or else we need to use JDBC-ODBC bridge which we must buy the license hence better to use JDBC connection only, however I am gonna detail creation of ODBC connection for Ingress Db.
Data Integrator installation folder (ODI_Home oracledi drivers) if they are not already. Dit/odi/driver.txt Last modified: 2017/02/12 21:06 by gerardnico. OBIEE 12C SNO Chapter Name 01 OBIEE Practitioner Level 01 Physical Layer 02 BMM Layer 03. OBIEE ODI INFORMATICA OBIA INSTALLATION OBIEE 12C Installation STEP 1: Microsoft Loop Back Adatptor STEP 2: Oracle Database 11G STEP 3: JDK STEP 4: Web Logic.
![12c 12c](http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_11g/odi_em_integration/images/t000125.gif)
Once Db software is downloaded please extract it and click on setup.exe which will take you through remaining installation steps. open Ingres Database Workbench, right click on database folder and select NEW option. Select Ingres option and at the bottom create a name for it and click on NEXT. Provide the name of the database, in my case i am using demodb which comes along with DB installation, providing the username/password is optional. @NOTE: creating these connections in the “Ingres Database Workbench” is just to test connections along with the JDBC URL otherwise this is not required, directly we can make connections in the ODI.
Providing the username/password is optional, either may provide or may not provide, connection test will be successful. Once we click on NEXT will detail us all the parameters provided, click on TEST CONNECTION will show us the result.
Once the test is success, click on Finish option will show us different schema’s available in the DB along with the default schema i.e. @NOTE: here vamrutha is a user i have used t install DB hence with the same name a schema has been created. All the default tables i.e.
Test DB tables will be created under vamrutha user. Here i am gonna detail how to create ODBC connection for Ingres Database. go to menu bar and select ODBC administrator option. Select SYSTEM DSN option, (do not select user DSN option) and click on ADD. Select Ingres II option and click on Finish which will prompt to enter the Ingres connection details as shown in the below diagram. @NOTE: I have provided demodb as the database which we gotby default with the database installation.
@NOTE: Since I have selected “Prompt user ID and password” option when I click on test is asking for username and password, or else will directly show the test connection result. Once we are done with creating ODBC connection, while creating dataserver in ODI either we can JDBC parameters or else can use ODBC parameters. First of all I will discuss how to use ODBC connection to connect with Ingres Db.
Right click on the Ingres technology in topology and select new technology. on the definition tab enter the DB name, provide the hostname where the Ingres is installed i.e.
ODBC connection is created or else will check in the local machine. by default will consider the ODBC-JDBC bridge driver connection. @NOTE: if we are using ODBC connection we need to make sure that the ODBC driver we are using is compatible with JDK used for Studio / Agent or else need to use third party ODBC-JDBC bridge. specify the user name and password used to connect to Db.test connection, it should be successful. If not success as I mentioned earlier will trigger to driver issue (could be JDK compatible issue or else drivers or not located) Here I am gonna discuss using JDBC drivers for Ingres connection. As I earlier mentioned, by default ODI will prompt for ODBC connection but i am going to show how to use JDBC driver instead of ODBC.
right click on the Ingres technology on topology and select new data server. on the definition tab provide the user name and password used to connect to Ingres Db. go to JDBC tab and do not select driver name instead use the following syntax.Driver: com.ingres.jdbc.IngresDriver URL: jdbc:ingres://:II7/.
![12c 12c](http://i.ytimg.com/vi/ynUURa5dA6Q/mqdefault.jpg)
please have a look below screenshot describing the JDBC usage. Test connection, it will fail because so far we have not provided ingres JDBC driver in the following locations. For agent place the driver in the following location. C: oracle ODI ODI1213 odi agent lib for studio place the driver in the following location. C: Users vamrutha AppData Roaming odi oracledi userlib now test connection, will success for sure.
Incase of error please check the JDK vs driver version compatible. Thanks Venkatesh.
Open Tools, third party JDBC drivers, libraries, property and bundle files, etc that are required by Oracle Data Integrator (ODI) Studio should be deployed into the userlib folder. The location of userlib directory is as follows: On Unix/Linux operating systems: $HOME/.odi/oracledi/userlib On Windows operating systems:%APPDATA% odi oracledi userlib where%APPDATA% is the Windows Application Data directory for the user account: C: Users AppData Roaming (in Windows 7, and Windows 8) The “userlib” directory contains an additionalpath.txt file, where you can declare additional files or folders outside of the /userlib directory from which the ODI Studio acquires its libraries and drivers. The following is an example of file contents for Windows:; Additional paths file; You can add here paths to additional libraries; Examples:; C: java libs myjar.jar; C: java libs myzip.zip; C: java libs.jar will add all jars contained in the C: java libs directory; C: java libs.jar will add all jars contained in the C: java libs directory and subdirectories Note:- ODI Studio must be restarted before the changes in the “additionalpath.txt” file will be loaded and usable.