IMHO this is a big deal, I can now use the SQL three part name to retrieve data from Db2 for i SQL Table functions on other partitions. The three part name is where I give the "database" name along with the schema and object name. In the IBM i world I can translate that to:
partition.library.object |
The dot (otherwise known as: period or full stop ( . )) must be used as the separator, rather than the slash ( / ).
For example if I want to retrieve a list of objects of the files in my library on another partition, I will be calling the other partition OTHERSYS. I can use the following statement to retrieve data from the other partition using the three part name with the, for example the SYSFILE View:
01 SELECT * FROM OTHERSYS.QSYS2.SYSFILES 02 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' |
If I tried using the three part name with a table function, let me use the ACITVE_JOB_INFO with a Select statement:
01 SELECT * 02 FROM TABLE(OTHERSYS.QSYS2.ACTIVE_JOB_INFO()) |
I receive the following error message:
SQL State: 42601 Vendor Code: -104 Message: [SQL0104] Token . was not valid. |
The message is informing me it does not know what to do with the second period name in the Table function name.
Added in IBM i 7.5 Technology Refresh 1 and 7.4 TR7 is a new "table reference": REMOTE. By using it I can reference Table functions on remote partitions. I can change the statement above to includer the remote:
01 SELECT * 02 FROM REMOTE TABLE(OTHERSYS.QSYS2.ACTIVE_JOB_INFO()) |
Line 2: As you can see I now say "REMOTE TABLE" instead of just "TABLE". I must qualify the Table function with its three part name.
I am not going to show the results of this statement. I am sure you all know what it looks like.
I have a program I wrote some time ago to list all of the jobs in message wait from six partitions into one subfile. This program was written before this new feature was available. The way around the inability to access a Table function with the three part name was create a SQL View over the Table function. I can access all Views with the three part name.
With the new "REMOTE TABLE" I can access the data from a remote partition by using the following:
01 SELECT JOB_NAME,SUBSYSTEM,JOB_TYPE,JOB_STATUS,FUNCTION 02 FROM REMOTE TABLE(OTHERSYS.QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*ALL')) 03 WHERE JOB_STATUS = 'MSGW' |
Another Table function I use to compare data between partitions is OBJECT_STATISTICS. The following statement would allow me generate the table that contains a list of all the objects in my library, MYLIB, on the other partition:
01 CREATE TABLE QTEMP.OUTFILE AS
02 (SELECT *
03 FROM REMOTE TABLE(TRAIN400.QSYS2.OBJECT_STATISTICS('MYLIB','ALL')))
04 WITH DATA
|
I don't think there is much point in giving more examples as the two above clearly show what I need to do to access a remote SQL Table function.
As I work in a multi-partition environment this is a big deal that is going to make my life a lot easier.
You can learn more about this from the IBM website:
December 6, 2023
There is another way you can get to a Table Function on a remote partition that works for earlier TRs and releases. You can read about it: An earlier way to get Table function data using 3 part name
This article was written for IBM i 7.5 TR1 and 7.4 TR7.
This is really nice. i havent noticed that, and have been looking for way to do this :-)
ReplyDeleteI found a strange thing while trying to use this great feature. i would like to compare a list of object libs on two systems, so i made this : with TESTMILJ AS (SELECT MILLBN FROM IASP01.MENU.TSTMLBPF WHERE milcsr = 'J')
ReplyDeleteSELECT * FROM testmilj, REMOTE TABLE(iasp01.QSYS2.OBJECT_STATISTICS(testmilj.millbn, '*ALL')) A
except
SELECT * FROM testmilj, TABLE(QSYS2.OBJECT_STATISTICS(testmilj.millbn,'*ALL')) b
;
turns out that i shows no diff, even that i know there would be. After investigation, it turns out that number 2 object statistics also rundt on remote system !!!!
Been using 3 part naming for awhile, but not for table functions. Cool to see the ability added with the REMOTE keyword, thanks Simon!
ReplyDeleteWe're unfortunately still on V7R3, but look forward to having this available. For those out there like us, there is a workaround. Not great, but this works
ReplyDeletecreate table othersystem.qtemp.xx as (
select * from table(qsys2.active_job_info())
with data;
create table qtemp.yy as (
select *
from othersystem.qtemp.xx )
with data;
The ability to invoke a table function on a remote system is available without the REMOTE TABLE support. You simply need to include a table with a 3-part name in your query. While it isn't obvious, it gets the job done. Try this on earlier releases:
ReplyDeletecreate table qtemp.xx as (
select * from table(qsys2.active_job_info())
where exists (select * from othersys.sysibm.sysdummy1)
)
with data;
Thank you Sue.
DeleteThe work-around is to use a WHERE clause with the EXISTS kwd. WHERE EXISTS (SELECT * FROM CHICAGO.SYSIBM.SYSDUMMY1). -- This causes the statement to run remotely. And no, you still cannot run a query over 2 or more partitions with one statement.
ReplyDeleteThank you
DeleteCould please explain us along with example => (Equal greater) symbol meaning in sql statements, Sign = (Equal) will serve the same purpose ?
ReplyDeleteThanks in advance...
The "=>" denotes, when using the parameter names in table functions, what follows is the value for this parameter.
DeleteI have always assume that this is used instead of just "=" with parameters that it is a "reserved character" that cannot be used because of Db2 compatibility between its different "flavors" or the ANSI standard.