Re: udf works fine when called from scripts but not within loop of data

[Steve Richter <stephenrichter@xxxxxxxxx>]

yeah, I would strip the function to basics, eliminate the regexp, get rid
of the fenced and parallel stuff.  Get something that works and then work
your way back to coding the function the way you want, looking for the
error to reappear.

CREATE OR REPLACE FUNCTION getMiddleName    (
inFullName  char(256))
RETURNS     char(30)
language sql
BEGIN

declare     fx int default 0 ;
declare     ix int default 0 ;
declare     vSqlCode decimal(5,0) ;
declare     sqlCode int ;
declare     wk varchar(256) default '' ;
declare     vFirstName varchar(256) default '' ;
declare     vMiddleName varchar(256) default '' ;
declare     vLastName   varchar(256) default '' ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET         VSQLCODE = SQLCODE ;

set         wk = trim(inFullName) ;
set         fx = locate(' ', wk ) ;
if          fx > 0 then
set         vFirstName = substr(wk,1,fx-1) ;
set         wk = trim(substr(wk,fx)) ;
else
set         vFirstName = wk ;
set         wk = '' ;
end if;

/* something after first name. check for middle name or last name. */
if          wk <> '' then
set         fx = locate(' ', wk ) ;
if          fx > 0 then
set         vMiddleName = substr(wk,1,fx-1) ;
set         vLastName = trim(substr(wk,fx)) ;
else
set         vLastName = wk ;
end if;
end if ;

return        vMiddleName ;

END


On Fri, Mar 27, 2020 at 9:13 AM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

> so i have a udf that takes a string and parses out firs middle last name...
>
> it uses substring, regexp_count, and locate....
>
> ddl is...
>
> language sql
> allow parallel
> not fenced
> returns null on null input
> MODIFIES sql data
>
> when i call it from a loop feeding it source strings it will work for some
> calls then just stop working.  the job log shows...
>
> Comparison of selection operands may never be equal.
> Comparison of selection operands may never be equal.
> Tried to refer to all or part of an object that no longer exists.
> Directory created.
> Function error X'1720' in machine instruction. Internal dump identifier
>   (ID) 01000E00.
> Failure for device or member MCH0007 file SYSDUMMY1 in library SYSIBM.
> Resource limit exceeded.
> User-defined function error on member QSQPTABL.
> User-defined function error on member QSQPTABL.
> Resource limit exceeded.
>
>
> hoping i'm simply missing something in my ddl.
>
> does anything pop out?  I can provide the code if necessary...
>
> tia
>
> jay
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com