Many a times we come across situations where in we want to accomplish a task for which there is an already built in function or a procedure. Most of the database vendors provide numerous such functions and procedures to achieve such tasks. But there are also occasions that none of the existing functions meet the actual goal that one desires. In such cases a user ends up in writing a function. Herein this article, I try to highlight as to how one can come up with his/her own C++ function which can be invoked using the Netezza SQL.
A user can create custom functions, aggregates, and shared libraries using the user-defined extensions feature. The custom functions can be run on the Netezza Performance Server (NPS) systems and used to perform specific types of analysis for business reporting and data queries. These objects leverage the massively parallel processing (MPP) environment to accelerate analysis of data. User-defined functions enable data processing directly on the system, leading to elimination or reduction of data movement to other systems for analysis, which ensures performance gains.
In other words, User defined Extensions (UDX) enhances Netezza functionality by enabling a user to write a C++ coded modules which can be invoked using the SQL queries.
There different types of UDX’s supported by Netezza are –
- User-Defined Scalar Functions (UDF)
- User-Defined Table Functions (UDTF)
- User-Defined Aggregates (UDA)
- User-Defined Shared Libraries
A typical UDX development cycle will include –
- Writing C++ code for the UDX.
- Compile C++ program.
- Register the UDX using the Netezza SQL CREATE.
- Test/Debug the UDX.
- Deployment of the UDX on development system followed by production system.
- Grant necessary permissions to the user to execute the UDX.
- Consider if the UDX can be executed in non-fenced mode
Let’s take a look on how UDF is created and used. UDF takes 0 or more input parameters and returns one output value. It is called once for every row and can be used in all SQL queries where built in functions are supported.
How to create the C++ file for UDF?
The file must include the header file udxinc.h in addition to any standard header file. The UDX classes and functions for API version 2 are defined in a namespace called nz:udx_ver2.
using namespace nz::udx_ver2;
Create a class object derived from the udf base class. The code will look something like this :
using namespace nz::udx_ver2;
class CMyFunc: public nz::udx_ver2::Udf
MyFunc(UdxInit *pInit) : Udf(pInit)
static nz::udx_ver2::Udf* instantiate(UdxInit *pInit);
virtual nz::udx_ver2::ReturnValue evaluate()
// Function Logic
nz::udx_ver2::Udf* MyFunc::instantiate(UdxInit *pInit)
return new MyFunc(pInit);
The instantiate method creates the object dynamically.
This CPP file will have to be compiled using the nzudxcompile utility.
The following object files are created
Once the UDF is compiled, it needs to be registered with the NPS system. The same utility can be used
nzudxcompile MyFunc.cpp –o myFunc.o
–-sig "MyFunc(varchar(64000))" --version 2 –-return INT4
–-class CMyFunc –-user myuser –-pw password –-db mydb
Alternatively, the UDF can be registered using SQL, For ex –
CREATE FUNCTION MyFunc(varchar(64000))
RETURNS int4 LANGUAGE CPP PARAMETER STYLE NPSGENERIC API VERSION 2
EXTERNAL CLASS NAME 'CMyFunc'
EXTERNAL HOST OBJECT '/home/nz/udx_files/myfunc.o_x86'
EXTERNAL SPU OBJECT '/home/nz/udx_files/myfunc.o_spu10';
These registered functions can be invoked like any function in a SQL statement.