Foreign Servers - Visibility?

Question

Question

Scott Roth · Dec 6

#SQL #HealthShare #InterSystems IRIS for Health #Health Connect #InterSystems IRIS

First time trying to use Foreign Tables/Servers instead of Linked Tables...

Within the SQL Editor inside of the Managment Portal, or connecting through DBeaver JDBC how we can see what Foreign Servers have been defined? Is there a way to query and verify structure of the Foreign Server connection to know that we are building the correct Foreign Tables?

I attempted to create my first Foreign table but it failed when I tried to query the tables because it said the table could not be found. But when I sign into the Database via SQL Management Studio, I can see the table.

Product version: IRIS 2024.1

$ZV: IRIS for UNIX (Red Hat Enterprise Linux 8 for x86-64) 2024.1 (Build 267_2U) Tue Apr 30 2024 16:06:39 EDT [HealthConnect:3.5.0-1.m1]

Discussion (3)2

Log in or sign up to continue

Enrico Parisi · Dec 6

Actually, try this:

select * from INFORMATION_SCHEMA.FOREIGN_SERVERS

0 0

score 0 · Answer 1 · 2024-12-06T10:01:23-05:00

I'm no expert on foreign tables, but maybe you can try:

select * from INFORMATION_SCHEMA.FOREIGN_TABLES

This should provide a list of defined foreign tables and the Foreign Server it uses.

I'm not sure how to get Foreign Server that have no tables defined.

score 0 · Answer 2 · 2024-12-06T16:21:05-05:00

I went through the motions of creating a Foreign Server, and foreign tables to pull in MS SQL information in order to centralize a view that I had created on MS SQL Server and just pull it into IRIS.

After fumbling my way through the creation, I could query the tables, but if I would only query 1 column to pull into this sql statement, I would get an error.

I could not see the tables in DBeaver as I prefer to use it to write my sql statements.

As soon as I signed out of the Production, both the foreign server, and tables no longer exist.

Are Foreign tables a temporary thing?

I would like to create a view in IRIS so my team doesn't have to rely on others to pull the data and we could reference it instead of calling a JDBC lookup on MS SQL server.

I would also like to make the server, and tables available to all namespaces.

Can someone help, or should I contact WRC?