Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

tried below but It's been running for so long and consuming too much CPU%

:

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

tried below but It's been running for so long and consuming too much CPU%

:

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

tried below but It's been running for so long and consuming too much CPU%

:

------------------------------
jerry ven

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

You may try the following query which will check the depending logical files for all physical files within a specific schema. The example will return all logical files that are NOT in the same library as the physical.

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');

Note: May also run sometime!

------------------------------
Birgitta Hauser
Database and Software Engineer
Birgitta Hauser - Modernization-Education-Consulting on IBM i (selfemployed)
Kaufering
+49 170 5269964

Original Message:
Sent: Fri February 14, 2025 08:03 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

tried below but It's been running for so long and consuming too much CPU%

:

------------------------------
jerry ven

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

So,

below is below SQL not sufficient to provide the desired LFs details here?

"select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

"

Thanks...

You may try the following query which will check the depending logical files for all physical files within a specific schema. The example will return all logical files that are NOT in the same library as the physical.

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');

Note: May also run sometime!

------------------------------
Birgitta Hauser
Database and Software Engineer
Birgitta Hauser - Modernization-Education-Consulting on IBM i (selfemployed)
Kaufering
+49 170 5269964

Original Message:
Sent: Fri February 14, 2025 08:03 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

tried below but It's been running for so long and consuming too much CPU%

:

------------------------------
jerry ven

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

What did you find out by trying it?

Is it more or less inclusive than "assuming" the PF is in the remarks?

What files were you finding that it missed?

But Birgitta's suggestion bears looking into.

So,

below is below SQL not sufficient to provide the desired LFs details here?

"select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

"

Thanks...

You may try the following query which will check the depending logical files for all physical files within a specific schema. The example will return all logical files that are NOT in the same library as the physical.

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');

Note: May also run sometime!

------------------------------
Birgitta Hauser
Database and Software Engineer
Birgitta Hauser - Modernization-Education-Consulting on IBM i (selfemployed)
Kaufering
+49 170 5269964

Original Message:
Sent: Fri February 14, 2025 08:03 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

tried below but It's been running for so long and consuming too much CPU%

:

------------------------------
jerry ven

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

i found different data set in both sql 

What did you find out by trying it?

Is it more or less inclusive than "assuming" the PF is in the remarks?

What files were you finding that it missed?

But Birgitta's suggestion bears looking into.

So,

below is below SQL not sufficient to provide the desired LFs details here?

"select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

"

Thanks...

You may try the following query which will check the depending logical files for all physical files within a specific schema. The example will return all logical files that are NOT in the same library as the physical.

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');

Note: May also run sometime!

------------------------------
Birgitta Hauser
Database and Software Engineer
Birgitta Hauser - Modernization-Education-Consulting on IBM i (selfemployed)
Kaufering
+49 170 5269964

Original Message:
Sent: Fri February 14, 2025 08:03 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

tried below but It's been running for so long and consuming too much CPU%

:

------------------------------
jerry ven

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

for SQL  where i have mentioned 'YOURSCHEMA'  in that SQL i got  some records but that schema it self was not found when i ran 'select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA'

i found different data set in both sql 

What did you find out by trying it?

Is it more or less inclusive than "assuming" the PF is in the remarks?

What files were you finding that it missed?

But Birgitta's suggestion bears looking into.

So,

below is below SQL not sufficient to provide the desired LFs details here?

"select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

"

Thanks...

You may try the following query which will check the depending logical files for all physical files within a specific schema. The example will return all logical files that are NOT in the same library as the physical.

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');

Note: May also run sometime!

------------------------------
Birgitta Hauser
Database and Software Engineer
Birgitta Hauser - Modernization-Education-Consulting on IBM i (selfemployed)
Kaufering
+49 170 5269964

Original Message:
Sent: Fri February 14, 2025 08:03 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

tried below but It's been running for so long and consuming too much CPU%

:

------------------------------
jerry ven

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

i was expecting this one would cover all records as i am not specifically providing any library name here and it will cover all such libraries as it's based on 'qsys2.sysindexes' but it appears it's not showing that library itself  for which i can fetch records by supplying 'YOURSCHEMA' in another SQL query.

Thanks..

for SQL  where i have mentioned 'YOURSCHEMA'  in that SQL i got  some records but that schema it self was not found when i ran 'select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA'

i found different data set in both sql 

What did you find out by trying it?

Is it more or less inclusive than "assuming" the PF is in the remarks?

What files were you finding that it missed?

But Birgitta's suggestion bears looking into.

So,

below is below SQL not sufficient to provide the desired LFs details here?

"select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

"

Thanks...

You may try the following query which will check the depending logical files for all physical files within a specific schema. The example will return all logical files that are NOT in the same library as the physical.

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');

Note: May also run sometime!

------------------------------
Birgitta Hauser
Database and Software Engineer
Birgitta Hauser - Modernization-Education-Consulting on IBM i (selfemployed)
Kaufering
+49 170 5269964

Original Message:
Sent: Fri February 14, 2025 08:03 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

tried below but It's been running for so long and consuming too much CPU%

:

------------------------------
jerry ven

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

for SQL  where i have mentioned 'YOURSCHEMA'  in that SQL i got  some records but that schema it self was not found when i ran 'select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA'

i found different data set in both sql 

What did you find out by trying it?

Is it more or less inclusive than "assuming" the PF is in the remarks?

What files were you finding that it missed?

But Birgitta's suggestion bears looking into.

So,

below is below SQL not sufficient to provide the desired LFs details here?

"select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

"

Thanks...

You may try the following query which will check the depending logical files for all physical files within a specific schema. The example will return all logical files that are NOT in the same library as the physical.

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');

Note: May also run sometime!

------------------------------
Birgitta Hauser
Database and Software Engineer
Birgitta Hauser - Modernization-Education-Consulting on IBM i (selfemployed)
Kaufering
+49 170 5269964

Original Message:
Sent: Fri February 14, 2025 08:03 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

tried below but It's been running for so long and consuming too much CPU%

:

------------------------------
jerry ven

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

Can you reply with an sql example?  I'm not understanding what you are saying.

Are you saying you tried

'select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

  and system_table_schema = 'YOURSCHEMA'

?  If so, they just try it out first without that 'and'.  You may have it backward and need to try system_index_schema instead.  But, again, try it first without the 'and'

for SQL  where i have mentioned 'YOURSCHEMA'  in that SQL i got  some records but that schema it self was not found when i ran 'select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA'

i found different data set in both sql 

What did you find out by trying it?

Is it more or less inclusive than "assuming" the PF is in the remarks?

What files were you finding that it missed?

But Birgitta's suggestion bears looking into.

So,

below is below SQL not sufficient to provide the desired LFs details here?

"select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

"

Thanks...

You may try the following query which will check the depending logical files for all physical files within a specific schema. The example will return all logical files that are NOT in the same library as the physical.

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');

Note: May also run sometime!

------------------------------
Birgitta Hauser
Database and Software Engineer
Birgitta Hauser - Modernization-Education-Consulting on IBM i (selfemployed)
Kaufering
+49 170 5269964

Original Message:
Sent: Fri February 14, 2025 08:03 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

tried below but It's been running for so long and consuming too much CPU%

:

------------------------------
jerry ven

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

Yes, I had tried this SQL as advised on post number 3 here "select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA"

and when i compared it's output with SQL query advised in post number 5 then i found that schema which i had supplied in SQL query ( as per post 5) that schema itself  was not found when i searched it under where INDEX_SCHEMA = 'YOURSCHEMA' ( The same SCHEMA name which i had supplied as per post 5 SQL query)

Can you reply with an sql example?  I'm not understanding what you are saying.

Are you saying you tried

'select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

  and system_table_schema = 'YOURSCHEMA'

?  If so, they just try it out first without that 'and'.  You may have it backward and need to try system_index_schema instead.  But, again, try it first without the 'and'

for SQL  where i have mentioned 'YOURSCHEMA'  in that SQL i got  some records but that schema it self was not found when i ran 'select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA'

i found different data set in both sql 

What did you find out by trying it?

Is it more or less inclusive than "assuming" the PF is in the remarks?

What files were you finding that it missed?

But Birgitta's suggestion bears looking into.

So,

below is below SQL not sufficient to provide the desired LFs details here?

"select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

"

Thanks...

You may try the following query which will check the depending logical files for all physical files within a specific schema. The example will return all logical files that are NOT in the same library as the physical.

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');

Note: May also run sometime!

------------------------------
Birgitta Hauser
Database and Software Engineer
Birgitta Hauser - Modernization-Education-Consulting on IBM i (selfemployed)
Kaufering
+49 170 5269964

Original Message:
Sent: Fri February 14, 2025 08:03 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

tried below but It's been running for so long and consuming too much CPU%

:

------------------------------
jerry ven

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

and i thought SQL advised in post number 3 is better than post number 5 as in sql from post 3 we don't need to mention any specific library name ( 'YOURSCHEMA') and across system it will list down all such LFs.

Thanks

Yes, I had tried this SQL as advised on post number 3 here "select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA"

and when i compared it's output with SQL query advised in post number 5 then i found that schema which i had supplied in SQL query ( as per post 5) that schema itself  was not found when i searched it under where INDEX_SCHEMA = 'YOURSCHEMA' ( The same SCHEMA name which i had supplied as per post 5 SQL query)

Can you reply with an sql example?  I'm not understanding what you are saying.

Are you saying you tried

'select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

  and system_table_schema = 'YOURSCHEMA'

?  If so, they just try it out first without that 'and'.  You may have it backward and need to try system_index_schema instead.  But, again, try it first without the 'and'

for SQL  where i have mentioned 'YOURSCHEMA'  in that SQL i got  some records but that schema it self was not found when i ran 'select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA'

i found different data set in both sql 

What did you find out by trying it?

Is it more or less inclusive than "assuming" the PF is in the remarks?

What files were you finding that it missed?

But Birgitta's suggestion bears looking into.

So,

below is below SQL not sufficient to provide the desired LFs details here?

"select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

"

Thanks...

You may try the following query which will check the depending logical files for all physical files within a specific schema. The example will return all logical files that are NOT in the same library as the physical.

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');

Note: May also run sometime!

------------------------------
Birgitta Hauser
Database and Software Engineer
Birgitta Hauser - Modernization-Education-Consulting on IBM i (selfemployed)
Kaufering
+49 170 5269964

Original Message:
Sent: Fri February 14, 2025 08:03 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

tried below but It's been running for so long and consuming too much CPU%

:

------------------------------
jerry ven

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

Can you reply with an sql example?  I'm not understanding what you are saying.

Are you saying you tried

'select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

  and system_table_schema = 'YOURSCHEMA'

?  If so, they just try it out first without that 'and'.  You may have it backward and need to try system_index_schema instead.  But, again, try it first without the 'and'

for SQL  where i have mentioned 'YOURSCHEMA'  in that SQL i got  some records but that schema it self was not found when i ran 'select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA'

i found different data set in both sql 

What did you find out by trying it?

Is it more or less inclusive than "assuming" the PF is in the remarks?

What files were you finding that it missed?

But Birgitta's suggestion bears looking into.

So,

below is below SQL not sufficient to provide the desired LFs details here?

"select *
from qsys2.sysindexes
where SYSTEM_INDEX_SCHEMA <> SYSTEM_TABLE_SCHEMA

"

Thanks...

You may try the following query which will check the depending logical files for all physical files within a specific schema. The example will return all logical files that are NOT in the same library as the physical.

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');

Note: May also run sometime!

------------------------------
Birgitta Hauser
Database and Software Engineer
Birgitta Hauser - Modernization-Education-Consulting on IBM i (selfemployed)
Kaufering
+49 170 5269964

Original Message:
Sent: Fri February 14, 2025 08:03 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

tried below but It's been running for so long and consuming too much CPU%

:

------------------------------
jerry ven

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

not sure where i got this one:
--  description: _logical-not-same-lib-as-pf.sql
-- DBFTDP =  Dependency: D-data,V-view,I-indirect 
-- 
SELECT DBFFIL AS Physical_file,
       DBFLIB as Physical_file_lib, 
       DBFFDP as Logical_file, 
       DBFLDP AS LogLib_file_lib 
       ,i.*
FROM QSYS.QADBFDEP i
WHERE dbflib <> dbfldp 
and DBFLIB  not in('QSYS');

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

it's giving me an error "Not authorized to object QADBFDEP in QSYS type *FILE"  also in advice post no. 5 and 15 we need to supply library name manually which i want to avoid and want SQL query to track and list down all such LFs without providing library name there manually.

Thanks..

not sure where i got this one:
--  description: _logical-not-same-lib-as-pf.sql
-- DBFTDP =  Dependency: D-data,V-view,I-indirect 
-- 
SELECT DBFFIL AS Physical_file,
       DBFLIB as Physical_file_lib, 
       DBFFDP as Logical_file, 
       DBFLDP AS LogLib_file_lib 
       ,i.*
FROM QSYS.QADBFDEP i
WHERE dbflib <> dbfldp 
and DBFLIB  not in('QSYS');

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

Jerry - no, you don't need to supply a library in my example (post #15) - just try this:

select systables.system_table_schema, systables.system_table_name,
       related_objects.library_name, related_objects.system_name, related_objects.sql_object_type
from qsys2.systables as systables
join lateral (
     select *
     from table(
          systools.related_objects(systables.system_table_schema, systables.system_table_name)
     )
) as related_objects on true
where systables.table_type in ('P', 'T')
  and systables.system_table_schema <> related_objects.library_name;

But be prepared, that this might run a while - depending on your machine.

HTH

Daniel

it's giving me an error "Not authorized to object QADBFDEP in QSYS type *FILE"  also in advice post no. 5 and 15 we need to supply library name manually which i want to avoid and want SQL query to track and list down all such LFs without providing library name there manually.

Thanks..

not sure where i got this one:
--  description: _logical-not-same-lib-as-pf.sql
-- DBFTDP =  Dependency: D-data,V-view,I-indirect 
-- 
SELECT DBFFIL AS Physical_file,
       DBFLIB as Physical_file_lib, 
       DBFFDP as Logical_file, 
       DBFLDP AS LogLib_file_lib 
       ,i.*
FROM QSYS.QADBFDEP i
WHERE dbflib <> dbfldp 
and DBFLIB  not in('QSYS');

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

I am getting this error for post no. 19 

Message ID : SQL0199

Message: "Keyword WHERE not expected. valid tokens: <> = <> <=  !<

Cause : The keyword WHERE was not expected here. A sysntax error was detected at keyword WHERE.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:35 AM
From: Daniel Gross
Subject: LFs pointing to PFs from incorrect libraries

Jerry - no, you don't need to supply a library in my example (post #15) - just try this:

select systables.system_table_schema, systables.system_table_name,
       related_objects.library_name, related_objects.system_name, related_objects.sql_object_type
from qsys2.systables as systables
join lateral (
     select *
     from table(
          systools.related_objects(systables.system_table_schema, systables.system_table_name)
     )
) as related_objects on true
where systables.table_type in ('P', 'T')
  and systables.system_table_schema <> related_objects.library_name;

But be prepared, that this might run a while - depending on your machine.

HTH

Daniel

------------------------------
Daniel Gross
#IBMChampion
Senior Core Developer, Geis Group
Pegnitz, Germany
https://blog.qpgmr.de/

Original Message:
Sent: Mon February 17, 2025 09:30 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

it's giving me an error "Not authorized to object QADBFDEP in QSYS type *FILE"  also in advice post no. 5 and 15 we need to supply library name manually which i want to avoid and want SQL query to track and list down all such LFs without providing library name there manually.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:19 AM
From: Bryan Dietz
Subject: LFs pointing to PFs from incorrect libraries

not sure where i got this one:
--  description: _logical-not-same-lib-as-pf.sql
-- DBFTDP =  Dependency: D-data,V-view,I-indirect 
-- 
SELECT DBFFIL AS Physical_file,
       DBFLIB as Physical_file_lib, 
       DBFFDP as Logical_file, 
       DBFLDP AS LogLib_file_lib 
       ,i.*
FROM QSYS.QADBFDEP i
WHERE dbflib <> dbfldp 
and DBFLIB  not in('QSYS');

------------------------------
Bryan Dietz

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

in this example also (as per post no. 5) under where systables.system_table_schema = '...your-lib...'  we have to provide libary name manually which i want to avoid , so that when we run this sql query system automatically searches all such libraries and give us such LFs list .

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 10:19 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

I am getting this error for post no. 19 

Message ID : SQL0199

Message: "Keyword WHERE not expected. valid tokens: <> = <> <=  !<

Cause : The keyword WHERE was not expected here. A sysntax error was detected at keyword WHERE.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:35 AM
From: Daniel Gross
Subject: LFs pointing to PFs from incorrect libraries

Jerry - no, you don't need to supply a library in my example (post #15) - just try this:

select systables.system_table_schema, systables.system_table_name,
       related_objects.library_name, related_objects.system_name, related_objects.sql_object_type
from qsys2.systables as systables
join lateral (
     select *
     from table(
          systools.related_objects(systables.system_table_schema, systables.system_table_name)
     )
) as related_objects on true
where systables.table_type in ('P', 'T')
  and systables.system_table_schema <> related_objects.library_name;

But be prepared, that this might run a while - depending on your machine.

HTH

Daniel

------------------------------
Daniel Gross
#IBMChampion
Senior Core Developer, Geis Group
Pegnitz, Germany
https://blog.qpgmr.de/

Original Message:
Sent: Mon February 17, 2025 09:30 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

it's giving me an error "Not authorized to object QADBFDEP in QSYS type *FILE"  also in advice post no. 5 and 15 we need to supply library name manually which i want to avoid and want SQL query to track and list down all such LFs without providing library name there manually.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:19 AM
From: Bryan Dietz
Subject: LFs pointing to PFs from incorrect libraries

not sure where i got this one:
--  description: _logical-not-same-lib-as-pf.sql
-- DBFTDP =  Dependency: D-data,V-view,I-indirect 
-- 
SELECT DBFFIL AS Physical_file,
       DBFLIB as Physical_file_lib, 
       DBFFDP as Logical_file, 
       DBFLDP AS LogLib_file_lib 
       ,i.*
FROM QSYS.QADBFDEP i
WHERE dbflib <> dbfldp 
and DBFLIB  not in('QSYS');

------------------------------
Bryan Dietz

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

If you want to loop through all libraries, just remove the WHERE Condition ... but do not complain about the query is running too long

in this example also (as per post no. 5) under where systables.system_table_schema = '...your-lib...'  we have to provide libary name manually which i want to avoid , so that when we run this sql query system automatically searches all such libraries and give us such LFs list .

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 10:19 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

I am getting this error for post no. 19 

Message ID : SQL0199

Message: "Keyword WHERE not expected. valid tokens: <> = <> <=  !<

Cause : The keyword WHERE was not expected here. A sysntax error was detected at keyword WHERE.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:35 AM
From: Daniel Gross
Subject: LFs pointing to PFs from incorrect libraries

Jerry - no, you don't need to supply a library in my example (post #15) - just try this:

select systables.system_table_schema, systables.system_table_name,
       related_objects.library_name, related_objects.system_name, related_objects.sql_object_type
from qsys2.systables as systables
join lateral (
     select *
     from table(
          systools.related_objects(systables.system_table_schema, systables.system_table_name)
     )
) as related_objects on true
where systables.table_type in ('P', 'T')
  and systables.system_table_schema <> related_objects.library_name;

But be prepared, that this might run a while - depending on your machine.

HTH

Daniel

------------------------------
Daniel Gross
#IBMChampion
Senior Core Developer, Geis Group
Pegnitz, Germany
https://blog.qpgmr.de/

Original Message:
Sent: Mon February 17, 2025 09:30 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

it's giving me an error "Not authorized to object QADBFDEP in QSYS type *FILE"  also in advice post no. 5 and 15 we need to supply library name manually which i want to avoid and want SQL query to track and list down all such LFs without providing library name there manually.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:19 AM
From: Bryan Dietz
Subject: LFs pointing to PFs from incorrect libraries

not sure where i got this one:
--  description: _logical-not-same-lib-as-pf.sql
-- DBFTDP =  Dependency: D-data,V-view,I-indirect 
-- 
SELECT DBFFIL AS Physical_file,
       DBFLIB as Physical_file_lib, 
       DBFFDP as Logical_file, 
       DBFLDP AS LogLib_file_lib 
       ,i.*
FROM QSYS.QADBFDEP i
WHERE dbflib <> dbfldp 
and DBFLIB  not in('QSYS');

------------------------------
Bryan Dietz

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

So tried below SQL query for the same :

"

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
                        )  

"

But i got this error : Message ID SQ20484

Message: Parameter 2 required for routine RELATED_OBJECTES in SYSTOOLS.

Cause: A Call statement for routine RELATED_OBJECTS in schema SYSTOOLS specified with a named parameter is not valid. Parameter 2 must be specified on the call statment because it has no default value.

Recovery:  Specify a value for parameter 2.  Try the request again.

So what should be the coorected SQL query here now?

Thanks ...

If you want to loop through all libraries, just remove the WHERE Condition ... but do not complain about the query is running too long

in this example also (as per post no. 5) under where systables.system_table_schema = '...your-lib...'  we have to provide libary name manually which i want to avoid , so that when we run this sql query system automatically searches all such libraries and give us such LFs list .

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 10:19 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

I am getting this error for post no. 19 

Message ID : SQL0199

Message: "Keyword WHERE not expected. valid tokens: <> = <> <=  !<

Cause : The keyword WHERE was not expected here. A sysntax error was detected at keyword WHERE.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:35 AM
From: Daniel Gross
Subject: LFs pointing to PFs from incorrect libraries

Jerry - no, you don't need to supply a library in my example (post #15) - just try this:

select systables.system_table_schema, systables.system_table_name,
       related_objects.library_name, related_objects.system_name, related_objects.sql_object_type
from qsys2.systables as systables
join lateral (
     select *
     from table(
          systools.related_objects(systables.system_table_schema, systables.system_table_name)
     )
) as related_objects on true
where systables.table_type in ('P', 'T')
  and systables.system_table_schema <> related_objects.library_name;

But be prepared, that this might run a while - depending on your machine.

HTH

Daniel

------------------------------
Daniel Gross
#IBMChampion
Senior Core Developer, Geis Group
Pegnitz, Germany
https://blog.qpgmr.de/

Original Message:
Sent: Mon February 17, 2025 09:30 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

it's giving me an error "Not authorized to object QADBFDEP in QSYS type *FILE"  also in advice post no. 5 and 15 we need to supply library name manually which i want to avoid and want SQL query to track and list down all such LFs without providing library name there manually.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:19 AM
From: Bryan Dietz
Subject: LFs pointing to PFs from incorrect libraries

not sure where i got this one:
--  description: _logical-not-same-lib-as-pf.sql
-- DBFTDP =  Dependency: D-data,V-view,I-indirect 
-- 
SELECT DBFFIL AS Physical_file,
       DBFLIB as Physical_file_lib, 
       DBFFDP as Logical_file, 
       DBFLDP AS LogLib_file_lib 
       ,i.*
FROM QSYS.QADBFDEP i
WHERE dbflib <> dbfldp 
and DBFLIB  not in('QSYS');

------------------------------
Bryan Dietz

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

Jerry,

you have to reduce the SYSTABLES results to PFs and TABLEs:

systables.table_type in ('P', 'T')

Because other object types might not give the needed 2nd parameter to  RELATED_OBJECTS.

Try that please - it should work.

HTH

Daniel

So tried below SQL query for the same :

"

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
                        )  

"

But i got this error : Message ID SQ20484

Message: Parameter 2 required for routine RELATED_OBJECTES in SYSTOOLS.

Cause: A Call statement for routine RELATED_OBJECTS in schema SYSTOOLS specified with a named parameter is not valid. Parameter 2 must be specified on the call statment because it has no default value.

Recovery:  Specify a value for parameter 2.  Try the request again.

So what should be the coorected SQL query here now?

Thanks ...

If you want to loop through all libraries, just remove the WHERE Condition ... but do not complain about the query is running too long

in this example also (as per post no. 5) under where systables.system_table_schema = '...your-lib...'  we have to provide libary name manually which i want to avoid , so that when we run this sql query system automatically searches all such libraries and give us such LFs list .

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 10:19 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

I am getting this error for post no. 19 

Message ID : SQL0199

Message: "Keyword WHERE not expected. valid tokens: <> = <> <=  !<

Cause : The keyword WHERE was not expected here. A sysntax error was detected at keyword WHERE.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:35 AM
From: Daniel Gross
Subject: LFs pointing to PFs from incorrect libraries

Jerry - no, you don't need to supply a library in my example (post #15) - just try this:

select systables.system_table_schema, systables.system_table_name,
       related_objects.library_name, related_objects.system_name, related_objects.sql_object_type
from qsys2.systables as systables
join lateral (
     select *
     from table(
          systools.related_objects(systables.system_table_schema, systables.system_table_name)
     )
) as related_objects on true
where systables.table_type in ('P', 'T')
  and systables.system_table_schema <> related_objects.library_name;

But be prepared, that this might run a while - depending on your machine.

HTH

Daniel

------------------------------
Daniel Gross
#IBMChampion
Senior Core Developer, Geis Group
Pegnitz, Germany
https://blog.qpgmr.de/

Original Message:
Sent: Mon February 17, 2025 09:30 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

it's giving me an error "Not authorized to object QADBFDEP in QSYS type *FILE"  also in advice post no. 5 and 15 we need to supply library name manually which i want to avoid and want SQL query to track and list down all such LFs without providing library name there manually.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:19 AM
From: Bryan Dietz
Subject: LFs pointing to PFs from incorrect libraries

not sure where i got this one:
--  description: _logical-not-same-lib-as-pf.sql
-- DBFTDP =  Dependency: D-data,V-view,I-indirect 
-- 
SELECT DBFFIL AS Physical_file,
       DBFLIB as Physical_file_lib, 
       DBFFDP as Logical_file, 
       DBFLDP AS LogLib_file_lib 
       ,i.*
FROM QSYS.QADBFDEP i
WHERE dbflib <> dbfldp 
and DBFLIB  not in('QSYS');

------------------------------
Bryan Dietz

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

Could we please  have corrected complete  SQL query here ?

Thanks...

Jerry,

you have to reduce the SYSTABLES results to PFs and TABLEs:

systables.table_type in ('P', 'T')

Because other object types might not give the needed 2nd parameter to  RELATED_OBJECTS.

Try that please - it should work.

HTH

Daniel

So tried below SQL query for the same :

"

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
                        )  

"

But i got this error : Message ID SQ20484

Message: Parameter 2 required for routine RELATED_OBJECTES in SYSTOOLS.

Cause: A Call statement for routine RELATED_OBJECTS in schema SYSTOOLS specified with a named parameter is not valid. Parameter 2 must be specified on the call statment because it has no default value.

Recovery:  Specify a value for parameter 2.  Try the request again.

So what should be the coorected SQL query here now?

Thanks ...

If you want to loop through all libraries, just remove the WHERE Condition ... but do not complain about the query is running too long

in this example also (as per post no. 5) under where systables.system_table_schema = '...your-lib...'  we have to provide libary name manually which i want to avoid , so that when we run this sql query system automatically searches all such libraries and give us such LFs list .

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 10:19 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

I am getting this error for post no. 19 

Message ID : SQL0199

Message: "Keyword WHERE not expected. valid tokens: <> = <> <=  !<

Cause : The keyword WHERE was not expected here. A sysntax error was detected at keyword WHERE.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:35 AM
From: Daniel Gross
Subject: LFs pointing to PFs from incorrect libraries

Jerry - no, you don't need to supply a library in my example (post #15) - just try this:

select systables.system_table_schema, systables.system_table_name,
       related_objects.library_name, related_objects.system_name, related_objects.sql_object_type
from qsys2.systables as systables
join lateral (
     select *
     from table(
          systools.related_objects(systables.system_table_schema, systables.system_table_name)
     )
) as related_objects on true
where systables.table_type in ('P', 'T')
  and systables.system_table_schema <> related_objects.library_name;

But be prepared, that this might run a while - depending on your machine.

HTH

Daniel

------------------------------
Daniel Gross
#IBMChampion
Senior Core Developer, Geis Group
Pegnitz, Germany
https://blog.qpgmr.de/

Original Message:
Sent: Mon February 17, 2025 09:30 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

it's giving me an error "Not authorized to object QADBFDEP in QSYS type *FILE"  also in advice post no. 5 and 15 we need to supply library name manually which i want to avoid and want SQL query to track and list down all such LFs without providing library name there manually.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:19 AM
From: Bryan Dietz
Subject: LFs pointing to PFs from incorrect libraries

not sure where i got this one:
--  description: _logical-not-same-lib-as-pf.sql
-- DBFTDP =  Dependency: D-data,V-view,I-indirect 
-- 
SELECT DBFFIL AS Physical_file,
       DBFLIB as Physical_file_lib, 
       DBFFDP as Logical_file, 
       DBFLDP AS LogLib_file_lib 
       ,i.*
FROM QSYS.QADBFDEP i
WHERE dbflib <> dbfldp 
and DBFLIB  not in('QSYS');

------------------------------
Bryan Dietz

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

#19 should work the way you described. 

Could we please  have corrected complete  SQL query here ?

Thanks...

Jerry,

you have to reduce the SYSTABLES results to PFs and TABLEs:

systables.table_type in ('P', 'T')

Because other object types might not give the needed 2nd parameter to  RELATED_OBJECTS.

Try that please - it should work.

HTH

Daniel

So tried below SQL query for the same :

"

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
                        )  

"

But i got this error : Message ID SQ20484

Message: Parameter 2 required for routine RELATED_OBJECTES in SYSTOOLS.

Cause: A Call statement for routine RELATED_OBJECTS in schema SYSTOOLS specified with a named parameter is not valid. Parameter 2 must be specified on the call statment because it has no default value.

Recovery:  Specify a value for parameter 2.  Try the request again.

So what should be the coorected SQL query here now?

Thanks ...

If you want to loop through all libraries, just remove the WHERE Condition ... but do not complain about the query is running too long

in this example also (as per post no. 5) under where systables.system_table_schema = '...your-lib...'  we have to provide libary name manually which i want to avoid , so that when we run this sql query system automatically searches all such libraries and give us such LFs list .

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 10:19 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

I am getting this error for post no. 19 

Message ID : SQL0199

Message: "Keyword WHERE not expected. valid tokens: <> = <> <=  !<

Cause : The keyword WHERE was not expected here. A sysntax error was detected at keyword WHERE.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:35 AM
From: Daniel Gross
Subject: LFs pointing to PFs from incorrect libraries

Jerry - no, you don't need to supply a library in my example (post #15) - just try this:

select systables.system_table_schema, systables.system_table_name,
       related_objects.library_name, related_objects.system_name, related_objects.sql_object_type
from qsys2.systables as systables
join lateral (
     select *
     from table(
          systools.related_objects(systables.system_table_schema, systables.system_table_name)
     )
) as related_objects on true
where systables.table_type in ('P', 'T')
  and systables.system_table_schema <> related_objects.library_name;

But be prepared, that this might run a while - depending on your machine.

HTH

Daniel

------------------------------
Daniel Gross
#IBMChampion
Senior Core Developer, Geis Group
Pegnitz, Germany
https://blog.qpgmr.de/

Original Message:
Sent: Mon February 17, 2025 09:30 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

it's giving me an error "Not authorized to object QADBFDEP in QSYS type *FILE"  also in advice post no. 5 and 15 we need to supply library name manually which i want to avoid and want SQL query to track and list down all such LFs without providing library name there manually.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:19 AM
From: Bryan Dietz
Subject: LFs pointing to PFs from incorrect libraries

not sure where i got this one:
--  description: _logical-not-same-lib-as-pf.sql
-- DBFTDP =  Dependency: D-data,V-view,I-indirect 
-- 
SELECT DBFFIL AS Physical_file,
       DBFLIB as Physical_file_lib, 
       DBFFDP as Logical_file, 
       DBFLDP AS LogLib_file_lib 
       ,i.*
FROM QSYS.QADBFDEP i
WHERE dbflib <> dbfldp 
and DBFLIB  not in('QSYS');

------------------------------
Bryan Dietz

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

for that i already replied in #20 what error i got and same error for below SQL as well where i have trimmed this SQL as per advice here:

"select systables.system_table_schema, systables.system_table_name,
       related_objects.library_name, related_objects.system_name, related_objects.sql_object_type
from qsys2.systables as systables
join lateral (
     select *
     from table(
          systools.related_objects(systables.system_table_schema, systables.system_table_name)
     )
) as related_objects on true
where systables.table_type in ('P', 'T')

"

Same error--- Message ID SQL0199

Message : Keyword Where not expected......

Thanks...

#19 should work the way you described. 

Could we please  have corrected complete  SQL query here ?

Thanks...

Jerry,

you have to reduce the SYSTABLES results to PFs and TABLEs:

systables.table_type in ('P', 'T')

Because other object types might not give the needed 2nd parameter to  RELATED_OBJECTS.

Try that please - it should work.

HTH

Daniel

So tried below SQL query for the same :

"

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
                        )  

"

But i got this error : Message ID SQ20484

Message: Parameter 2 required for routine RELATED_OBJECTES in SYSTOOLS.

Cause: A Call statement for routine RELATED_OBJECTS in schema SYSTOOLS specified with a named parameter is not valid. Parameter 2 must be specified on the call statment because it has no default value.

Recovery:  Specify a value for parameter 2.  Try the request again.

So what should be the coorected SQL query here now?

Thanks ...

If you want to loop through all libraries, just remove the WHERE Condition ... but do not complain about the query is running too long

in this example also (as per post no. 5) under where systables.system_table_schema = '...your-lib...'  we have to provide libary name manually which i want to avoid , so that when we run this sql query system automatically searches all such libraries and give us such LFs list .

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 10:19 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

I am getting this error for post no. 19 

Message ID : SQL0199

Message: "Keyword WHERE not expected. valid tokens: <> = <> <=  !<

Cause : The keyword WHERE was not expected here. A sysntax error was detected at keyword WHERE.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:35 AM
From: Daniel Gross
Subject: LFs pointing to PFs from incorrect libraries

Jerry - no, you don't need to supply a library in my example (post #15) - just try this:

select systables.system_table_schema, systables.system_table_name,
       related_objects.library_name, related_objects.system_name, related_objects.sql_object_type
from qsys2.systables as systables
join lateral (
     select *
     from table(
          systools.related_objects(systables.system_table_schema, systables.system_table_name)
     )
) as related_objects on true
where systables.table_type in ('P', 'T')
  and systables.system_table_schema <> related_objects.library_name;

But be prepared, that this might run a while - depending on your machine.

HTH

Daniel

------------------------------
Daniel Gross
#IBMChampion
Senior Core Developer, Geis Group
Pegnitz, Germany
https://blog.qpgmr.de/

Original Message:
Sent: Mon February 17, 2025 09:30 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

it's giving me an error "Not authorized to object QADBFDEP in QSYS type *FILE"  also in advice post no. 5 and 15 we need to supply library name manually which i want to avoid and want SQL query to track and list down all such LFs without providing library name there manually.

Thanks..

------------------------------
jerry ven

Original Message:
Sent: Mon February 17, 2025 09:19 AM
From: Bryan Dietz
Subject: LFs pointing to PFs from incorrect libraries

not sure where i got this one:
--  description: _logical-not-same-lib-as-pf.sql
-- DBFTDP =  Dependency: D-data,V-view,I-indirect 
-- 
SELECT DBFFIL AS Physical_file,
       DBFLIB as Physical_file_lib, 
       DBFFDP as Logical_file, 
       DBFLDP AS LogLib_file_lib 
       ,i.*
FROM QSYS.QADBFDEP i
WHERE dbflib <> dbfldp 
and DBFLIB  not in('QSYS');

------------------------------
Bryan Dietz

Original Message:
Sent: Fri February 14, 2025 03:55 AM
From: jerry ven
Subject: LFs pointing to PFs from incorrect libraries

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

Jerry - no, you don't need to supply a library in my example (post #15) - just try this:

select systables.system_table_schema, systables.system_table_name,
       related_objects.library_name, related_objects.system_name, related_objects.sql_object_type
from qsys2.systables as systables
join lateral (
     select *
     from table(
          systools.related_objects(systables.system_table_schema, systables.system_table_name)
     )
) as related_objects on true
where systables.table_type in ('P', 'T')
  and systables.system_table_schema <> related_objects.library_name;

But be prepared, that this might run a while - depending on your machine.

HTH

Daniel

it's giving me an error "Not authorized to object QADBFDEP in QSYS type *FILE"  also in advice post no. 5 and 15 we need to supply library name manually which i want to avoid and want SQL query to track and list down all such LFs without providing library name there manually.

Thanks..

not sure where i got this one:
--  description: _logical-not-same-lib-as-pf.sql
-- DBFTDP =  Dependency: D-data,V-view,I-indirect 
-- 
SELECT DBFFIL AS Physical_file,
       DBFLIB as Physical_file_lib, 
       DBFFDP as Logical_file, 
       DBFLDP AS LogLib_file_lib 
       ,i.*
FROM QSYS.QADBFDEP i
WHERE dbflib <> dbfldp 
and DBFLIB  not in('QSYS');

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...

This is exactly my example what I posted earlier, not sure why he did not try it

Jerry - no, you don't need to supply a library in my example (post #15) - just try this:

select systables.system_table_schema, systables.system_table_name,
       related_objects.library_name, related_objects.system_name, related_objects.sql_object_type
from qsys2.systables as systables
join lateral (
     select *
     from table(
          systools.related_objects(systables.system_table_schema, systables.system_table_name)
     )
) as related_objects on true
where systables.table_type in ('P', 'T')
  and systables.system_table_schema <> related_objects.library_name;

But be prepared, that this might run a while - depending on your machine.

HTH

Daniel

it's giving me an error "Not authorized to object QADBFDEP in QSYS type *FILE"  also in advice post no. 5 and 15 we need to supply library name manually which i want to avoid and want SQL query to track and list down all such LFs without providing library name there manually.

Thanks..

not sure where i got this one:
--  description: _logical-not-same-lib-as-pf.sql
-- DBFTDP =  Dependency: D-data,V-view,I-indirect 
-- 
SELECT DBFFIL AS Physical_file,
       DBFLIB as Physical_file_lib, 
       DBFFDP as Logical_file, 
       DBFLDP AS LogLib_file_lib 
       ,i.*
FROM QSYS.QADBFDEP i
WHERE dbflib <> dbfldp 
and DBFLIB  not in('QSYS');

Hi,

I need to extract all such LFs which are created on PFs (which are created in incorrect libraries)

for example LF1 which is there in Lib1 and has been created on PF1 in Lib2.

So any SQL query to extract names of all such LFs ?

Thanks...