Accessing data from multiple databases with Ingres

Document ID : KB000055740
Last Modified Date : 14/02/2018
Show Technical Document Details


When developing and executing CleverPath Reporter reports that access data from an Ingres database, that data will generally be restricted to the same database for a given report. This Technical Document discusses the use of Ingres/Star with CleverPath Reporter and how this can be used to access multiple Ingres databases from within the same report.

What is Ingres/Star?

Ingres/Star (called Star throughout the remainder of this document) is a distributed data manager that adds to Ingres the capability of a distributed relational database management system. It lets you combine a number of separate databases to create a single view of your data, which you access as you would any single, local database. By creating a Star database in Ingres you can include tables from different databases and have one single view. You would then create a Vnode going against this Star database and from CleverPath Reporter you can create reports going against this Star database.

How to create a Star database?

Please follow the steps below to create a Star database:

  1. Open Ingres Visual DBA on the box that has Ingres database installed.

  • Create a database, say st1, and make it "Distributable".

    Create Database on local

  • Once the distributable database is created go to Tables underneath it and right click on Tables. Select "Register As Link".

  • In the "Register Table as Link" dialog select different tables from different databases and say OK.

    Register Table as Link

  • You will see that these tables get added into your Star database (st1) that are essentially links to tables on other databases.

    Ingres Visual DBA

  • Once this Star database gets created, you can then go to your ODBC Data Source Administrator and create an ODBC data source that connects to your Star database. Make sure you select your VNODE, STAR as the Type and st1 as your database. Test this connection to make sure it works.

    Ingres ODBC Administrator

  • Using this ODBC data source create a report from CleverPath Reporter builder. It will be possible to access tables from different databases within the same query as if those tables were part of the same physical database.
  • More information concerning Ingres and Ingres/Star is available from SupportConnect.