RE: SQL query to get names of LFs pointing to PFs created in incorrect libraries

You may use a composition of the SYSTABLES Catalog-View (for selecting your
physical files or SQL Tables) and the SQL Service RELATED_OBJECTS within the
SYSTOOLS Library.
The following examples will for all tables in YOURSCHEMA the dependent
logical files which are not in the same library as the physical files/tables
(i.e. which are not in YOURSCHEMA).

Select Table_Schema as PFTable_Schema, Table_Name as PFTable,
Schema_Name as LFSchema, SQL_Name as LFSQLName, System_Name
as LFSYSName
from SysTables a cross join
Lateral(Select *
from Table(Systools.Related_Objects(
Library_Name => a.Table_Schema,
File_Name => a.Table_Name))
Where SQL_Object_Type like '%LOGICAL%'
and Schema_Name <> a.Table_Schema
)
Where a.Table_Schema = 'YOURSCHEMA'
and Table_Type in ('P', 'T');

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of pr20251925
via RPG400-L
Sent: Friday, 14 February 2025 15:21
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Cc: pr20251925 <pr20251925@xxxxxxxxx>
Subject: SQL query to get names of LFs pointing to PFs created in incorrect
libraries

Hi,

How to extract all LFs which are created based on PFs(which are in incorrect
libraries)

For example LF1 which is there in LIB1 and has been created based on PF1 in
Lib2( ideally should have been created in LIB1 only).

So Any SQL query by which detailss of all Such LFs could be extracted?

Thanks...
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.