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****NLIB.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.

first off why would you need a udf to concatenate the strings? You could just do that in any select statement like:
select :mystrng1 || :mystrng2 || mystrng3, other_cols
from my_table
where ..........

Also, how many seconds does it take to run 10000 iterations of your select without the UDF? What exactly are you doing within your C++? How long does it take to execute it outside of the UDF?

Dave

Thanks for the response Dave,

Actually concatenating the strings is not the purpose if my UDF. I was soing some complex calculations earlier for which there are no Db2 supported functions. When I saw a very poor performance I wrote this concatenating strings UDF's which basically uses STRCAT function. Outside the UDF it performs like 1 sec for 10000 transactions. When I use the UDF too if I dont use the security USER I get good performance like 2.5 secs for performance but as and when I use Security USER .. I guess WLM estaablishing a RACF security environment is taking all the time. Am not a DBA and not getting a lot of DBA support is really puzzling me what needs to be done.

Thanks
Ramesh.

How are you invoking the UDF? I'd say that the authentication should be done only once on the FIRST call made to the function - assuming you have the 10000 UDF calls in the same SQL statement.

Thanks for the response.

Can you please give me a little detail on how do we check authentication in the first call and not in subsequent calls because when I specify security user DB2 is creating a security environment which is causing a big performance hit. The other thing I am looking at is the LE enclaves we are getting. I wonder if IBM had any documented performance stats so that atleast I know I am getting similar to what they have published.

Thanks
Ramesh.

Sorry,

I forgot to mention that I am invoking the UDF from my COBOL program. I have it as a SELECT X(..,..,..) from SYSIBM.SYSDUMMY1 where X is my UDF.

Thanks
Ramesh.

You are calling the UDF in a query against SYSIBM.SYSDUMMY1? If so, then each invocation of the UDF is independent of any other, so the authentication has to be done again and again. Could you run your UDF against some table that processes the data and, thus, pipe lots of data through the UDF instead of making single calls?

Hi Stolze,

I am getting a getting a better performance when I am running it against a table and the info in the table but at the same time I am trying to acheive a better performance with individual calls and am not able to get any further in that.

Thanks
Ramesh.

What do you expect? The UDF is loaded into your WLM environment when called for the first time. The regular z/OS security checks apply there, of course. If your UDF just does a single evaluation, it is unloaded after that (maybe cached, but still...) You can reap a real benefit only if you stream lots of data through the function, i.e. call it on a set of rows. In this respect, it doesn't even make a difference whether we talk about DB2 z/OS and DB2 LUW. Processing single rows is just a stupid way to work with a DBMS (and UDFs are part of that).