Re: Another SQL headbanger

Pete,

You definitely want to download this REDBOOK:

Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries
http://www.redbooks.ibm.com/abstracts/sg246503.html?Open
SQL Stored Procedures ~= RPG IV Programs
SQL User Defined Functions ~= RPG IV Procedures

UDFs can be external, with the code written in RPG, CL, COBOL, C, Java, ect, or SQL UDFs written in
SQL as in my example. Stored procedures work the same way.

There are two types of UDFs:
1) Scalar, which return a single value like in my example
2) Table, which return a "temporary table" or results set that can be used in the FROM clause of a SQL
SELECT statement.

As with stored procedures, when you create a SQL UDF the system actually creates a ILE C program.
While both SPs and UDFs live in a particular library you can call them system wide. You can
save/restore both SPs and UDFs.

One important thing to keep in mind with UDFs is that the DB expects them to return quickly. There is
in fact a timeout after which the DB will throw an exception message. Default is 30sec and is
controlled by QAQQINI. Given that a UDF could conceivably be invoked for every row of a million row
table, you can see why they should be fast.


HTH,
Charles