Re: Easy way to find Physical & Logical files in different libraries

On 7/13/2015 2:01 PM, CRPence wrote:

select dbffil, dbflib, dbffdp, dbfldp, dbftdp
from qsys.qadbldep /* publicly authorized logical file */
/* the physical file is QADBFDEP; needs *ALLOBJ SpcAut */
where dbflib <> dbfldp /* cross-library */
and dbftdp in ('D', 'V', 'I') /* I think this is exhaustive */

I found this quite helpful, thanks Chuck.

Paying it forward, I added the long SQL name for the dependent file,
because in my case I have views with long names (customer_sales_2012).

I used SYSTABLES because... well, I have authority to it and it's a JOIN
and I don't want to re-invent the wheel.

-- ordered by 'depends on'
select substr(dbffil, 1, 10) as file, dbflib as lib,
coalesce(table_name, dbffdp) as depfile, dbfldp as deplib, dbftdp as
type
from qsys.qadbldep
left join qsys2.systables
on substr(dbffdp, 1, 10) = system_table_name
and dbfldp = table_schema
where dbflib <> dbfldp
and dbftdp in ('D', 'V', 'I')
and dbflib <> 'QSYS'
and dbflib <> 'QSYS2'
order by 2, 1, 4, 3;