SQL function -- General
On the General tab:
- Specify the name by which you want the function to be known in the Function field. The
name can be the same as a function or procedure that exists in the same schema. However,
the signature of the function must not be the same as any other function that exists in the same schema.
- Specify a description of the procedure in the Description field. The length can
be up to 2000 characters. This field is optional.
- Select to return a Single value or a Table.
- If you selected to return a Single value, select the type that the function returns.
Depending on what type you select, you must also specify the length of the return
value in the Length field and the number of digits to the right of the decimal in the
Scale field or the CCSID type in the CCSID field. If these values are different from
the values returned from the function program, the returning values will be cast to the values you define here.
- If you selected to return a table, insert columns into the column grid. Select the
name, type, length, scale, and CCSID as needed. If you select a large object type,
select Return value as a locator to save system resources by deferring or avoiding
moving data. If these values are different from the values returned from the
function program, the returning values will be cast to the values you define here.
- If you selected to return a table, you can specify a Table cardinality. If you
do not, the default value of 1000 will be used.
- If the function does not access information that may be used by another function, select Can run in parallel.
- If the function does not call other functions or procedures, select
Program does not call outside of itself (No External Action).
- If you want the same result to be returned every time the procedure is called
with the same values for the parameters, select Same result returned from successive
calls with identical input (Deterministic). This will save the values that the
procedure returns. Those values are automatically returned if the procedure is
called with the same parameters, thereby saving system resources.
- In the Data Access field, select Contains SQL if the function changes
and reads SQL data.
Select No SQL if the function does not read, write, or change SQL data. Select
Reads SQL data
if the function reads, but does not write or change, SQL data. Select Modifies SQL data if
the function changes, but does not read, SQL data.
- Specify a specific name for the function in the Specific name field. This
name must be different from other functions or procedures in the same schema. If
you do not specify a specific name, the system will generate one for you.
Next, select
Parameters
SQL statement