DB2 table functions: TABLE(), XMLTABLE, admin and monitoring

Invocation: the TABLE() wrapper

A table function is a table reference. The common pattern is:

1
2
3
SELECT T.*
  FROM TABLE(schema.function_name(arg1, arg2)) AS T
 WHERE T.some_column = 'value';

TABLE(...) tells the parser this function returns a table, not a scalar. The correlation name (AS T) names the result so you can qualify columns. Arguments are evaluated before the function produces rows. You can join TABLE(...) to base tables, views, and nested table expressions. Cardinality depends on the function: zero, one, or many rows.

XMLTABLE is special: it is written as XMLTABLE(...) in FROM without always needing the TABLE keyword in the same way, because the built-in syntax includes the column definitions. UNNEST turns an array into rows. User-defined and most supplied UDFs use TABLE(fn()).

Built-in: XMLTABLE and UNNEST

XMLTABLE

XMLTABLE evaluates an XQuery row expression and returns a table. PASSING supplies the XML document (and optional variables). COLUMNS names SQL columns with types and PATH expressions. FOR ORDINALITY adds a BIGINT position column.

1
2
3
4
5
6
7
8
9
SELECT X."PO ID", X."Quantity"
  FROM PO_TABLE P,
       XMLTABLE(
         '//item'
         PASSING P.PORDER
         COLUMNS
           "PO ID"    INTEGER PATH '../../@POid',
           "Quantity" INTEGER PATH 'quantity'
       ) AS X;

XMLTABLE is the shredding tool: XML in, relational rows out. XMLQUERY/XMLCAST stay in the SELECT list when you need a single value. When you need a set, use XMLTABLE in FROM.

UNNEST

If you use ordinary arrays (CREATE TYPE ... AS ... ARRAY), UNNEST(array-expression) returns one row per element. That is how you join an array host variable or a column of array type to a real table. Without UNNEST, an array is one value, not a set of rows.

Administrative table functions

IBM ships user-defined functions with Db2. The administrative task scheduler family lives typically in schema DSNADM and returns scheduler metadata as tables.

• ADMIN_TASK_LIST() — one row per defined task (TASK_NAME, schedule windows, INTERVAL, USERID, the stored procedure to run, optional input SELECT text, DB2_SSID, and related attributes). TASK_NAME is not nullable; many other columns are.
• ADMIN_TASK_STATUS — status/history style rows for executions (SQLCODE and timestamps of last runs). Use it after LIST to see whether a task is succeeding.

1
2
3
SELECT TASK_NAME, INTERVAL, USERID, DB2_SSID
  FROM TABLE(DSNADM.ADMIN_TASK_LIST()) AS T
 ORDER BY TASK_NAME;

These functions read scheduler data; they do not replace -DISPLAY or SDSF. Authorization follows the function’s package and schema privileges. If TABLE(DSNADM.ADMIN_TASK_LIST()) fails with a function-not-found SQLCODE, the supplied DDL was not installed or the SQL path does not include DSNADM—qualify the name.

Related admin interfaces are sometimes stored procedures (for example command wrappers) rather than table functions. If the manual says CALL, it is a procedure. If it shows FROM TABLE(...), it is a table function. Do not mix the two invocation styles.

Monitoring functions

BLOCKING_THREADS is the headline supplied table function for “who will get in the way of this change?” It helps identify applications, activities, and Db2 resources that may be incompatible with catalog migration or similar online catalog updates. You query it before a migrate window, then act on the thread or object list.

Day-to-day monitoring on z/OS is still largely IFI, -DISPLAY THREAD, traces, and vendor monitors. Table functions complement that world by giving SQL access to a specific snapshot (scheduler, blockers). They are not a replacement for RMF or Omegamon. When a function returns many rows, treat it like any other table: filter in WHERE, avoid SELECT * in production jobs, and do not poll it in a tight application loop.

Statistics functions versus catalog statistics

Beginners look for a STATS() table function that returns cardinality. On z/OS the optimizer reads catalog statistics that RUNSTATS wrote: SYSIBM.SYSTABLES, SYSINDEXES, SYSCOLUMNS, SYSCOLDIST, and related tables. Missing or conflicting statistics show up in:

• SYSIBM.SYSSTATFEEDBACK — catalog feedback of stats the optimizer wanted
• DSN_STAT_FEEDBACK — EXPLAIN-time feedback tied to QUERYNO

TYPE values such as T (table), I (index), C (cardinality), F (frequency), H (histogram) tell you which RUNSTATS options to add. DSNACCOX is a stored procedure that recommends REORG/RUNSTATS from real-time statistics—it is not a table function. When a topic list says “statistics functions” next to table functions, read it as “how SQL exposes stats and admin recommendations,” then go to the catalog and RUNSTATS, not to SUM().

User-defined table functions

SQL table functions

CREATE FUNCTION name (parameters) RETURNS TABLE (col type, ...) LANGUAGE SQL RETURN SELECT ... The SELECT is inlined. All RETURNS TABLE columns are nullable. Determinism and SQL-data access clauses (READS SQL DATA, and so on) must match what the body does.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE FUNCTION DEPTINFO (COLD_VALUE CHAR(9), T2_FLAG CHAR(1))
    RETURNS TABLE (COLA INT, COLB INT, COLC INT)
    LANGUAGE SQL
    SPECIFIC DEPTINFO
    NOT DETERMINISTIC
    READS SQL DATA
    RETURN
      SELECT A.COLA, B.COLB, B.COLC
        FROM TABLE1 AS A
        LEFT OUTER JOIN TABLE2 AS B
          ON A.COL1 = B.COL1 AND T2_FLAG = 'Y'
       WHERE A.COLD = COLD_VALUE;

Call it with FROM TABLE(DEPTINFO('ABC', 'Y')) AS D. Predicates on D.COLA may be optimized together with the inlined SELECT. Keep the body a single RETURN fullselect; compiled SQL PL loops belong in other routine types.

External table functions

LANGUAGE COBOL (or C, Java, …) RETURNS TABLE. The program is a package. Each time Db2 needs another row it invokes the function; the program returns one row or signals end of data. Use this when the source is not SQL (a VSAM file, a vendor API, a parsed report). Bind, WLM environment, and scratchpad options matter the same way they do for external stored procedures. Failures surface as SQLCODEs from the function’s runtime.

TABLE LIKE table-name AS LOCATOR appears on trigger-oriented external functions that receive a transition table locator, not the full table in a host structure.

Authorization and path

Unqualified function names search the SQL path (CURRENT PATH / PATH bind option), then built-ins. Qualify IBM functions (DSNADM.ADMIN_TASK_LIST) in jobs so a user UDF cannot shadow them. EXECUTE privilege on the function (and on underlying tables for SQL functions, following the usual SQL security model) is required. Do not grant EXECUTE on monitoring functions more widely than the people who may see thread and auth-id data.

Explain It Like I'm Five

A scalar function is a vending machine that drops one candy. A table function is a vending machine that drops a whole lunch tray with rows of snacks. You set the tray on the cafeteria table (FROM) and pick the snacks you want (SELECT). XMLTABLE is a tray maker that unpacks a lunchbox (XML) into neat rows. ADMIN_TASK_LIST is a tray of sticky notes about chores the janitor scheduled. BLOCKING_THREADS is a tray of “kids standing in the doorway” before you move a cabinet. If you only needed one number, you would have used a candy machine instead.

Exercises

1. Write a SELECT against TABLE(DSNADM.ADMIN_TASK_LIST()) AS T that returns only tasks with a non-null INTERVAL. What does a null INTERVAL mean?
2. Sketch CREATE FUNCTION EMP_BY_DEPT(D CHAR(3)) RETURNS TABLE (...) that returns EMPNO and LASTNAME for that department. How would you call it?
3. Explain when you would choose XMLTABLE versus XMLQUERY plus XMLCAST.
4. Look up BLOCKING_THREADS in the IBM SQL Reference for your function level and list the arguments your shop’s version accepts.
5. Query SYSIBM.SYSSTATFEEDBACK (if populated) and map TYPE to a RUNSTATS option. Why is that not a table function?