Creating UDXs in R

Creating a user-defined extension (UDX) in R involves designing the UDX, writing and debugging the underlying R code, and operational testing.

To create a UDX in R:
  1. Identify the action that the user-defined extension is to perform. For example, you might require a function that performs tasks such as specialized string operations or comparisons; custom mathematical analysis; or conversions such as metric to English measurements, Celsius to Fahrenheit, or currency conversions.

    Before the user-defined extensions feature was introduced, conversion and analysis tasks typically required users to export data from the database, carry out the conversion and analysis processing, then load the converted data back into the database for storage. With user-defined extensions, you can do many or all of these steps directly in the database.

  2. Review the existing SQL functions to confirm that your function is not already available as an SQL function. SQL offers a wide set of string, mathematical, analytical, and conversion functions. For example, if you want a function that changes the letter case of a string from uppercase to lowercase, or vice versa, SQL already provides LOWER() and UPPER() functions to do this.
  3. Write the R code that implements the corresponding function and save it as a source file.
  4. Deploy (promote and register) the UDX as described in Deploying a UDX on a Db2 instance.
  5. Test and debug the UDX to identify and resolve any errors in the processing.
  6. If necessary, transfer the UDX to other Db2 instances as described in Transferring a UDX from one Db2 instance to another. Users of those instances can use the UDX in their SQL queries in the same way that they would use any other SQL function.