Combining results from 2 tables into one table

[bryn_griffith]

Hi all

Hoping someone can help me with this ... fingers crossed.

I'm trying to create a table which contains the following information:
1. Name and library of currently attached journal receivers
2. Object authorities and object owner for the currently attached journal receivers

I can get the information for step 1 by doing the following:

SELECT ATTACHED_JOURNAL_RECEIVER_NAME AS JRNRCV,ATTRCVLIB AS JRNRCVLIB FROM QSYS2.JOURNAL_INFO WHERE JOURNAL_LIBRARY NOT LIKE 'Q%'
ORDER BY ATTACHED_JOURNAL_RECEIVER_NAME

What I'd then like to do is retrieve the object authority and owner details for the currently attached journal receivers and combine them into one table with the results from step 1.
I know this information is in QSYS2.OBJECT_PRIVILEGES, the following statement is an example of what I've used to identify this info:

SELECT SYSTEM_OBJECT_NAME AS JRNRCV,SYSTEM_OBJECT_SCHEMA AS JRNRCVLIB,OBJECT_TYPE AS OBJTYPE,OWNER AS OWNER,AUTHORIZATION_NAME AS USRPRF,OBJECT_AUTHORITY AS OBJ_AUTH
FROM QSYS2.OBJECT_PRIVILEGES WHERE SYSTEM_OBJECT_NAME = 'JRNRCV1' OR SYSTEM_OBJECT_NAME = 'JRNRCV2' OR SYSTEM_OBJECT_NAME = 'JRNRCV3'
ORDER BY SYSTEM_OBJECT_NAME

I’ve specified the (made up) name of some attached receivers into the above statement to create an example, but this isn't something I want to do on a permanent basis.

Ideally, I’d like the table from the combined results to look like this – if possible:

JRNRCV	JRNRCVLIB	OBJTYPE	OWNER	USRPRF#1	USRPRF#1 OBJ_AUTH	USRPRF#2	USRPRF#2 OBJ_AUTH	USRPRF#3	USRPRF#3 OBJ_AUTH
JRNRCV1	RCVLIB1	*JRNRCV	OWNER1	OWNER1	*ALL	USER1	*USE	*PUBLIC	*EXCLUDE
JRNRCV2	RCVLIB2	*JRNRCV	OWNER2	OWNER2	*ALL	USER2	*USE	*PUBLIC	*EXCLUDE
JRNRCV3	RCVLIB3	*JRNRCV	OWNER3	OWNER3	*ALL	USER3	*USE	*PUBLIC	*EXCLUDE

Any help is greatly appreciated as this is something I need to make repeatable as we have 40+ LPARs to administer.

Thanks

Bryn

P.S. I'm open to any suggestions that will help me achieve my goal of one table containing the attached receivers and their associated authorities - I won't be offended if someone rips up the above suggestion and replaces it with something entirely different :-)

[Dennis See]

Hi Bryn,
I don't think you can extract multiple rows of info, into multiple columns of info (ie USRPF#1, USPRF#2,... columns). However, to combine the tables, the statement below would do the job, with the addition of the WITH common-table-expression prefix. However, it isn't efficient (probably to do with indexes) when I tried it as it took a long time to run and I didn't wait for completion. I suppose you need to add a "CREATE TABLE XXX AS " statement (or INSERT statement) to save the data in a batch run, as it would take a long time to run.
WITH TEMPFILE AS (
SELECT ATTACHED_JOURNAL_RECEIVER_NAME AS JRNRCV,
ATTRCVLIB AS JRNRCVLIB FROM QSYS2.JOURNAL_INFO
WHERE JOURNAL_LIBRARY NOT LIKE 'Q%'
)
SELECT SYSTEM_OBJECT_NAME AS JRNRCV,
SYSTEM_OBJECT_SCHEMA AS JRNRCVLIB,
OBJECT_TYPE AS OBJTYPE,
OWNER AS OWNER,
AUTHORIZATION_NAME AS USRPRF,
OBJECT_AUTHORITY AS OBJ_AUTH
FROM QSYS2.OBJECT_PRIVILEGES INNER JOIN TEMPFILE
ON TEMPFILE.JRNRCVLIB = SYSTEM_OBJECT_SCHEMA
AND TEMPFILE.JRNRCV = SYSTEM_OBJECT_NAME
ORDER BY SYSTEM_OBJECT_NAME