Debug by using UDX stubs

You can replace calls to UDFs or UDAs with a stub object that returns a trivial value. Use this process to “turn off” your user-defined functions or aggregates if a table query fails. Disabling UDXs might also be helpful during times when you need to troubleshoot query performance or system issues such as SPU resets. The UDX stub capability is not supported for queries that run in the Postgres environment on the host.

The UDX stub capability is controlled by using an nzsql session variable called udx_stub. If you set the variable to true (or 1), UDXs are not executed fully. Instead, they return a basic value that essentially causes them to be ignored.

To enable the UDX stub processing, set the session variable within your nzsql session as follows

   MYDB.SCHEMA(MYUSER)=> set udx_stub=1;

The command returns the message SET VARIABLE if successful.

When you next run a SQL query that calls a UDX, the UDX does not execute. For example, use the CustomerName UDF example from Create user-defined functions. If you enter the following query:

MYDB.SCHEMA(MYUSER)=> SELECT * FROM customers WHERE CustomerName(b) = 1;

The output would normally be:

 A | B
---+------------------
 1 | Customer A
 4 | Customer ABC
(2 rows)

If you enable the UDX stub processing, the output for the same query displays as follows:

 A | B
---+------------------
 3 | Customer CBA
 1 | Customer A
 4 | Customer ABC
 2 | Customer B
(4 rows)

Essentially, with the UDX stub enabled, the WHERE clause does not restrict the output; therefore, the command displayed the entire table.

To disable the UDX stub processing and enable your user-defined functions and aggregates, set the udx_stub session variable to false (0):

   MYDB.SCHEMA(MYUSER)=> set udx_stub=0;