Enterprise Manager - Linked Databases

Description

Linked Databases provide administrators with the ability to configure access to third party databases from within the BBj SQL engine. Similar to the “Linked Server” feature in Microsoft SQL Server, with access to a Linked Database, a BBj user can execute SQL queries that incorporate data from BBj tables with data acquired from a third party database, all in a single result set.

Creating a Linked Database

Use the BBj Enterprise Manager to create a Linked Database configuration. The first step is to add the required JDBC driver JAR file to the BBjServices classpath. Consult your third party database documentation for information regarding the necessary JARs.

Add the JDBC Driver JAR

  1. Double-click on the Java Settings item in the EM navigator.

  2. Select the Classpath tab.

  3. Select the < Default > classpath item from the list.

  4. Click the add JAR button next to the list of JARs in the default classpath

  5. Select the JAR or JARs required by the JDBC driver according to the provider’s documentation. IMPORTANT NOTE: Ensure that the JDBC driver JAR selected matches the version of the JVM running. BBjServices. Some DBMS providers include multiple versions (MS SQL Server is one such DBMS).

  6. Save the changes and restart BBjServices for the changes to take effect.

Configure the Linked Database

  1. Double-click on the Databases item in the EM navigator.

  2. Click the New Database button.

  3. Provide a unique database name for the Linked Database.

  4. Select Linked Database from the Action/Type field.

  5. Provide the JDBC connection information used to connect to the third party database. Consult the third party JDBC driver’s documentation for specific details.

Using a Linked Database

Accessing a Linked Database is simple. Once configured, any SQL statement run through the BBj SQL engine (SQL verbs in BBj programs, JDBC driver and ODBC driver connections) has access to data provided by the Linked Database. Keep in mind that the connection information provided in the configuration determines the data available based on the user and database permissions of that third party connection

Example 1

The most basic usage of a Linked Database is a simple SELECT statement which includes the Linked Database name followed by the schema and ending with the table name. For example, to access data located in a PostgreSQL database configured with a Linked Database name “Postgres” the SELECT statement would look something like the following:

SELECT col1, col2, col3

FROM Postgres.myschema.mytable

Example 2

Linked Databases are not limited to single table or simple queries.  Queries may include tables from one or more BBj databases as well as any number of third party Linked Databases, all in a single query. This example joins data from a BBj ORDER_HEADER table with a CUSTOMER table from a MySQL database:

SELECT o.order_num, o.order_date, c.last_name, c.first_name

FROM   order_header o INNER JOIN TheMySQLDB.jdoe.customer c

       ON o.cust_num = c.cust_num

Common Problem Areas

Adding a third party to the mix has the potential for unique issues specific to each third party DBMS.  The most common issues encountered when incorporating a third party connection in BBj are:

  • Incorrect JDBC driver JAR file or incorrect version of the JAR for the JVM used to run BBjServices.  Ensure the driver is compatible with the JVM.

  • Some drivers utilize a native component (Oracle OCI driver for example).  When incorporating a mixed Java/native driver, ensure that the native components match the bits of the JVM.  For example, if BBjServices is running using a 64-bit JVM, 32-bit native components will fail.  Also, ensure the native components are visible and accessible by BBjServices and the user running BBjServices.

  • Standard networking issues are another common problem.  Note that all access to the third party database will come from the BBjServices process as opposed to the client connecting to BBjServices.  Ensure firewalls and network resources are accessible by the machine running BBjServices.

  • Be sure the JDBC connection string used in the Linked Database configuration is correct and functioning properly. Note that the configuration dialog has a Test button.  Make sure to test the connection configuration before attempting to use the Linked Database in an SQL statement.