How to use the Cross Connection features in heterogeneous database environments in Toad Data Point

In the real world I found that most customers have two or more different database vendors in house and sometimes there is a business need to join both database environments. I found some features in Toad Data Point Professional Edition, which cover this aspect and I want to share with you. These features are:

• Database Diagram
• Cross Query
• Cross Editor
• Toad Views

In my examples I will join tables from Oracle version 12 c and DB2 LUW version 10.5, but this will work for other database vendors and also for example for data from Excel or flat files.

Before I can work with both database I have to connect via the Connection Manager. Here are my Connection Properties:

Now I will start with the Database Diagram feature. Via right-click I bring the DB2 LUW table Employee into the Database Diagram:

Toad Data Point find automatically the relationships for this table and show it to me in a nice way:

Now I change connection in the Connection Manager to Oracle and via drag and drop I bring the Oracle tables Emp and Dept from my Quest_OPTI schema into the Database Diagram. At least I define manually a relationship between the DB2 LUW Employee table and the Oracle Emp table:

For example this diagram can be also print or send via email:

Now I want to create a query, which join my Oracle and DB2 LUW tables and give me the needed data back. The Query Builder from Toad Data Point will help me here. At first I bring via double-click or drag-and-drop the DB2 LUW tables Employee and Department into the Query Builder. It automatically shows the entity relationship between both tables, which is defined in the DB2 LUW database. Then I put the Oracle tables Emp and Dept into the Query Builder, which now change into Cross-Connection Query Mode.

With drag-and-drop on the columns I define the application relationship, which is not defined in the databases, between the DB2 LUW and Oracle tables:

Then I click and mark the columns from all tables I want in my query. At the bottom all selected columns show up with column and table name at the Diagram tab:

At the Query tab I can control the SQL, which is generated for me:

The Results tab shows me the data, when I excecute this SQL:

Back to the Query tab in the Query Builder, where I can see the generated SQL. Via right-click on the SQL I can open the SQL in the Cross Editor:

I can run this SQL with heterogeneous connection in the Cross Editor and get also the result:

From this Cross Editor windows I can via right-click open the Toad Views and can save this heterogeneous SQL here:

So it is now save in the Toad Views and it is re-usable:

Here you can find the Toad Views: