Userdefined Functions in DB2

1
User-defined Functions in DB2

• Pam Odden

2
Objectives

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

3
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
  program
• 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 function
• 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

4
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
  application

5
The User-Defined Function Environment

6
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.

7
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
  linked
• If the user-defined function contains SQL
  statements, the bind process is used to create a
  package
• 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

8
External Example, cont.

• A CREATE FUNCTION statement registers CALC_BONUS
  to DB2
• CREATE FUNCTION CALC_BONUS(DECIMAL(9,2),DECIMAL(9,
  2))
• 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
• ( SELECT CALC_BONUS(SALARY,COMM)
• FROM EMP WHERE EMP_ID 12345 )

9
Example SQL Scalar UDF

• In an SQL scalar function, the CREATE FUNCTION
  statement contains the source code
• 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

10
Example, cont.

• Say the bonus from our previous example can be
  calculated from the salary using simple math
• CREATE FUNCTION CALC_BONUS (SAL DECIMAL(9,2))
• RETURNS DECIMAL(9,2)
• LANGUAGE SQL
• CONTAINS SQL
• NO EXTERNAL ACTION
• DETERMINISTIC
• RETURN SAL 0.8
• This function could be called as in
• SELECT SALARY, CALC_BONUS(SALARY)
• FROM EMP
• WHERE CALC_BONUS(SALARY) gt 1000.00
• OR CALC_BONUS(SALARY) 0.00

11
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 integers.
• CREATE FUNCTION MYAREA (INTEGER, INTEGER)
• RETURNS DECIMAL (9,2)
• SOURCE M7535DB1.AREA (DECIMAL(7,2),
  DECIMAL (7,2))

12
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.
• CREATE FUNCTION DOCMATCH (VARCHAR(30),
  VARCHAR(255))
• RETURNS TABLE (DOC_ID CHAR(16))
• EXTERNAL NAME DOCM1
• LANGUAGE C
• PARAMETER STYLE DB2SQL
• NO SQL
• DETERMINISTIC
• NO EXTERNAL ACTION
• FENCED
• SCRATCHPAD
• FINAL CALL
• DISALLOW PARALLEL
• CARDINALITY 20
• SELECT DOC_ID FROM DOCMATCH(SUPERHEROS,
  Dogbert)

13
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.

14
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 storage.
• Returns null on null input or Called on null
  input if returns null on null input is
  specified, the program wont 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

15
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 made.
• Disallow Parallel tells DB2 not to split up the
  select statement into multiple tasks. This is
  required for a udf that returns a table.
• 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.

16
Summary

• 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