Choosing between AEs and UDXs

While UDX functions are invoked as callbacks where the NPS system calls UDX notification and implementation functions, an AE application runs as a conventional program that calls the NPS system to retrieve and return data.

There are three types of user-defined SQL functions as described in SQL functions. These functions should not be confused with the generic UDX term for functions that consist of user-defined functions (UDF), user-defined table functions (UDTF), user-defined aggregates (UDA), and user-defined shared libraries. For example, a UDF is a type of scalar function, but a scalar function does not need to be a UDF. In other words, the type is referred to as a SQL function, which can be a built-in, UDX, or AE.

When called as scalar functions or table functions, AEs have a different control mechanism than UDFs and UDTFs. The UDX functions are invoked as callbacks such that the NPS system calls UDX notification and implementation functions. In contrast, an AE application runs as a conventional program that calls the NPS system to retrieve and return data. To the UDX it appears that the NPS system calls the UDX, while the AE view is that the AE calls the NPS system. For the user, it means that an AE looks like a typical application, where, conceptually, input rows are similar to standard input and output results similar to standard output.

For aggregate functions, both AEs and UDXs use a notification/callback mechanism. Additionally, some AE Language Adapters, such as those for C++ and Java, provide an extra alternate callback message handler interface for functions.

The existing UDX capability of the NPS system and the new AE capability are complementary solutions. Because UDX operates inside an NPS system process, raw data transfer is faster and the system resource footprint is smaller. Because AEs operate outside the NPS system process, there is much more freedom and control over the application design.

As a general guideline, the three primary responsibilities of AE functionality are:
  • Process control
  • Data marshalling between the NPS system and the AE
  • Coordination between the specific SQL function calling mechanism and the AE (a calling mechanism refers to whether the AE was called as a table function, scalar function, or aggregate function)

The functionality for these three responsibilities is designed to be language-independent. Although Netezza provides specific AE support for a number of common languages, AEs are designed to support any programming language capable of calling C functions. They support both32-bit and 64-bit applications.

Since AEs extend the capabilities of the NPS appliance, there are some instances when a UDX maybe more suited to the solution. The following table offers some general guidelines when considering between UDXs and AEs.
Table 1. Guidelines for choosing between UDXs and AEs
Application UDX or AE
C++ with a simple algorithm that processes large amounts of data While it could be an AE, it typically should be written as a UDX. When algorithms are simple, most of the application time is used performing data transfer, a task that is better suited to UDXs.
Java Since a UDX must be written in C++, any Java applications must be implemented as an AE.
C++ with complex algorithms that makes heavy use of third-party application libraries Either a UDX or AE works, but the following application requirements suggest or mandate the use of an AE:
  • The use of a third-party library that is incompatible with the NPS system.
  • A requirement for lifecycle control. UDXs always have a limited life span. AEs can have indefinite life spans and can be run as daemons.
  • If the AE's conventional program design (as opposed to UDX callbacks) is a better fit for the application.