An earlier way to get Table function data using 3 part name

In August I wrote about a new way to get data from another partition using the three part name with the new REMOTE TABLE. I said that I did not know of another way to get data from a Table function using the three part name.

I received an answer from IBM Db2 for i member Sue Romano with an example of how to do it for older releases. The example she gave she used the WHERE EXISTS clause with the three part name in it.

I use the three part name to get all kinds of information from other partitions, and in this example I will be giving a couple of examples:

1. Using the ACTIVE_JOB_INFO to get data about the active jobs on the other partition
2. Producing a list of objects on the remote partition using OBJECT_STATISTICS

In these examples I am going to call the remote partition OTHERSYS, and the partitions I am running these examples on are running IBM i 7.4.

Let me start by showing how to get data from ACTIVE_JOB_INFO. If I want to get a list of all jobs and all the information about them I could just use the following statement:

01 SELECT * 02 FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) 03 WHERE EXISTS (SELECT * FROM OTHERSYS.SYSIBM.SYSDUMMY1)

Lines 1 and 2: Are as I would use on this partition.

Line 3: This is what defines and makes the connection to the OTHERSYS partition. The Table SYSDUMMY, in the library SYSIBM, is as its name suggest a dummy file found in all IBM i partitions.

I am not going to show the results from this statement here as it is too much.

What I am going to show is the following: I created a CL program on OTHERSYS called TESTCL, it is unimportant what this program does. I submitted a call to this program to batch, on OTHERSYS, with the job name "SIMON". I then went looking for it by modifying the above SQL statement:

01 SELECT JOB_NAME,JOB_NAME_SHORT,SUBSYSTEM,JOB_TYPE 02 FROM TABLE(QSYS2.ACTIVE_JOB_INFO( 03 SUBSYSTEM_LIST_FILTER => 'QBATCH', 04 CURRENT_USER_LIST_FILTER => 'SIMON')) 05 WHERE EXISTS (SELECT * FROM OTHERSYS.SYSIBM.SYSDUMMY1)

Line 1: I am only interest in these four columns.

Lines 2 – 4: The ACTIVCE_JOB_INFO and I am passing two parameters to it to minimize the results it will return:

1. SUBSYSTEM_LIST_FILTER:  I only want to return results from the QBATCH subsystem
2. CURRENT_USER_LIST_FILTER:  And then only those jobs submitted by me, user profile SIMON

Line 5: This line means I want to return the results from OTHERSYS.

The results show that the job is running on OTHERSYS:

JOB_NAME JOB_ JOB_NAME _SHORT SUBSYSTEM TYPE ------------------ -------- --------- ---- 705571/SIMON/MYJOB MYJOB QBATCH BCH

In my next example I want to search if the program TESTCL is in my library, MYLIB, on OTHERSYS. For this I would use the OBJECT_STATISTICS Table function. In this example I am only interested in two columns from the Table function and a SQL special register to return the server name of the partition:

01 SELECT CURRENT_SERVER AS "Server", 02 OBJNAME,OBJCREATED 03 FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','PGM','TESTCL')) 04 WHERE EXISTS (SELECT * FROM OTHERSYS.SYSIBM.SYSDUMMY1)

Line 1: I am using the CURRENT_SERVER special register to return the name of the other partition.

Line 2: I only want the object name and object created timestamp for the object I am searching for.

Line 3: I am using OBJECT_STATISTICS to return results for objects in my library that are programs and called TESTCL.

Line 4: This is where I define the connection to OTHERSYS.

My results are:

Server OBJNAME OBJCREATED -------- ------- -------------------------- OTHERSYS TESTCL 2023-MM-DD 23:19:36.000000

CURRENT_SERVER returned the server name of OTHERSYS to prove to me that the data is being returned from the other partition.

In my final example I am showing how I can create a file on this partition containing output from the ACTIVE_JOB_INFO Table function on OTHERSYS:

01 CREATE TABLE QTEMP.OTHERSYS_ACTIVE_JOBS 02 AS 03 (SELECT * 04 FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) 05 WHERE EXISTS (SELECT * FROM FCB.SYSIBM.SYSDUMMY1)) 06 WITH DATA

Line 1: I am creating the output file in the library QTEMP.

Lines 3 and 4: I am retrieving all columns and all rows from ACTIVE_JOB_INFO.

Line 5: Making the connection to OTHERSYS.

Line 6: And I want the Table function to add data to my output file.

I am sure you now get the idea of how to use the WHERE EXISTS to get data from remote Table functions.

"Thank you" to Sue for informing of this.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.