SQL Functions

SQL Functions

Keyword Description

Create Function Ext_Orders

(parmWhere CHAR(5)) CREATE FUNCTION names the UDTF and registers it with SQL. This function is called Ext_Orders (external orders). This name will be referenced by SQL statements using the function.

You may define an optional comma-delimited parameter list. For table functions, parameter lists are usually passed to a) limit the number of rows returned or b) assist in the calculation of column values.

Returns Table This keyword defines the column list (column names and attributes) that the table function will return.

External Name 'ExtDB.orders' This option is applicable only to functions written in a language other than SQL. The external name is a program name that will be called when the function is invoked. For Java programs, the external name is specified as Class.method. For non-Java programs, the name is specified as library/program.

Language Java This keyword specifies that the function is written in Java. Other options include SQL, COBOL, CL, RPGLE, C, etc.

Parameter Style DB2General The parameter style keyword determines how the database manager will pass parameters to an external program. Research these options thoroughly before writing programs.

For Java, the DB2General style is a DB2-specific option that specifies that the table function will extend the com.ibm.db2.app.UDF class.

Another parameter style called "Java" is used when writing routines conforming to the SQLJ Part 1: SQL Routines standard. However, this standard only supports scalar (not table) functions, so DB2General must be used.

Fenced The fenced option specifies that multiple invocations of the function (i.e., the function is used more than once in a single statement) will run in separate threads so as to avoid potential conflict with each other.

No SQL This parameter, which is mainly for optimization, specifies that there are no DB2 SQL statements to be run by the function.

Disallow Parallel This option is required for UDTFs that cannot be multi-threaded.

Scratchpad This option specifies that a static storage area will be allocated to the function. This storage area will be preserved between calls to the function. If a number isn't specified, 100 bytes will be allocated.

This keyword is special for Java UDTFs because if it is not specified, Java will instantiate a new instance of the class each time the function is invoked.

Final Call This option specifies that the FIRST and FINAL call types will be invoked.

Returns Null on Null Input If one or more input parameters passed to the table function are NULL, the function will return zero rows.