Testing FOREIGN SERVER and FOREIGN TABLE feature

Article

Andreas Schneider · Jan 12 1m read

Open Exchange

#Docker #JDBC #Namespace #SQL #Testing #InterSystems IRIS

Hi! I've extended my demo repository, andreas5588/demo-dbs-iris, to make it easy to test the FOREIGN SERVER and FOREIGN TABLE features in IRIS.

To achieve this, I created a namespace called FEDERATION. The idea is as follows:

Set up JDBC connections for each namespace.
Create a FOREIGN SERVER within the FEDERATION namespace for each connection.
Define a FOREIGN TABLE a least for one table based on each foreign server.

The Script: demo-dbs-iris/src/sql/02_create_foreign_server.sql

IRIS does not support executing SQL statements that combine tables from different namespaces. To address this limitation, this federated namespace leverages the concept of creating foreign servers to include other namespaces. By defining foreign tables, it becomes possible to seamlessly combine tables across namespaces, allowing users to write SQL statements that unify data into a single query. This namespace is used to explore this feature, serves as a practical demonstration of this capability, showcasing how to explore and utilize it.

Run this demo container directly via:

docker pull andreasschneiderixdbde/demo-dbs-iris:latest

After that you can do queries like that:

Have fun testing and improving the demo

Andreas

David.Satorres6134 · Jan 13

Hi Andreas,

If I understand it correctly, all databases are in the same instance but simply in different namespaces. I've always overcome this by simply mapping the packages and globals over the namespaces (or in %ALL). In what is your demo enhancing it?

Thanks for the approach, though! :-)

0 0

Andreas Schneider · 1 hr ago

David,
I saw this demo as an opportunity to explore the potential of this type of integration (foreign server), as the concept appears to offer a highly versatile and general-purpose solution.Initially, my plan was to integrate DuckDB; however, I encountered some challenges during the process. Consequently, I chose to begin with the simplest integration, with the intention of gradually expanding it to include additional variants over time.

If the namespaces are running on a single instance, the solution you described seems like an excellent approach and likely offers better performance as well. By the way: Your approach might also work with multiple instances if the database is integrated as a remote database?! However, I haven't had a chance to test this, so I'm not sure how the IRIS SQL engine would handle such a setup.

Andreas