function using an external source. Columns functions can be created as 

following:

db2>create function average(ID) returns ID source avg(integer)

Table function

A table function is a user defined function, which returns a data in tabular 

form to the SQL statement that calls it. So, a table function can only be 

invoked in the FROM clause of a SQL statement. Such a function can be 

used to apply SQL language processing power to non DB2 UDB data or to 

convert such data into a DB2 table. Table functions are always external. They 

are useful when you want to convert your file text data to tabular form, or 

convert your XML data to tabular form. Example 10 8 shows the creation and 

usage of a tabular function, which fetches the user's data from an existing file 

in a tabular format.

Example 10 8   Table function creation and usage

db2> create function usernames() 

returns table(

integer id,

vorname varchar(20),

name varchar(3),

email varchar(20))

external name  itso.users  

language C parameter style SQL 

not deterministic called on null 

input scratchpad final call 

no sql external action disallow parallel

db2>select * from TABLE(usernames()) as usernames

10.6  Materialized query tables (MQT)

A 

materialized query table (MQT) 

is a table whose definition is based on the 

result of a query, and whose data is in the form of precomputed results that are 

taken from one or more tables on which the materialized query table definition is 

based. Sometimes they are also referred to as 

materialized views

.

Prior to Version 8, DB2 UDB supported summary tables also known as 

automatic summary tables (ASTs)

 or 

replication summary tables

. Summary 

tables are now considered to be a special type of MQT whose fullselect contains 

a 

GROUP BY

 clause summarizing data from the tables referenced in the fullselect.

The main characteristics of MQTs are:

328 

MySQL to DB2 UDB Conversion Guide