Re: SQL7008 when trying to create view

On 06-Jun-2017 16:36 -0600, Dan wrote:

So, giddy with excitement over getting the new TEXT column added to
the SYSPARTITIONSTAT view, I attempted to wrap the following into a
view:

SELECT A.DBXLIB as SRCPF_LIBR
, A.DBXFIL as SRCPF_NAME
, B.SYSTEM_TABLE_MEMBER as SrcMbrName
, B.SOURCE_TYPE as SrcMbrType
, B.LAST_SOURCE_UPDATE_TIMESTAMP as SrcMbrUpdT
, b.CREATE_TIMESTAMP as SrcMbrCrtT
, b.LAST_CHANGE_TIMESTAMP as SrcMbrLCTS
, b.TEXT as SrcMbrText
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

But, when I wrapped the above query with "CREATE VIEW ABC AS (" and
")", I got
SQL7008: QADBXATR in QSYS not valid for operation. The reason code is
7. 7 -- A based-on table used in creation of a view is not valid.
Either the table is program described table or it is in a temporary
schema.

It doesn't appear to me that either reason provided applies to
QADBXATR in QSYS.

Ideas?

File QADBXATR is a DDS LF; an additional reason for RC7, but not explicitly listed cause. Such a VIEW must be created referencing the "table" [or DDS PF] named QADBXREF instead. Note: The Special Authority (SPCAUT) *ALLOBJ is a requirement for the User Profile used to issue the CREATE VIEW. Note: See also Message Description for msg CPF32D1 [WRKMSGD CPF32D1] for caveats on creating such a permanent object over a System Database Cross Reference file [as also applies to catalog VIEW files in user libraries].