Here is some simple code that we use to do a mass replace of text strings in physical file source members. It does a case sensitive search and replaces the entire search string with the replacement sting and will always produce data shift if the search and replacement strings are of different lengths.
The search and replace is performed with a few simple lines of SQL embedded within some equally simple ILE RPG code.
Here is the command source that is used to prompt the ILE RPG program:
CMD PROMPT('Mass replace in Source Members')
PARM KWD(SOURCE) TYPE(SOURCE) MIN(1) +
PROMPT('Source File eg QRPGLESRC')
SOURCE: QUAL TYPE(*NAME) LEN(10) MIN(1)
QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) +
SPCVAL((*LIBL) (*CURLIB)) PROMPT('Library')
PARM KWD(FROMSTR) TYPE(*CHAR) LEN(130) RSTD(*NO) +
MIN(1) ALWUNPRT(*NO) FULL(*NO) +
CASE(*MIXED) PROMPT('Find String')
PARM KWD(TOSTR) TYPE(*CHAR) LEN(130) RSTD(*NO) +
MIN(1) ALWUNPRT(*NO) FULL(*NO) +
CASE(*MIXED) PROMPT('Replacement String')
… and here is the ILE RPG with the embedded SQL that performs the search and replace:
**free
// Example program to mass replace a search string by a replacement string in source members of
// a source physical file eg QRPGLESRC etc. Use with caution. It is advisable to back your
// source up before you perform a mass replace. The SQL used to perform the search has been
// coded to be case sensitive and will cause data shift where the replacement string is longer
// or shorter than the search string. Leading spaces will be included while trailing spaces
// will be trimmed. This logic could be changed with different SQL options.
//---------------------------------------------------------------------------------------------//
// All source code included in this document are provided on an "AS IS" basis without warranty //
// of any kind. NOVAGEM WILL NOT BE LIABLE FOR ANY ACTUAL, DIRECT, SPECIAL, INCIDENTAL, OR //
// INDIRECT DAMAGES OR FOR ANY ECONOMIC CONSEQUENTIAL DAMAGES INCLUDING LOST PROFITS OR //
// SAVINGS. //
//---------------------------------------------------------------------------------------------//
// Novagem Ltd
ctl-opt dftactgrp(*NO) option(*NOSHOWCPY:*NODEBUGIO);
dcl-ds qusec;
qusbprv int(10) inz(%size(qusec));
qusbavl int(10) inz(0);
qusei char(7);
*N char(1);
msgdata char(200);
end-ds;
dcl-ds errorCode likeds(qusec);
dcl-s qCmd char(6000);
dcl-s FROMSTRwithPercents char(132);
dcl-s MLFILE char(10);
dcl-s MLLIB char(10);
dcl-s MLNAME char(10);
dcl-s MLSEU2 char(10);
dcl-s msgDta char(3000);
dcl-s msgKey char(4);
dcl-s noOfReplacements int(10);
dcl-s sqlMsg char(2000);
dcl-s sqlStmt char(2000);
dcl-ds srcFNamLib;
srcFNam char(10);
srcFLib char(10);
end-ds;
dcl-pr QMHSNDPM extpgm;
msgID char(7) const;
msgFile char(20) const;
msgDta char(32767) options(*varsize) const;
msgDtaLen int(10) const;
msgType char(10) const;
stackEntry char(10) const;
StackCount int(10) const;
msgKey char(4);
error char(1024) options(*varsize) noopt;
end-pr;
dcl-pr QCMDEXC extpgm;
*n char(6000) options(*varsize) const;
*n packed(15:5) const;
end-pr;
dcl-pi MASSRPL;
SOURCE char(20);
FROMSTR char(130);
TOSTR char(130);
end-pi;
// Create a temporary file and populate it with a list of members contained in the source physical file
srcFNamLib=SOURCE;
qCmd='DSPFD FILE('+%trim(srcFLib)+'/'+%trim(srcFNam)+') TYPE(*MBRLIST) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MRMBRLIST)';
QCMDEXC(qCmd:%Len(%trimr(qCmd)));
// *****
dow 0=0;
// *****
// Retrieve next row from member list table
exec sql SELECT MLFILE,MLLIB,MLNAME,MLSEU2 INTO :MLFILE,:MLLIB,:MLNAME,:MLSEU2 FROM MRMBRLIST FETCH FIRST 1 ROWS ONLY;
if SQLSTATE<>'00000'; // Exit if EOF reached
msgDta=%trim(%char(noOfReplacements))+' replacements completed successfully.';
QMHSNDPM(' ':' ':%trim(msgDta):%len(%trim(msgDta)):'*INFO':'*PGMBDY':1:msgKey:errorCode); // Send completion message
leave;
endif;
// Create alias to point at source file member
exec sql DROP ALIAS QTEMP/NXTMBR;
sqlStmt = 'CREATE ALIAS QTEMP/NXTMBR FOR '+%trim(srcFLib)+'/'+%trim(srcFNam)+' ('+%trim(MLNAME)+')';
exec sql EXECUTE IMMEDIATE :sqlStmt;
if SQLSTATE<>'00000';
msgDta='Unable to create alias on '+%trim(srcFLib)+'/'+%trim(srcFNam)+'.'+%trim(MLNAME);
exsr GetErrorText;
leave;
endif;
// Perform replacement of string in member
FROMSTRwithPercents='%'+%trimr(FROMSTR)+'%';
exec sql UPDATE NXTMBR SET SRCDTA = REPLACE(SRCDTA,TRIM(TRAILING FROM :FROMSTR),TRIM(TRAILING FROM :TOSTR))
WHERE SRCDTA LIKE TRIM(:FROMSTRwithPercents) WITH NC;
if SQLSTATE<>'00000' and // Test for error
SQLSTATE<>'02000';
msgDta='Unable to update mbr '+%trim(srcFLib)+'/'+%trim(srcFNam)+'.'+%trim(MLNAME);
exsr GetErrorText;
leave;
endif;
noOfReplacements=noOfReplacements+SQLERRD(3); // Bump counter for number of members processed
// Delete row from table
exec sql DELETE FROM QTEMP/MRMBRLIST WHERE MLLIB=:MLLIB AND MLFILE=:MLFILE AND MLNAME=:MLNAME WITH NC;
// ***
enddo;
// ***
*INLR=*ON;
return;
// ****************
begsr GetErrorText;
// ****************
// Get error diagnostic info and output msg to screen
exec sql GET DIAGNOSTICS CONDITION 1 :sqlMsg = MESSAGE_TEXT;
msgDta=%trim(msgDta)+': '+sqlMsg;
QMHSNDPM(' ':' ':%trim(msgDta):%len(%trim(msgDta)):'*INFO':'*PGMBDY':1:msgKey:errorCode);
// ***
endsr;
// ***
Walk through
On line 73 we create a list of source members in the chosen source physical in a temporary table in QTEMP using the command DSPFD.
Then on line 81 we read the next record from the previously created temporary table checking for EOF at which point we output the completion message and exit the program.
On line 90 we create an alias in SQL (NXTMBR) which allows SQL to access the particular member retrieved in the row from the table above.
Then on line 100 we perform the search and replace using an SQL update statement which references the aliased member defined on line 90 checking for any error codes and outputting the SQL diagnostics if any errors occur.
The row from the temporary member list table is then deleted on line 111 and the loop is processed again starting with fetching the next row from the table.
We have called the ILE RPG program MASSRPL and named and compiled the command accordingly. As always it’s advisable to backup your source before performing any mass replace operations.