How to search source physical file member for a specific String

SearchSrcMbr

It was just a question in a Forum: How to search (all) source physical file members for a specific string and list all those members
In this examples all source files beginning with "QSRC" in the "YOURSCHEMA" library are searched whether they include "String". 
All Source Members that include "String" are returned

With a as (Select a.System_Table_Schema OrigSchema, 
                  a.System_Table_Name   OrigTable, 
                  a.System_Table_Member OrigMember,
                  Trim(System_Table_Schema) concat '/' concat 
                  Trim(System_Table_Name)   concat '(' concat 
                  Trim(System_Table_Member) concat ')'         as OrigCLOBMbr
             from Syspartitionstat a
             Where     System_Table_Name   like 'QSRC%'
                   and System_Table_Schema =    'YOURSCHEMA')
Select OrigSchema, OrigTable, OrigMember 
   from a
   Where Get_Clob_From_File(OrigClobMbr) like '%String%';   

Hi Birgitta,

Your code above has: Where System_Table_Name like 'QSRC%'
I am asking if instead of looking for specific table names, can you join with SYSTABLES and look for tables where file_type = 'S'.

Glenn

Thanks Birgitta,

FYI - Had an issue whereby it failed to identify part of the object name due to wild card shenanigans and did not filter correctly.

As seen it ignored the underscore in the selection criteria.

The fix (Thanks @zkarj735) was to use this instead.

Where System_Table_Name like 'QCLSRC+_%' escape '+'

Hi Birgitta,

I can't use it if my schema is inside IASP. It return me sqlstate 42704, Is there any circumvention?

(If I try on QGPL it works, and I can do a select on a table into iasp)

Thank you

when i try to run this is ACS Run Sql Scripts, i receive sql0443 state 42927
LOB and XML locators are not allowed with COMMIT(*NONE)

Yes, that's true! When working with Locators (LOB or XML-Locators) you have to run under commitment control. Normally the lowest level *CHG is enough.
Locators are kind of Pointers ... and Pointers have to be freed. ... LOB-Locators or XML-Locators get freed when executing a COMMIT or ROLLBACK statement! So you must use them under commitment control.