Created
September 18, 2019 23:57
-
-
Save forstie/3442abb3ba50c1aea487b47849189b33 to your computer and use it in GitHub Desktop.
For User Defined Table Functions (UDTFs), a trick needs to be employed. Include a where clause whose only purpose is to push the UDTF invocation to the remote database!
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- Traditional 3-part name SQL would reference a table, view, or procedure | |
-- | |
call otherRDB.schema.procedure123(); | |
stop; | |
insert into localschema.fact_table | |
select * from otherRDB.remoteschema.fact_table; | |
stop; | |
-- | |
-- UDTF query that is run on a remote Db2 for i, and the results returned to the local IBM i | |
-- | |
select current server, h.* | |
from table ( | |
qsys2.history_log_info() | |
) h | |
where exists | |
(select 1 from otherRDB.sysibm.sysdummy1); | |
stop; |
Thanks, I've used the CREATE TABLE AS caveat to effect my requirement. Hooray for QTEMP.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi,
There currently isn't a way to explicitly reference more than one database in a single query.
There are exceptions to the rule for some non-Query statements like INSERT with remote Subselect, and CREATE TABLE AS with remote subselect.
You could also have a UDTF that queries multiple IBM i's and then returns the results, making it appear that a single query can extract, compare, etc multiple IBM i's in a single query.
Regards, Scott