I have a UDB function which is currently returning values within a select statement. No problem until I was asked to enhance this. The UDF selects from a table using a value passed in. If a value is found the corresponding field is returned. If the value is not found - the same value is returned.

I need to enhance this - when the value coming in is not found - I need to add it to an "empty slot" which is set up ahead of time. I will then create a new empty slot - for use later with this incoming value.

example - underlying table has
col1 col2
A B
C
you call the UDF with A as the parameter and B is returned. If you call with X - then X is not found.
The enhancement would "partner" X with C via and update statement. Then it would insert X without any value in col1 ..... and so on
The result would be
col1 col2
A B
X C
X

I don't know of a way to perform inserts/updates within a function. If there is a way I would like to know how. Otherwise I think I need to convert this into a stored procedure. Which I have done - however - how do I convert the existing SQL to work using the SP instead of the UDF ??

You can do data modifications in table UDFs.

Aside from that, a conversion to a stored procedure is pretty much straight forward. The major difference between both is how you use a UDF vs. an SP, i.e. in a query or insert/update/delete statement vs. the CALL statement.

I changed my UDF to a table funtion.
However I can no longer call this UDF using
"select scramb.scrambfn('MICHAEL',0) from sysibm.sysdummy1"
I must now use

"select ta.*,tb.* from table ( scramb.scrambfnt('MICHAEL',0)) as ta "
This is ok - EXCEPT -
I need to call this multiple times
something like this :
$=> db2 "select ta.*,tb.* from table ( scramb.scrambfnt('MICHAEL',0)) as ta ,table ( scramb.scrambfnt('JOHN',0)) as tb"

However I get his error when I do.
SQL20267N The function "SCRAMB.SCRAMBFNT" (specific "SQL090610150002100")
modifies SQL data and is invoked in an illegal context. Reason code = "1".
SQLSTATE=429BL

Any suggestions?

Did you include the MODIFIES SQL DATA statement in the declaration? Plus, I'd change that join to a union.

Yes I did include modifies sql data. This works if I call it once - works great.

This works
select ta.*,' ' from table ( scramb.scrambfnt('MICHAEL',0)) as ta
;
select ' ',tb.* from table ( scramb.scrambfnt('JOHN',0)) as tb
;

This does not work :
select given_name_one ,ta.* from nucleus.personname
,table ( scramb.scrambfnt(GIVEN_name_one,0)) as ta
where person_name_id = 10001
;

If "does not work" means getting syntax error, you can try:
select given_name_one ,ta.*
from nucleus.personname as pn
,table ( scramb.scrambfnt(pn.GIVEN_name_one,0)) as ta
where person_name_id = 10001
;