Using External Tables (XPS)

In Extended Parallel Server, a SELECT statement that is a part of a LOAD or UNLOAD operation involving an external table is subject to these restrictions:

When you move data from a database into an external table, the SELECT statement must define all columns in the external table. The SELECT statement must not contain a FIRST, FOR UPDATE, INTO, INTO SCRATCH, or INTO TEMP clause. You can use an ORDER BY clause, however, to produce files that are ordered within themselves.

Execute Routine Clause

You can specify the EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement to insert values that a user-defined function returns.

Execute Routine Clause:
 
|--EXECUTE--+-PROCEDURE--procedure------+----------------------->
            |  (1)                      |
            '--------FUNCTION--function-'
 
>--(--+-----------------------+--)------------------------------|
      | .-,-----------------. |
      | V              (2)  | |
      '---| Argument |------+-'

Notes:

Dynamic Server only
See Arguments

Element	Description	Restrictions	Syntax
function, procedure	User-defined function or procedure to insert the data	Must exist	Database Object Name

When you use a user-defined function to insert column values, the return values of the function must have a one-to-one correspondence with the listed columns. That is, each value that the function returns must be of the data type expected by the corresponding column in the column list.

For backward compatibility, Dynamic Server can use the EXECUTE PROCEDURE keywords to execute an SPL function that was created with the CREATE PROCEDURE statement.

If the called SPL routine scans or updates the target table of the INSERT statement, the database returns an error. That is, the SPL routine cannot select data from the table into which you are inserting rows.

If a called SPL routine contains certain SQL statements, the database server returns an error. For information on which SQL statements cannot be used in an SPL routine that is called within a data manipulation statement, see Restrictions on SPL Routines in Data-Manipulation Statements.