Re: Another SQL headbanger

[Pete Helgren <Pete@xxxxxxxxxx>]

Great stuff Charles.  This will be very helpful.

The UDF worked like a charm!

Thanks again,

Pete


Wilt, Charles wrote:
> 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
>
>
>