Hello,
i have a little performance problem with DB2 UDF:

i've created 2 UDFs:
Code:
EAN_128(EanCodeReq VARCHAR(4), Id bigint) returns VARCHAR(32)
and
Code:
EAN_128_CHECKSUM(EanCode VARCHAR(32)) RETURNS CHAR(1)
EAN_128 simple select into table (< 100 lines), fetches varchar value and then adds checksum to this value. Checksum is calculated by calling EAN_128_CHECKSUM (simple while loop, some multiplications and additions)

My problem: EAN_128 is toooooo sloooow, or better said, calling EAN_128 by "select EAN_128(....) from dummy" is slow. I've found out, that the body of function (select, some ifs and checsum calculation) tooks < 0.05 sec, but calling is expensive, it takes more than 1.5 sec.

To clarify the background:
EAN_128 is called from our ERP (java/j2ee) each time employee in warehouse scans palette.
After scan, label with barcode is printed, and this print (in fact, some java stuff+ean_128 generation) takes too long time.

Im java developer, so sorry for my primer, but it seems to me like DB2 "compiles" this function each time it's called. And i've also find out, that more lines with call of another UDF is put into EAN_128, the more time first call takes.

ie,
if i put only this line into EAN_128:
Code:
SET ret_ = '(00)' || strData || EAN_128_CHECKSUM(strData);
call takes let's say 0.25s

but if i put into EAN_128 this:
Code:
if (...) then
  SET ret_ = '(00)' || strData || EAN_128_CHECKSUM(strData);
else if (...)
 SET ret_ = '(01)' || strData||   EAN_128_CHECKSUM(strData);
else if (...)
 SET ret_ = '(03)' || strData || EAN_128_CHECKSUM(strData);
end if;
call takes 1.4 sec

To sum up:
-EAN_128 is called only once per cca 20-30 seconds, from java code
-from my point of view it seems calling UDF within another UDF significantly slows down UDF call

Has anobody experienced this behaviour or i've totally missed something?