UDF performance issue

[vsramesh]

Hi All,

I am trying to use a UDF for DB2. It is a simple UDF written in C++ which takes 2 strings as input concates them and the result is given as output. I am trying this to measure the performance. My fucntion is defined as

CREATE FUNCTION
CPPUDF(
VARCHAR(40) CCSID EBCDIC,
VARCHAR(40) CCSID EBCDIC,
VARCHAR(40) CCSID EBCDIC )
RETURNS
VARCHAR(80) CCSID EBCDIC
LANGUAGE C
PARAMETER VARCHAR NULTERM
NOT DETERMINISTIC
NO SQL
EXTERNAL NAME CPPUDF
PARAMETER STYLE DB2SQL
PROGRAM TYPE SUB
SCRATCHPAD
FINAL CALL
SECURITY USER
WLM ENVIRONMENT DB9GENV3;

I am invoking the above fuction in a COBOL program and the program just has one select statement with this UDF.

The WLM environment is
//DB9GWLM3 PROC DB2SSN=DB9G,NUMTCB=60,APPLENV=DB9GENV3
//*
//NUMTCB@1 SET NUMTCB=
//*
//DSNTPSMP EXEC PGM=DSNX9WLM,TIME=1440,
// PARM='&DB2SSN,20,&APPLENV',
// REGION=0M,DYNAMNBR=5
//* Include SDSNEXIT to use Secondary Authids (DSN3@AT
//STEPLIB DD DISP=SHR,DSN=DSN910.DB9G.RUNLIB.LOAD

Now I am getting very poor performance with this UDF something like 300 seconds for 10000 iterations of the select statement.

I tried several options but none of them seem to help. Can anyone please throw some light on anything similar they have done or anyhelp in this respect would be highly appreciated.

Thanks a lot in anticipation
Ramesh.