Posted: Wed Oct 01, 2008 2:02 pm Post subject: UDF performance issues.
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
Code:
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
Code:
//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.
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
Posted: Thu Oct 02, 2008 1:38 pm Post subject:
I don't know a lot about UDF's in z/OS, but I have a couple thoughts.
1) Let's start with this; Why are you coding a UDF to concat 2 strings? I hope you're just trying to get all the technical details ironed out and then you'll code the real UDF.
2) Have you determined that the real cause of the performance degradation is due to the UDF? Have you run the SQL replacing the reference to your UDF with a standard CONCAT?
3) Why do you want a SCRATCHPAD? I don't think that will cause the degradation that you described, but it could aggravate it.
4) Try STAY RESIDENT.
5) I don't know how to determine the LE options for a C++ pgm, but the defaults are typically way too high. Once you determine what you really need, you should provide better values in the CREATE.
6) Does C++ require a run-time environment to be loaded? I had a problem years ago with a user exit called from an IMS utility. The called pgm was an Assembler module that called a COBOL pgm, and everytime the utility invoked the user exit, the COBOL run-time environment was reloaded. The degradation was severe. I'd check to see if the C++ module is loading a run-time everytime it's invoked.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
