I was hoping that this would be an excuse to dig into the UDTF that
Birgitta wrote for
https://www.ibm.com/developerworks/ibmi/library/i-power-of-udtf/ but I
couldn't get it to join and return info for each row. I dug out another
little chunk of code from a utility service program, wrapped it as a UDF
and this works.
http://code.midrange.com/5eef431128.html Create the module/service
program/UDF and then just change your sql to:
SELECT A.DBXLIB as SRCPF_LIBR
, A.DBXFIL as SRCPF_NAME
, B.SYSTEM_TABLE_MEMBER as SrcMbrName
, UDFRTVMBRD(A.DBXLIB,A.DBXFIL,B.SYSTEM_TABLE_MEMBER) as SrcMbrDesc
, B.LAST_SOURCE_UPDATE_TIMESTAMP as SRCPF_UDAT
, B.SOURCE_TYPE as SRCPF_SRCTYPE
, b.CREATE_TIMESTAMP as Source_Create_Timestamp
, b.LAST_CHANGE_TIMESTAMP as Source_Type_Text_Change_Timestamp
FROM QSYS.QADBXATR A
, LATERAL
( SELECT * FROM TABLE
( QSYS2.PARTITION_STATISTICS(A.DBXLIB, A.DBXFIL)
) AS X
) AS B
WHERE A.DBXATR = 'PF'
and A.DBXREL = 'Y'
and A.DBXTYP = 'S'
AND B.SOURCE_TYPE IS NOT NULL
Kevin Bucknum
Senior Programmer Analyst
MEDDATA/MEDTRON
Tel: 985-893-2550
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Dan
Sent: Monday, May 23, 2016 5:27 PM
To: Midrange Systems Technical Discussion
Subject: Member Text of source member not in SYSPSTAT; where to find?
So, last year, some of you helped me to cobble together the following
SQL, which produces a list of all source members on the system:
SELECT A.DBXLIB as SRCPF_LIBR
, A.DBXFIL as SRCPF_NAME
, B.SYSTEM_TABLE_MEMBER as SrcMbrName
, B.LAST_SOURCE_UPDATE_TIMESTAMP as SRCPF_UDAT
, B.SOURCE_TYPE as SRCPF_SRCTYPE
, b.CREATE_TIMESTAMP as Source_Create_Timestamp
, b.LAST_CHANGE_TIMESTAMP as Source_Type_Text_Change_Timestamp
FROM QSYS.QADBXATR A
, LATERAL
( SELECT * FROM TABLE
( QSYS2.PARTITION_STATISTICS(A.DBXLIB, A.DBXFIL)
) AS X
) AS B
WHERE A.DBXATR = 'PF'
and A.DBXREL = 'Y'
and A.DBXTYP = 'S'
AND B.SOURCE_TYPE IS NOT NULL
Now, I am looking for the member text for the source member. I am
surprised it is not in SYSPSTAT. I looked in the IBM i catalog tables
and views (
www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcatalogtbls.ht
m),
but came up empty. It seems to me that the member text must be stored
somewhere in the i catalog. Anyone know where to find this?
- Dan
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
midrange.com
