DB2 for iSeries Considerations


For completeness, the syntax diagram for the iSeries CREATE FUNCTION statement is shown in Figure 9.31, followed by an explanation of differences from DB2 on LUW.

Figure 9.31. CREATE FUNCTION statement syntax for iSeries.
 >>-CREATE FUNCTION---function-name -----------------------------> >--(--+--------------------------------+--)--*------------------>       | .-,--------------------------. |       | V                            | |       '---parameter-name--data-type1-+-' >--RETURNS--+-data-type----------------------------+---*-------->             |        .-,-------------------------. |             |        V                           | |             '-TABLE----column-name--data-type2---+-' >--LANGUAGE SQL-----------------------------------------------+->    .-NOT DETERMINISTIC-- .    .-MODIFIES SQL DATA--. >--+--------------------+-----+--------------------+------------>    '-DETERMINISTIC------'     +-CONTAINS SQL-------+                               '-READS SQL DATA-----'    .-CALLED ON NULL INPUT-------. >--+----------------------------+--+--------------------------+->    '-RETURNS NULL ON NULL INPUT-'  '-SPECIFIC--specific-name--'    .-EXTERNAL ACTION-----.     .-FENCED-----. >--+---------------------+-----+------------+------------------->    '-NO EXTERNAL ACTION--'     '-NOT FENCED-'    .-STATIC DISPATCH------. >--+----------------------+-----+---------------------+--------->                                 '-ALLOW PARALLEL------'                                 '-DISALLOW PARALLEL---'                                  .-INHERIT SPECIAL REGISTERS-. >--+---------------------------+-+---------------------------+-->    '-CARDINALTITY integer------' >--+----------------------+---SQL-routine-body------------------><    '-SET OPTION-statement-' 

A ROW is not a valid RETURN type option. It is considered a special case of a TABLE.

A UDF specified with the UNFENCED option executes in a thread within the same environment as the database manager, in contrast to the FENCED option which executes in a thread outside the environment. The UNFENCED option has the potential to perform better.

The ALLOW PARALLEL option permits multiple instances of the UDF to execute at the same time, and the DISALLOW PARALLEL option gives you the option of serializing the execution of the UDF. For table functions, only the DISALLOW PARALLEL option is valid and is mandatory.

The CARDINALITY clause is only used for table functions and provides an estimate on the number of rows returned. This information, if available, is used by the optimizer.

The SET OPTION option statement is used to specify processing options that will be used to create the function. For a complete list of options, refer to the SET OPTION statement information found in the "Statements" chapter of the DB2 UDB for iSeries SQL Reference. If the SET OPTION clause is specified, it must be the last clause before the SQL routine body.



    DB2 SQL PL. Deployment and Advanced Configuration Essential Guide for DB2 UDB on Linux., UNIX, Windows, i5. OS, z. OS
    Intelligent Enterprises of the 21st Century
    ISBN: N/A
    EAN: 2147483647
    Year: 2003
    Pages: 205

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net