SQL Equivalent of FNDSTRPDM, for All Members ?

[MFisher]

I want to run interactive SQL, browsing CL and RPG source code for character strings, using Navigator.

I can get a list of source members with this

SELECT TABLE_PARTITION FROM QSYS2.SYSPARTITIONSTAT
WHERE TABLE_NAME = 'QCLSRC' AND TABLE_SCHEMA = 'MyLib'

And can display 1 source member like this

CREATE ALIAS QTEMP.MYMBRB FOR MyLib.QCLSRC (MySrcMember).

SELECT * FROM QTEMP.MYMBRB

How can I create some interactive SQL code to select from all CL (or RPG) members where SRCDTA is LIKE '%String%' ?

[paolinosal]

Hi Fischer,

I make this function, it is not very fast...

Create or replace Function qgpl/FindString (Lib char(10), pfsrc Char(10), Mbrname varchar(128), SearchString varchar(200) )
  Returns Table (
    Riga Decimal(6,2),
     Sourceline Varchar(200)
  )
 Modifies Sql Data
  Begin

    Declare SqlStatement Varchar(1000);    
    Declare LikeString   Varchar(210);    


    Set SqlStatement = 'Create Or Replace Alias Qtemp/SourceFile For ' concat lib concat '/' concat pfsrc concat '(' concat MbrName concat ')';
    Execute immediate SqlStatement;

    Set Likestring = '%' concat SearchString concat '%';


    Return Select Srcseq, Trim(Srcdta) From SourceFile Where Upper(Srcdta) Like upper(LikeString);

  End;

;


Select Table_Partition, Riga, Sourceline
  From Syspstat, Table (
         QGPL/findstring(Table_Schema, Table_Name, Table_Partition, 'STRINGTOSERCH')
       )
  Where Table_Name = 'QRPGLESRC'
        And Table_Schema = 'QGPL'
  Order By 1;

Bye

[Vectorspace]

Rather than (re)creating alias objects, you could use qsys2/qcmdexc() to run OVRDBF and DLTOVR commands override the table to the desired member?

[B.Hauser]

Here is an example:
https://gist.github.com/BirgittaHaus...b5aacecb824266

In this examples all source members located within the source files beginning with QSRC in the YOURSCHEMA libray are searched.

The source members that include String are returned

[MFisher]

Here is an example:

https://gist.github.com/BirgittaHaus...b5aacecb824266

Hi, this looks like the solution, but I had to specify QSYS2 for syspartionstat, but then get an error, copied from the joblog.




Cause . . . . . : An error occurred while invoking user-defined function GET_CLOB_FROM_FILE in library QSYS2. The error occurred while invoking the associated external program or service program QDBSSUDF2 in library QSYS, program entry point or external name QSQGTCF, specific name GET_CLOB_FROM_FILE_1. The error occurred on member SYSPA00001 file SYSPSTAT in library QSYS2. The error code is 1. The error codes and their meanings follow:
1 -- The external program or service program returned SQLSTATE 42926. The text message returned from the program is: LOB and XML locators are not allowed with COMMIT(*NONE). .
2 -- The external program failed before it completed.
3 -- The database timed out waiting for the program to return. The timeout value used by the database was 0 minutes and 30 seconds.
4 -- The external program no longer exists or is not found.
5 -- One of the input parameters of the function had a data mapping error.
6 through 26 -- See the previous message in the joblog.
For an external program, the program entry point displayed will be *N.

Recovery . . . : For error codes 1 and 2, determine the cause of the error from either the SQLSTATE or a previously listed message.
For error code 3, either increase the timeout limit using the QAQQINI file setting or determine why the external program or service program did not return in the time allotted.
For error code 4, ensure the program or service program exists for the duration of the query.
For error code 5, determine the cause of the data mapping error.
For error codes 6 through 26, see the previous message in the joblog.
Refer to the DB2 for i SQL programming topic collection in the Database category in the IBM i Information Center book, http://www.ibm.com/systems/i/infocenter/ for more information on user-defined functions.

Possible choices for replying to message . . . . . . . . . . . . . . . :
C -- The request is canceled.
I -- The request is ignored.

[MFisher]

I ran this: FYI, We're on V7 R2 M0

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 QSYS2.Syspartitionstat a

             Where     System_Table_Name   like 'QRPGLESRC%'

                   and System_Table_Schema =    'MyLibrary')

Select OrigSchema, OrigTable, OrigMember

   from a

   Where Get_Clob_From_File(OrigClobMbr) like  '%ECL%'  ;

[B.Hauser]

You had to qualify the view, because you are using SQL Naming conventions (and not Sytem Naming conventions), so the library list is not searced.
... as the Error message says, you have to execute this solution under commitment control.

Both (Naming Conventions and Isolation Leven/Commitment control) can/must be set on the connection.

Birgitta

[MFisher]

I can't find Commitment control settings in Navigator properties.
Or do I set it interactively before running a script ?

I don't want to make any changes affecting anyone else.

[B.Hauser]

You have to look for Log Level in your connection.
You may also try to add WITH CS at the end of your SELECT statement.

Are you working with Client Access for Windows or ACS (Access Client Solutions)?

[MFisher]

I am using IBM System I Navigator version 7.1.0

[JonBoy]

Maybe it is just me ... but isn't this a classic example of where SQL is probably not the best tool for the job? iSphere search in RDi or ... is probably going to provide a better answer