-
-
Save forstie/1bdf4ee3662c17e916025ff45bd5f2d0 to your computer and use it in GitHub Desktop.
-- | |
-- 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') | |
); | |
Hi,
I changed the line 27 to 935 and used the RUN SQL Script.
call acndbs6lib.IFS_search_replace_and_create(Source_IFS_file =>
'/admsapps/notifications/templates/WORKORDERCOMPLETION.TEMPLATE',
Target_IFS_file =>
'/admsapps/notifications/WORKORDERCOMPLETION.TEMPLATE',
search_string => '&CONTACTNAME',
replace_string => '客') ;
And here's the error message.
SQL State: QZZ01 Vendor Code: -438 Message:
[SQL0438] ACNDBS6LIB.IFS_search_replace_and_create() failed with: -418 AND 4261.
'/admsapps/notifications/templates/WORKORDERCOMPLETION.TEMPLATE' exists in my IFS Directory
'/admsapps/notifications/WORKORDERCOMPLETION.TEMPLATE' this one does not exist, since it should be created once the script runs successfully.
Thank you
Can you wrapper the CALL with:
STRDBMON / ENDDBMON and query the output for QQRID = 1000 order by QQETIM?
Or... open a service ticket and it might be easier to resolve what's happening.
I would try changing line 27 from CCSID 37 to 935.
I would also try using this from ACS's Run SQL Scripts and not in STRSQL.
For the case that failed, it looks like the source path did not exist.
Lets see what the joblog looks like in Run SQL Scripts, with CCSID 935, and being passed a good source path.