Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2008
    Posts
    7

    Unanswered: 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

    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.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jun 2008
    Posts
    7
    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.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2008
    Posts
    7
    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.

  6. #6
    Join Date
    Jun 2008
    Posts
    7
    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.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jun 2008
    Posts
    7
    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.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •