Tweet
I wrote this script to search RPG code for a string. I find it more helpful than FNDSTRPDM. Hope someone else can benefit.
Thanks to those a few months ago who helped me with some questions I had that enabled me to get this working. I'm open to comments and constructive criticism.
Can be copied & modified to use with CL or DDS
MODIFIED to allow multiple Source File Library searches.
Thanks to those a few months ago who helped me with some questions I had that enabled me to get this working. I'm open to comments and constructive criticism.
Can be copied & modified to use with CL or DDS
MODIFIED to allow multiple Source File Library searches.
Code:
----==================================================
----=================== RPG SEARCH ====================
----==================================================
--- Run the whole block at once. Takes about 30 seconds on my system
--- Create TEMP File for Source member names to search , & File of Results
----- RPG Source Member Names that will be searched
CREATE OR REPLACE TABLE QTEMP.RPG_SOURCE (
ROW# BIGINT NOT NULL ,
TABLE_SCHEMA FOR COLUMN TABSCHEMA VARCHAR(128) ALLOCATE(10) CCSID 37 NOT NULL ,
TABLE_NAME FOR COLUMN TABNAME VARCHAR(128) ALLOCATE(18) CCSID 37 NOT NULL ,
TABLE_PARTITION FOR COLUMN TABPART VARCHAR(128) ALLOCATE(100) CCSID 37 NOT NULL ,
COMPLETED VARCHAR(1) ALLOCATE(1) CCSID 37 NOT NULL,
PARTITION_TEXT FOR COLUMN TABTEXT VARCHAR(100) ALLOCATE(100) CCSID 37 ) ON REPLACE DELETE ROWS RCDFMT RPG_SOURCE ;
CREATE OR REPLACE TABLE QTEMP.SRC_SEARCH (
SRCSEQ NUMERIC(6, 2) NOT NULL ,
SRCDTA CHAR(100) CCSID 37 NOT NULL ,
SRCHSTRING VARCHAR(30) CCSID 37 NOT NULL ,
SRC_MEMBER VARCHAR(12) CCSID 37 NOT NULL ,
SRC_TEXT VARCHAR(100) CCSID 37,
SRCDAT NUMERIC(6,0) ,
SRCYYYYMMDD numeric(8, 0) ,
SRCLIB VARCHAR(20) ) ON REPLACE DELETE ROWS RCDFMT SRC_SEARCH ;
INSERT INTO QTEMP.RPG_SOURCE ----- Load RPG Source Member Names into File RPG_SOURCE
select *
from (select ROW_NUMBER() OVER () row#, TABLE_SCHEMA , TABLE_NAME , TABLE_PARTITION , ' ' as Completed , PARTITION_TEXT
FROM QSYS2.SYSPARTITIONSTAT
WHERE TABLE_NAME LIKE 'QRPG%' AND TABLE_SCHEMA in ( 'SRCLIB_1' , 'SRCLIB_2' )--- <<---- SEARCH SOURCE LIBRARY & FILE QDDS, QCL, QRPG..... LXERPSRC Prod Source
)
ORDER by TABLE_PARTITION ;
---==================
begin
declare v_Lib char(50);
declare v_Table char(50);
declare v_member char(50);
declare SQLState char(5);
declare v_Cmd varchar(256);
declare Srch_String varchar(25);
declare Srch_Wildcard varchar(27);
declare v_Text varchar(100);
declare SourceCode cursor for
select Table_Schema , Table_Name , Table_Partition , PARTITION_TEXT --- Library , Src File Name , Member Name
FROM QTEMP.RPG_SOURCE ;
open SourceCode ;
SET Srch_String = 'MySearchString' ; --- <<<<========= SEARCH STRING
SET Srch_Wildcard = '%' concat UPPER(Srch_String) concat '%' ; --- Puts % before and after for LIKE statement.
FetchLoop:
loop
fetch SourceCode into v_Lib , v_Table, v_member , v_Text ;
if SQLState >= '02000' then
leave FetchLoop;
end if;
set v_Cmd = 'CREATE ALIAS QTEMP.MYMBRB FOR ' concat v_Lib concat '.' concat v_Table concat ' (' concat v_member concat ')';
execute immediate v_Cmd;
INSERT INTO QTEMP.SRC_SEARCH SELECT SRCSEQ , SRCDTA , Srch_String , v_member , v_Text, SRCDAT,
CASE WHEN SRCDAT < 700000 THEN (20000000 + SRCDAT) ELSE (19000000 + SRCDAT) END,
v_lib
FROM QTEMP.MYMBRB where UPPER(SRCDTA) like Srch_Wildcard ; --- '%' concat Srch_String concat '%' ------'%SUBSTR%' ;
DROP ALIAS QTEMP.MYMBRB ;
end loop;
close SourceCode;
end;
;
SELECT SRCSEQ , SRCDTA ,
SUBSTR(SRCYYYYMMDD,1,4) || '-' || SUBSTR(SRCYYYYMMDD,5,2) || '-' || SUBSTR(SRCYYYYMMDD,7,2) as SRC_DATE ,
SRCHSTRING , SRC_MEMBER ,SRCLIB , SRC_TEXT --*
FROM QTEMP.SRC_SEARCH ---- Results. . Can be run manually afterwards with modified WHERE to fine-tune results.
WHERE SUBSTR(SRCDTA,7,1) <> '*' --- Omit Comment lines with * in pos 7
--- and UPPER(SRCDTA) like '%ABCDE%' --- Additional search string
ORDER BY SRCLIB , SRC_MEMBER , SRCSEQ
---- Run up to here
;==;
--- Unique PGM Names only. Run separately
SELECT DISTINCT SRCHSTRING ,SRC_MEMBER , ' ' , SRC_TEXT
FROM QTEMP.SRC_SEARCH ---- Results. . Can be run manually with modified WHERE to fine-tune results.
WHERE SUBSTR(SRCDTA,7,1) <> '*' --- Omit Comment lines with * in pos 7
ORDER BY SRCLIB , SRC_MEMBER
;==;
Comment