Creating UDXs in Lua

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

Lua is a powerful, lightweight scripting language for general procedural programming. Because it is an extension language, it has no notion of a “main” program; it works only when embedded in a program called the embedding program or host program. The host program can invoke functions to execute a segment of Lua code, can write and read Lua variables, and can register C functions to be called by Lua code. By using C functions, you can augment Lua to cover a wide range of problem domains.

nzLua is a dialect of the Lua language designed especially for creating user-defined extensions (UDXs). Some of the features of Lua, such as the ability to access external files, ability to execute external programs, and debugging features, have been disabled in nzLua. However, other functions have been added to make nzLua easier to use for developers who are familiar with standard SQL functions.

nzLua was created using a version of Lua called LuaJIT, which uses just-in-time compilation techniques to compile frequently-used code paths directly into 80x86 instructions. Although a UDX written in nzLua is slower than one written in C++, the JIT compiler results in execution that is 5 to 50 times faster than is typical for UDXs written in other interpreted languages such as Perl, Python, or JavaScript (including Javascript V8, which also uses a JIT compiler).

To create a UDX in Lua:
  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 Lua 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.
    Note: During deployment, a Lua UDX is automatically converted to and registered as a C++ UDX. The corresponding entry in the system catalog will specify the language of the UDX as CPP (for C++).
  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.