Accessing external data sources with Blitz Report™

Can I access external databases with Blitz Report?

The short answer: Yes you can!

In this post we’ll discuss;

How to set up a basic remote database connection
How to access the remote data from within a Blitz Report SQL query
Performance consideration when accessing data across a database link.
Example use cases for database links and Blitz Report

Creating a remote database connection – Database links

To access data from a remote database, you will need to have a predefined connection called a database link. Creating a database link isn’t complicated, but it does require special database privileges, so you will need to consult with your DBA team for assistance.

Creating the connection can be as simple as entering the following command into a database management tool such as Toad or SQL Developer:

create database link your_remote_db connect to apps_query identified by apps_query using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=your_database_host_name.com)(PORT=1521)) (CONNECT_DATA= (SID=EBSDB)))'

Accessing data through a database link

Accessing the data in the remote database tables or functions through the database link is done within the SQL statement by adding the @ symbol after the object name and then the predefined database link name. Here’s a simple example:

select * from supplier_info@your_remote_db
 
/* 'supplier_info' is a table name on the remote database */

This “object@databaselink” notation needs to be added for each table or function referenced in the remote database.

Performance Considerations

Accessing remote databases thru database links is a valuable tool to help get the answers you need. When constructing your SQL query with database links, developers need to be more thoughtful in the planning of their query as they are more likely to run into performance issues. These issues are dependent on the content of your database, the structure of the SQL code, and of course the physical network connection.

Let’s consider two scenarios where accessing a database link may affect performance;

SQL only querying a remote database
SQL uses a mix (join) between the local Oracle EBS tables and a remote database

SQL query from remote tables or functions only

If a Blitz Report SQL query only accesses tables or functions across the database link, all of the processing will take place at the remote database.

Blitz Report passes the report SQL to the EBS database for execution. The EBS database optimizer understands that only remote objects are involved and sends the entire query to the remote database for execution. Once complete, the result is passed back to the EBS database and onto Blitz Report for processing and delivery to the end-user.

So based on our initial example;

select * from supplier_info@your_remote_db

All of the query execution will be performed at the remote database.

SQL query uses a mix (join) between the local Oracle EBS tables and a remote database

Let’s use the example above and assume that “your_remote_db” contains important, proprietary information about suppliers that must be included with their information from the Oracle EBS database;

select
s.supplier_no,
s.supplier_name,
si.extra_value
from
suppliers s,
supplier_info@your_remote_db si
where
s.supplier_no = si.supplier_no
order by
si.extra_value

If the number of records in the EBS database is small, this SQL statement may force the execution planner to make several round trips to the remote database to join the records. In our example, the “Order By” clause is referencing a column in the remote table for sorting, so the execution plan may involve returning all of the records to the remote database to complete the sort, resulting in a performance penalty.

Controlling the execution plan

One strategy that can help ensure the best performance is to help the execution planner decide where the work should be performed. This is accomplished by providing a “DRIVING_SITE” hint within your SQL statement.

In our example, if the data requested from the remote site is a large table, we could use the following notation in our Blitz Report SQL to optimize performance by having the execution performed at the remote database with the completed result returned to Blitz Report;

select /*+ driving_site(si)*/
s.supplier_no,
s.supplier_name,
si.extra_value
from
suppliers s,
supplier_info@your_remote_db si
where
s.supplier_no = si.supplier_no
order by
si.extra_value

Database Links and Performance – Consult your DBA team

We hope we have provided enough information to help you get started using remote database links in Blitz Report.

Every organization’s Oracle EBS database, external databases, database link connection strings, and their related authentication & authorization parameters are unique and well beyond the scope of this blog post. Please engage and consult with your DBA team. They are in the best position to help you set up your external database link(s) and ensure your users are getting the very best performance from your Blitz Reports.

If your team is experiencing some difficulties with your remote database connection strategies, we’d suggest booking a Performance Tuning consulting session with one of our experts. We’d be happy to help.

Example use cases for database links and Blitz Report

The following are a few use cases where Blitz Report clients have found database links helpful;

Oracle E-Business Suite is often used in heterogeneous environments – with systems running on other databases such as SQL Server, PostgreSQL, MySQL, Db2, to name just a few – and some of these external systems have very poor reporting capabilities. In this scenario, Blitz Report can serve as a central repository for all reporting, allowing clients to take full advantage of Blitz Report’s integrated version control and change management features.

Running Blitz Report on a production system’s application tier, but connecting to a cloned or standby database. Blitz Report is based on a fairly lightweight architecture, which allows the offloading of complex query processing from the production database, helping overall performance.

Reconciling interface data between the E-Business Suite and connected remote systems.
Reconciling source and destination systems during data migration projects. Ask about our “Project Implementation Toolkit”.