Creating UDXs in R

Beginning with IBM Db2 version 11.5.6, creating a user-defined extension (UDX) in R involves designing the UDX, writing and debugging the underlying code, and operational testing.

To create a UDX in R:
  1. Ensure that the R_path database manager configuration parameter is pointing to a R runtime that can be used by the Db2 fenced process. Please see.
  2. 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.

  3. 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.
  4. Write the R code that implements the corresponding function and save it as a source file.
  5. Create the source files as referenced here:Creating UDXs in R.
  6. Test and debug the UDX to identify and resolve any errors in the processing.