Re: File not in QSYS2.SYSTABLES

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