Java User-Defined Routines

The behaviors of installing and invoking UDRs written in Java code follow the SQLJ: SQL Routines specification. Every UDR written in Java code maps to an external Java static method whose class resides in a Java Archive (JAR) file that was installed in a database. The SQL-to-Java data type mapping is done according to the JDBC specification.

UDRs can be user-defined functions or user-defined procedures, which can return values or not, as follows:

• A user-defined function returns one or more values and therefore can be used in SQL expressions.

  For example, the following query returns the results of a UDR called area() as part of the query results:

  SELECT diameter, area(diameter) FROM shapes
  WHERE diameter > 6

• A user-defined procedure is a routine that optionally accepts a set of arguments and does not return any values.

  A procedure cannot be used in SQL expressions because it does not return a value. However, you can call it directly, as the following example shows:

  EXECUTE PROCEDURE myproc(1, 5)

  You can also call user-defined procedures within triggers.

For general information about UDRs, refer to the IBM Informix: User-Defined Routines and Data Types Developer's Guide.

UDRs written in Java code can perform the following tasks.

Type of UDR

Purpose

End-user routine

A UDR that performs some common task for an end user

User-defined aggregate

A UDR that calculates an aggregate value on a PROCEDURE particular column or value

Parallelizable UDR

A UDR that can run in parallel when executed within an SQL statement

(UDRs that open JDBC connections cannot run in parallel.)

Cast function

A UDR that converts or casts one data type to another

Operator function

A UDR that implements some operator symbol (such as +, -, or /)

Iterator function

A user-defined function that returns more than one row of data

Iterator functions written in Java code are supported using some Informix extensions.

Functional index

A UDR on which an index can be built

Opaque data type support
function

A user-defined function that tells the database server how to handle the data of an opaque data type

Negator function

A function that calculates the not operation for a particular operator or function

You cannot use UDRs written in Java code for any of the following features:

• Commutator functions
• Cost functions
• Operator-class functions
• Selectivity functions
• User-defined statistics functions