Re: sql, Using a variable or column name on a 'connect to'

playing around with this...

the following works in a UDTF

begin
delete from lcllib.mytable;

insert into lcllib.mytable;
select current_server, job_name
from table(QSYS2.ACTIVE_JOB_INFO(
SUBSYSTEM_LIST_FILTER => 'SOMESUBSYS')) X
where exists( select 1 from rmtsys1.sysibm.sysdummy1);

insert into lcllib.mytable;
select current_server, job_name
from table(QSYS2.ACTIVE_JOB_INFO(
SUBSYSTEM_LIST_FILTER => 'SOMESUBSYS')) X
where exists( select 1 from rmtsys2.sysibm.sysdummy1);

return select * from lcllib.mytable;

And the UDTF can be the basis of a local view..

next week...try to run the inserts dynamically, so I can run the code in a
loop with the system name changed for each iteration...

Charles


On Fri, Feb 1, 2019 at 8:10 AM Bryan Dietz <bdietz400@xxxxxxxxx> wrote:

This is on a similar track, but I found this Twitter from Scott F.
that is along similar lines as the "connect to"


Hey, #SQL #Db2fori users...
If you code a table based 3-part name in your WHERE clause, your UDTF
invocation will execute on the target RDB.
-- Example:
select current_server, h.* from table(qsys2.history_log_info()) h
where exists (select 1 from rmtdb.sysibm.sysdummy1);


maybe it helps or spurs additional thought.

Bryan
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com