SQL function -- General

On the General tab:

  1. 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.
  2. Specify a description of the procedure in the Description field. The length can be up to 2000 characters. This field is optional.
  3. Select to return a Single value or a Table.
  4. 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.
  5. 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.
  6. 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.
  7. If the function does not access information that may be used by another function, select Can run in parallel.
  8. If the function does not call other functions or procedures, select Program does not call outside of itself (No External Action).
  9. 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.
  10. 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.
  11. 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