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

Really useful, thankyou.

Had to issue a transaction isolation level of “read uncommitted” to enable GET_CLOB_FROM_FILE.

Get_CLOB_From_File returns a CLOB locator.
And when working with LOB locators commitment control is required.
If forgot it to mention because I always work under commitment control.

Hi Birgitta,
Can I get an explanation of how to use this for dummies...lol

I have been spending more time learning the database structure and how to interact with it. I am very familiar with using SQL on the system. When you say Schema does that = library name? Some software refers to Schema as the RDB name.

I tried the following changes and the SQL executes but with no results:
System_Table_Name like 'SRC%'
and System_Table_Schema = 'MATTS'
Where Get_Clob_From_File(OrigClobMbr) like '%FTP%'

I was hoping this would bring back all of the source that referenced FTP in it. Am I totally off base with what I am trying to do and what you are saying this does?

Thank you.

Schema and library are (at least on IBM i) identical. Schema is the SQL Name and Library the IBM i name.
Where are your sources located? In Source Files that start with QSRC? If your sources you want to search are located for examples in source files starting with QRPG, you then have to replace QSRC with QRPG.

Hi Birgitta,

Do you have a version that doesn't rely on source files starting with 'QSRC'?
I.E. Search all tables that are type source physical files.

Thank you.

Hi Glenn, I’m not quite sure what you mean. I didn’t specify any source file … and may sources are also not in Source Files starting with QSRC. You can copy and paste the source code in any SQL environment (for example ACS – Run SQL Script) and then run it. Could be it is the compiler that copies the source into a QSQL File in the QTEMP or any other schema. Mit freundlichen Grüßen / Best regards Birgitta Hauser Modernization – Education – Consulting on IBM i "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) From: GlennGundermann ***@***.***> Sent: Freitag, 25. März 2022 16:29 To: BirgittaHauser ***@***.***> Cc: Birgitta Hauser ***@***.***>; Author ***@***.***> Subject: Re: BirgittaHauser/SearchSrcMbr @GlennGundermann commented on this gist.

…

_____ Hi Birgitta, Do you have a version that doesn't rely on source files starting with 'QSRC'? I.E. Search all tables that are type source physical files. Thank you. — Reply to this email directly, view it on GitHub <https://gist.github.com/74b78ed5db37970155b5aacecb824266#gistcomment-4110507> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/AFPAC76QOGX7UV6OTUFRYR3VBXLTHANCNFSM4TOIS7WA> . You are receiving this because you authored the thread. <https://github.com/notifications/beacon/AFPAC74N6LCY7KKTXMEWUTLVBXLTHA5CNFSM4TOIS7WKYY3PNVWWK3TUL52HS4DFVNDWS43UINXW23LFNZ2KUY3PNVWWK3TUL5UWJTQAH24KW.gif> Message ID: ***@***.*** ***@***.***> >

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.