Re: File not in QSYS2.SYSTABLES

[Peter Dow <petercdow@xxxxxxxxx>]

And just to add to the discussion, SYSTABLES is itself an SQL VIEW. You 
can do DSPFD QSYS2/SYSTABLES to see the SQL statement used to create it, 
and in that you will see the FROM files Rob mentioned, as well as the 
WHERE clause that apparently excludes SQL VIEWS. Given that, you could 
create your own view with different selection criteria.

CREATE VIEW systables
            (
                        table_name for "NAME",
                        table_owner FOR "CREATOR",
                        table_type FOR "TYPE",
                        column_count FOR "COLCOUNT",
                        row_lengthfor "RECLENGTH",
                        table_text FOR "LABEL",
                        long_comment FOR "REMARKS",
                        table_schema FOR "DBNAME",
                        last_altered_timestamp FOR "ALTEREDTS",
                        system_table_name FOR "SYS_TNAME",
                        system_table_schema FOR "SYS_DNAME",
                        file_type FOR "FILETYPE",
                        base_table_schema FOR "TBDBNAME",
                        base_table_name FOR "TBNAME",
                        base_table_member FOR "TBMEMBER",
                        system_table FOR "SYSTABLE",
                        select_omit FOR "SELECTOMIT",
                        is_insertable_into FOR "INSERTABLE",
                        iasp_number FOR "IASPNUMBER",
                        enabled,
                        maintenance FOR "MAINTAIN",
                        REFRESH,
                        refresh_time FOR "REFRESHDTS",
                        mqt_definition FOR "MQTDEF",
                        ISOLATION,
                        partition_table FOR "PART_TABLE",
                        table_definer FOR "DEFINER"
            )
            AS
SELECT          dbxlfi,
                varchar(strip(dbxown,trailing),128),
                substr(dbxatr,1,1),
                integer(dbxnfl),
                integer(dbxrdl),
                dbxtxt,
                dbxrem,
                dbxlb2,
                dbxats,
                dbxfil,
                dbxlib,
                dbxtyp,
                dbxalb,
                dbxafl,
                dbxamb,
                dbxsys,
                dbxso,
                CASE dbxinsert
                                WHEN 'Y' THEN 'YES'
                                ELSE 'NO'
                END bx_pasp,
                CASE dbxmqtopt
                                WHEN 'E' THEN 'YES'
                                WHEN 'D' THEN 'NO'
                                ELSE dbxmqtopt
                END,
                CASE dbxmqtmain
                                WHEN 'U' THEN 'USER'
                                WHEN 'S' THEN 'SYSTEM'
                                ELSE dbxmqtmain
                END,
                CASE dbxmqtrmod
                                WHEN 'D' THEN 'DEFERRED'
                                WHEN 'I' THEN 'IMMEDIATE'
                                ELSE dbxmqtrmod
                END,
                dbxmqtref,
                dbxmqttxt,
                dbxmqtcmt,
                CASE
                                WHEN dbxpart > 0 THEN 'YES'
                                ELSE 'NO'
                END,
                varchar(strip(dbxdefiner),128)
FROM            qsys.qadbxref
LEFT OUTER JOIN qsys.qadbxmqt
ON              dbxlib = dbxmqtlib
AND             dbxfil = dbxmqtfil
WHERE           dbxrel = 'Y'
AND             dbxatr <> 'IX'
AND             dbxatr <> 'CL'



On 10/8/2019 8:16 AM, Rob Berendt wrote:
> That, and an index or view is not considered a table.
>
> Look for the files QADB* for a slightly more thorough, but primitive, look at all files.  You'll probably find that object in one of those.  These have been around since 3.1.
>
> Rob Berendt