As I am not familiar with DB2 explain facility, I haven't tried this
yet. Need some time to study how to compare access plans =P

Thanks Mike~

mike

My query goes sth like this:

I have a table named "attendance", which records each shift and the
associated # of hours worked by every part-time staff each day.

The table's size is very large (over 1,000,000 rows) and keeps growing
every day.

Sample data for "attendance":

staff    rdate        shiftid    hour
====================================
ABC    2006-05-01    1    2
ABC    2006-05-01    2    3
DEF    2006-05-01    1    3

My query is "Check how many hours worked by each staff per day during
'2006-05-01' AND '2006-05-02'.

Plain SQL
=================================

SELECT
       staff,
       rdate,
       SUM(hour)
FROM
       attendance
WHERE
       rdate BETWEEN '2006-05-01' AND '2006-05-02'

GROUP BY
       staff,
       rdate
;

UDF Body
=========

CREATE FUNCTION query (csdate DATE,cedate DATE)
RETURNS TABLE
(
       staff           VARCHAR(20),
       rdate           DATE,
       ttlhour         INTEGER
)

SPECIFIC query
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN

SELECT
       staff,
       rdate,
       SUM(hour)
FROM
       attendance
WHERE
       rdate BETWEEN csdate AND cedate

GROUP BY
       staff,
       rdate
;

UDF sample invocation
=====================
select * from table(query(DATE('2006-05-01'),DATE('2006-05-02'))) as T1

Have I coded something wrong that causes such a long execution time
with UDF?