UDSF in a SQL query

After you register a UDSF in a Db2® instance, you can call the function in the same manner as any other SQL function. To use a UDSF, users must have the EXECUTE privilege for it.

For example, assume that the following SQL statements were executed to create a table:

CREATE TABLE customers (a INT, b VARCHAR(200));
INSERT INTO customers VALUES (1, 'Customer A');
INSERT INTO customers VALUES (2, 'Customer B');
INSERT INTO customers VALUES (3, 'Customer CBA');
INSERT INTO customers VALUES (4, 'Customer ABC');

You can run the sample customername function against the data in this table:

SELECT * FROM customers WHERE CustomerName(b) = 1;

The output is:

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

You can use fully qualified names to access functions in other schemas. The user who runs the query must have access privileges to the UDX in the other database. For example:

SELECT * FROM user1.customers WHERE
user2.CustomerName(b) = 1;