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

It seems to me that this might be a case of an imperfect implementation of the external tables.

That's an interesting observation! I would have expected the IRIS SQL syntax to be used here as well, even if it's referring to an external table from a MySQL database.

In IRIS, you need to use TOP x: IRIS Documentation - TOP Clause. As for MySQL, you're absolutely right—the SQL syntax there specifies LIMIT.

It seems to me that the IRIS SQL Engine might not determine the columns precisely before passing them to the external SQL Engine (in this case, MySQL). Ideally, an IRIS SQL translation into the language of the external SQL engine would occur here. This approach ensures true data virtualization. Sounds quite sophisticated! 😊