forstie/IFS_search_replace_and_create.sql

## IFS_search_replace_and_create.sql
--
-- Subject: IFS stream file transformation
-- Author: Scott Forstie (thanks to Sue Romano for helping with this Gist)
-- Date  : June 28, 2021
-- Features Used : This Gist uses ifs_read, ifs_write, regexp_replace, and the previously unknown fx designation for a unicode literal.
--
-- Function - Point is routine at an existing IFS steam file, which contains some character data that you want to globally replace.
--            The function extracts (reads) the contents, replaces the search string occurrences with the replacement string, and then
--            writes everything to the target IFS stream file.
--
cl: crtlib coolstuff;

create or replace procedure coolstuff.IFS_search_replace_and_create(
    Source_IFS_file DBCLOB(16M) ccsid 1200,
    Target_IFS_file DBCLOB(16M) ccsid 1200,
    search_string  CLOB(100) ccsid 1208,
    replace_string CLOB(100) ccsid 1208
    )
begin
  declare a_line CLOB(2G) ccsid 1208;
  declare not_found condition for '02000';
  declare at_end integer default 0;
  declare local_sqlcode integer;
  declare local_sqlstate char(5);
  declare v_message_text varchar(70) for sbcs data;

  declare ifs_cursor_stmt_text clob(10k) ccsid 37;
  declare ifs_cursor cursor  with hold for ifs_cursor_stmt;

  declare continue handler for sqlexception
    begin
    get diagnostics condition 1
        local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate;
    set v_message_text = 'coolstuff.IFS_search_replace_and_create() failed with: ' concat local_sqlcode concat
          '  AND ' concat local_sqlstate;
    signal sqlstate 'QZZ01'
      set message_text = v_message_text;
    set at_end = 1;
  end;
  declare continue handler for not_found set at_end = 1;

  set ifs_cursor_stmt_text = 'select regexp_replace(line, fx''' concat hex(search_string)
                                              concat ''', fx''' concat hex(replace_string) concat ''') from
      table(QSYS2.IFS_READ_UTF8(?, END_OF_LINE => ''NONE'')
    )' ;
  -- In case debug is needed...
  -- call systools.lprintf('ifs_cursor_stmt_text: ' concat ifs_cursor_stmt_text);

  --
  -- Remove the target IFS stream file, should it exist
  --
  begin
    declare ignore_error integer default 0;
    declare continue handler for sqlexception set ignore_error = 1;
    call qsys2.qcmdexc('RMVLNK OBJLNK(''' concat Target_IFS_file concat ''')');
  end;

  prepare ifs_cursor_stmt from ifs_cursor_stmt_text;
  open ifs_cursor using Source_IFS_file;
  --
  -- Each fetch will bring back up to 2GB of data
  -- An IFS stream file could be as large as 1TB, so we need to loop until all the data is processed
  --
  fetch from ifs_cursor into a_line;
  while (at_end = 0) do
    CALL QSYS2.IFS_WRITE_UTF8(Target_IFS_file, a_line, END_OF_LINE => 'NONE');
    fetch from ifs_cursor into a_line;
  end while;
  close ifs_cursor;
end;
stop;

--
-- Example usage:
--

call coolstuff.IFS_search_replace_and_create(Source_IFS_file => '/home/coolstuff/ainput.txt',
                      Target_IFS_file => '/home/coolstuff/aoutput.txt',
                      search_string   => 'blah',
                      replace_string  => '客');
stop;

select *
  from table (
      QSYS2.IFS_READ_UTF8('/home/coolstuff/aoutput.txt')
    );
	--
	-- Subject: IFS stream file transformation
	-- Author: Scott Forstie (thanks to Sue Romano for helping with this Gist)
	-- Date : June 28, 2021
	-- Features Used : This Gist uses ifs_read, ifs_write, regexp_replace, and the previously unknown fx designation for a unicode literal.
	--
	-- Function - Point is routine at an existing IFS steam file, which contains some character data that you want to globally replace.
	-- The function extracts (reads) the contents, replaces the search string occurrences with the replacement string, and then
	-- writes everything to the target IFS stream file.
	--
	cl: crtlib coolstuff;

	create or replace procedure coolstuff.IFS_search_replace_and_create(
	Source_IFS_file DBCLOB(16M) ccsid 1200,
	Target_IFS_file DBCLOB(16M) ccsid 1200,
	search_string CLOB(100) ccsid 1208,
	replace_string CLOB(100) ccsid 1208
	)
	begin
	declare a_line CLOB(2G) ccsid 1208;
	declare not_found condition for '02000';
	declare at_end integer default 0;
	declare local_sqlcode integer;
	declare local_sqlstate char(5);
	declare v_message_text varchar(70) for sbcs data;

	declare ifs_cursor_stmt_text clob(10k) ccsid 37;
	declare ifs_cursor cursor with hold for ifs_cursor_stmt;

	declare continue handler for sqlexception
	begin
	get diagnostics condition 1
	local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate;
	set v_message_text = 'coolstuff.IFS_search_replace_and_create() failed with: ' concat local_sqlcode concat
	' AND ' concat local_sqlstate;
	signal sqlstate 'QZZ01'
	set message_text = v_message_text;
	set at_end = 1;
	end;
	declare continue handler for not_found set at_end = 1;

	set ifs_cursor_stmt_text = 'select regexp_replace(line, fx''' concat hex(search_string)
	concat ''', fx''' concat hex(replace_string) concat ''') from
	table(QSYS2.IFS_READ_UTF8(?, END_OF_LINE => ''NONE'')
	)' ;
	-- In case debug is needed...
	-- call systools.lprintf('ifs_cursor_stmt_text: ' concat ifs_cursor_stmt_text);

	--
	-- Remove the target IFS stream file, should it exist
	--
	begin
	declare ignore_error integer default 0;
	declare continue handler for sqlexception set ignore_error = 1;
	call qsys2.qcmdexc('RMVLNK OBJLNK(''' concat Target_IFS_file concat ''')');
	end;

	prepare ifs_cursor_stmt from ifs_cursor_stmt_text;
	open ifs_cursor using Source_IFS_file;
	--
	-- Each fetch will bring back up to 2GB of data
	-- An IFS stream file could be as large as 1TB, so we need to loop until all the data is processed
	--
	fetch from ifs_cursor into a_line;
	while (at_end = 0) do
	CALL QSYS2.IFS_WRITE_UTF8(Target_IFS_file, a_line, END_OF_LINE => 'NONE');
	fetch from ifs_cursor into a_line;
	end while;
	close ifs_cursor;
	end;
	stop;

	--
	-- Example usage:
	--

	call coolstuff.IFS_search_replace_and_create(Source_IFS_file => '/home/coolstuff/ainput.txt',
	Target_IFS_file => '/home/coolstuff/aoutput.txt',
	search_string => 'blah',
	replace_string => '客');
	stop;

	select *
	from table (
	QSYS2.IFS_READ_UTF8('/home/coolstuff/aoutput.txt')
	);