Skip to content

Instantly share code, notes, and snippets.

@forstie
Created September 18, 2019 23:57
Show Gist options
  • Save forstie/3442abb3ba50c1aea487b47849189b33 to your computer and use it in GitHub Desktop.
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!
--
-- 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;
@zkarj735
Copy link

zkarj735 commented Nov 9, 2021

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