UDTF in a SQL query

After you register a UDTF with your Db2® system, you and other permitted users can use the UDTF in a FROM clause where a table would normally appear in a query. To use a UDTF, users must have Execute privilege for the FUNCTION object or for the specific UDTF.

For the parseNames example, assume that you have a table named orders that contains records for each customer sale of items, where the items are a comma-separated list of product ID codes. For example:

CREATE TABLE orders(order_id INTEGER, cust_id VARCHAR(200), sale_date 
DATE, prod_codes VARCHAR(1000)) DISTRIBUTE ON (order_id);

INSERT INTO orders(order_ID, cust_ID, sale_date, PROD_CODES) VALUES 
(124, 'AB123456', '2010-08-26', '124,6,12,121');

INSERT INTO orders(order_ID, cust_id, sale_date, PROD_CODES) VALUES 
(125, 'AB987657', '2010-08-26', '8');

INSERT INTO orders(order_ID, cust_id, sale_date, PROD_CODES) VALUES 
(126, 'AB456754', '2010-09-01', '32,5,76,65,121,98');

INSERT INTO orders(order_ID, cust_id, sale_date, PROD_CODES) VALUES 
(131, 'AB643623', '2010-09-02', '12,88,41');

INSERT INTO orders(order_ID, cust_id, sale_date, PROD_CODES) VALUES 
(142, 'AB664353', '2010-09-04', '1,145,52,53,93,98,100');

INSERT INTO orders(order_ID, cust_id, sale_date, PROD_CODES) VALUES 
(132, 'AB643623', '2010-09-04', '121');

INSERT INTO orders(order_ID, cust_id, sale_date, PROD_CODES) VALUES 
(143, 'AB123456', '2010-09-05', '87,182');

INSERT INTO orders(order_ID, cust_id, sale_date, PROD_CODES) VALUES 
(120, 'AB876123', '2010-09-05', '28,36,80');

INSERT INTO orders(order_ID, cust_id, sale_date, PROD_CODES) VALUES 
(150, 'CD876543', '2010-09-05', '80,43,55,12,4,67,92');

Note: Each database has default date format based on the locale, for example, mm-dd-yyyy is the default date format for USA. More details can be found in https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.nls.doc/doc/r0004572.html.

The orders table displays as:

select * from orders;

 ORDER_ID | CUST_ID  | SALE_DATE  |      PROD_CODES
----------+----------+------------+-----------------------
      120 | AB876123 | 2010-09-05 | 28,36,80
      126 | AB456754 | 2010-09-01 | 32,5,76,65,121,98
      142 | AB664353 | 2010-09-04 | 1,145,52,53,93,98,100
      150 | CD876543 | 2010-09-05 | 80,43,55,12,4,67,92
      143 | AB123456 | 2010-09-05 | 87,182
      124 | AB123456 | 2010-08-26 | 124,6,12,121
      132 | AB643623 | 2010-09-04 | 121
      125 | AB987657 | 2010-08-26 | 8
      131 | AB643623 | 2010-09-02 | 12,88,41
(9 rows)

Then, you can run the sample parseNames UDTF:

MYDB.SCHEMA(MYUSER)=> SELECT t.cust_id, f.product_id FROM orders AS t, TABLE 
( parseNames(prod_codes) ) AS f;

Sample output follows:

 CUST_ID  | PRODUCT ID
----------+------------
 AB876123 | 28
 AB876123 | 36
 AB876123 | 80
 AB987657 | 8
 AB123456 | 87
 AB123456 | 182
 AB643623 | 12
 AB643623 | 88
 AB643623 | 41
 AB123456 | 124
 AB123456 | 6
...

By default, the admin user account has execute access to all UDXs. The user account that registered a UDTF also has execute access to that UDTF. The database owner has privileges to run the UDTF in the database, and for systems with multiple schemas, the schema owner has privileges to run all UDTFs in the schema. Other users can be given privilege to run specific or all UDTFs.