User-defined Functions in DB2 by z54WpGGV


									User-defined Functions in DB2

                                    Pam Odden

Staff Development Session 2003.10               08/27/2003

 What is a User-defined Function
 How to implement one
 Examples
      – External UDF
      – SQL User-defined Scalar Function
      – Sourced UDF

Staff Development Session 2003.10                08/27/2003
            What is a User-defined Function?
    Functionality that is defined in the database and can
     be used within SQL
       – Can range from simple SQL expression to a complex

    There are 3 types of UDF
       – External – separate program written in a host language
       – SQL – contains all the code in the DB2 UDF definition
       – Sourced – based on existing built-in or user-defined

    UDFs can also be categorized by what is returned
       – User-defined scalar function (returns one value)
       – User-defined table function (returns a table)
       – Only an external UDF can return a table
Staff Development Session 2003.10                                 08/27/2003
                               Creating a UDF
         Creating and using a user-defined function
          involves these steps:
        –     Setting up the environment for user-defined
              functions. A system administrator usually
              performs this step.
        –     Writing and preparing the user-defined
              function. This step is necessary only for an
              external user-defined function.
        –     Defining the user-defined function to DB2
        –     Invoking the user-defined function from an SQL

Staff Development Session 2003.10                          08/27/2003
        The User-Defined Function Environment

Staff Development Session 2003.10               08/27/2003
                  Example – External Scalar UDF

  Suppose your organization needs a user-defined
     scalar function that calculates the bonus each
     employee receives. Since it is a complex
     calculation dependent on information in other
     tables, an external UDF is specified:
        –     The function name is CALC_BONUS.
        –     The two input fields are on the EMP table, SALARY and
              COMM columns, of type DECIMAL(9,2).
        –     The output is DECIMAL(9,2).
        –     The program for the user-defined function is written in
              COBOL and has a load module name of CBONUS.

Staff Development Session 2003.10                                   08/27/2003
                          External Example, cont.

        The external user-defined function is
         created in the following steps:
        – The user-defined function, which is a COBOL
          program, is written, precompiled, compiled, and
        – If the user-defined function contains SQL
          statements, the bind process is used to create a
        – The program is tested thoroughly
        – Execute authority must be granted on the user-
          defined function package to the person who will
          define the UDF in DB2
Staff Development Session 2003.10                        08/27/2003
                         External Example, cont.
      A CREATE FUNCTION statement registers
       CALC_BONUS to DB2:
                     RETURNS DECIMAL(9,2)
                     EXTERNAL NAME 'CBONUS'
                     PARAMETER STYLE DB2SQL
                     LANGUAGE COBOL;

      The definer then grants execute authority on
       CALC_BONUS to all invokers
      Statements may now be executed, either statically
       or dynamically, using CALC_BONUS:
                 UPDATE PAYROLL SET BONUS =
                    FROM EMP WHERE EMP_ID = ‘12345’ );

Staff Development Session 2003.10                                        08/27/2003
                   Example: SQL Scalar UDF

     In an SQL scalar function, the CREATE
      FUNCTION statement contains the source
        – The source code is a single SQL expression that
          evaluates to a single value
        – the SQL expression is specified in the RETURN
          clause of the CREATE FUNCTION statement
        – The value of the SQL expression must be
          compatible with the data type of the parameter in
          the RETURNS clause
        – To prepare an SQL scalar function for execution,
          you execute the CREATE FUNCTION statement,
          either statically or dynamically
Staff Development Session 2003.10                         08/27/2003
                                Example, cont.
   Say the bonus from our previous example
    can be calculated from the salary using
    simple math:
                     RETURNS DECIMAL(9,2)
                     LANGUAGE SQL
                     CONTAINS SQL
                     NO EXTERNAL ACTION
                     RETURN SAL * 0.8;
   This function could be called as in:
                      FROM EMP
                      WHERE CALC_BONUS(SALARY) > 1000.00
                      OR CALC_BONUS(SALARY) = 0.00;

Staff Development Session 2003.10                                08/27/2003
              Example: Sourced Scalar UDF
  A sourced scalar function can be created to make
   use of an existing function, either built-in or user-
   defined, and change it to suit different requirements.
  This allows using parameters of different data types
   than the original function was created to handle.
  Example:
 Say your department has a udf called AREA that
   calculates the area of a rectangle when given the
   length and width, in decimal data types. You want
   to do the same thing, but your length and width are
                RETURNS DECIMAL (9,2)
                SOURCE M7535DB1.AREA (DECIMAL(7,2), DECIMAL (7,2))

Staff Development Session 2003.10                                    08/27/2003
               Example: External Table UDF
    A table function would be used in the FROM clause of a
     SELECT statement
    It returns the table to the SELECT one row at a time
    Example: defines a function that finds documents in a text
     management system. The first parameter is a subject area and
     the second is a string to search for. A list of documents is
     returned that are in the desired subject area and contain the
     desired string.
                RETURNS TABLE (DOC_ID CHAR(16))
                EXTERNAL NAME DOCM1
                LANGUAGE C
                PARAMETER STYLE DB2SQL
                NO SQL
                NO EXTERNAL ACTION
                FINAL CALL
                DISALLOW PARALLEL
                CARDINALITY 20;

Staff Development Session 2003.10                                08/27/2003
               Options for User-defined Functions
    Deterministic or Not Deterministic: a deterministic function
     always returns the same output for the same input, ie. a
     function that returns the square root of the input. Our example
     is deterministic. A function that is not deterministic may return
     different output for the same input, ie. a random number
     generator, or a function that is dependent on the current date
     or current time

    External Action or No External Action: specifies whether the
     function takes an action that changes the state of an object not
     managed by DB2, for example, sending a message or writing
     a file.

    No SQL, Contains SQL, Reads SQL Data, or Modifies SQL
     Data: specifies the extent of SQL in the function. DB2 does
     check this, and an SQL error is returned if the function tries to
     do more than specified.
Staff Development Session 2003.10                                        08/27/2003
                                    Options, cont.
    Parameter style DB2SQL or Parameter style JAVA: for all
     languages other than JAVA the parameters are SQL data
     types and are automatically given indicator variables so null
     values can be passed. When the udf is coded in JAVA,
     parameters are defined in a way that conforms with JAVA

    FENCED: specifies that the external program runs in an
     external address space to prevent possible corruption of DB2

    Returns null on null input or Called on null input: if returns null
     on null input is specified, the program won’t be called if any of
     the parameters is null. Otherwise, the program will be called
     and will allow for the possibility of null input.

    Scratchpad or No scratchpad: causes DB2 to provide a place
     for the function to store info from one invocation to the next

Staff Development Session 2003.10                                          08/27/2003
                                    Options, cont.

    Final Call or No Final Call: With Final Call, a call will be made
     at the beginning for initialization and at the end for clean up.
     Either way, one or more Open, Fetch, and Close Calls are

    Disallow Parallel: tells DB2 not to split up the select statement
     into multiple tasks. This is required for a udf that returns a

    Cardinality integer: Specifies an estimate of the number of
     rows you expect will be returned, that is used in optimization.

    Stay Resident No or Yes: No causes the load module to be
     deleted from memory after execution. Yes causes it to remain
     in memory.

Staff Development Session 2003.10                                        08/27/2003
    UDFs can make powerful functionality
     available within SQL statements
    For complex functions, a separate program
     is written and prepared in a host language,
     which is called by DB2
    Simple scalar functions have an SQL
     statement included in the function definition
    UDFs can be based on existing built-in or
     user-defined functions, and take arguments
     of different data types
    External table functions can return multiple
     rows to the calling program and handle
     opening, fetching, and closing a cursor
Staff Development Session 2003.10                    08/27/2003

To top