Parameter style SQL

With parameter style SQL, the parameters are passed into the external program as follows (in the order specified):

SQL-argument

This argument is set by Db2 before calling the UDF. This value repeats n times, where n is the number of arguments specified in the function reference. The value of each of these arguments is taken from the expression specified in the function invocation. It is expressed in the data type of the defined parameter in the create function statement. Note: These parameters are treated as input only; any changes to the parameter values made by the UDF are ignored by Db2.

SQL-result

This argument is set by the UDF before returning to Db2. The database provides the storage for the return value. Since the parameter is passed by address, the address is of the storage where the return value should be placed. The database provides as much storage as needed for the return value as defined on the CREATE FUNCTION statement. If the CAST FROM clause is used in the CREATE FUNCTION statement, Db2 assumes the UDF returns the value as defined in the CAST FROM clause, otherwise Db2 assumes the UDF returns the value as defined in the RETURNS clause.

SQL-argument-ind

This argument is set by Db2 before calling the UDF. It can be used by the UDF to determine if the corresponding SQL-argument is null or not. The nth SQL-argument-ind corresponds to the nth SQL-argument, described previously. Each indicator is defined as a two-byte signed integer. It is set to one of the following values:

0

The argument is present and not null.

-1

The argument is null.

If the function is defined with RETURNS NULL ON NULL INPUT, the UDF does not need to check for a null value. However, if it is defined with CALLS ON NULL INPUT, any argument can be NULL and the UDF should check for null input. Note: these parameters are treated as input only; any changes to the parameter values made by the UDF are ignored by Db2.

SQL-result-ind

This argument is set by the UDF before returning to Db2. The database provides the storage for the return value. The argument is defined as a two-byte signed integer. If set to a negative value, the database interprets the result of the function as null. If set to zero or a positive value, the database uses the value returned in SQL-result. The database provides the storage for the return value indicator. Since the parameter is passed by address, the address is of the storage where the indicator value should be placed.

SQL-state

This argument is a CHAR(5) value that represents the SQLSTATE.

This parameter is passed in from the database set to '00000' and can be set by the function as a result state for the function. While normally the SQLSTATE is not set by the function, it can be used to signal an error or warning to the database as follows:

01Hxx

The function code detected a warning situation. This results in an SQL warning, Here xx may be one of several possible strings.

38xxx

The function code detected an error situation. It results in a SQL error. Here xxx may be one of several possible strings.

function-name

This argument is set by Db2 before calling the UDF. It is a VARCHAR(139) value that contains the name of the function on whose behalf the function code is being called.

The form of the function name that is passed is:

     <schema-name>.<function-name>

This parameter is useful when the function code is being used by multiple UDF definitions so that the code can distinguish which definition is being called. Note: This parameter is treated as input only; any changes to the parameter value made by the UDF are ignored by Db2.

specific-name

This argument is set by Db2 before calling the UDF. It is a VARCHAR(128) value that contains the specific name of the function on whose behalf the function code is being called.

Like function-name, this parameter is useful when the function code is being used by multiple UDF definitions so that the code can distinguish which definition is being called. Note: This parameter is treated as input only; any changes to the parameter value made by the UDF are ignored by Db2.

diagnostic-message

This argument is set by Db2 before calling the UDF. It is a VARCHAR(70) value that can be used by the UDF to send message text back when an SQLSTATE warning or error is signaled by the UDF.

It is initialized by the database on input to the UDF and may be set by the UDF with descriptive information. Message text is ignored by Db2 unless the SQL-state parameter is set by the UDF.

scratchpad

This argument is set by Db2 before calling the UDF. It is only present if the CREATE FUNCTION statement for the UDF specified the SCRATCHPAD keyword. This argument is a structure with the following elements:

• An INTEGER containing the length of the scratchpad.
• The actual scratchpad, initialized to all binary 0's by Db2 before the first call to the UDF.

The scratchpad can be used by the UDF either as working storage or as persistent storage, since it is maintained across UDF invocations.

For table functions, the scratchpad is initialized as above before the FIRST call to the UDF if FINAL CALL is specified on the CREATE FUNCTION. After this call, the scratchpad content is totally under control of the table function. Db2 does not examine or change the content of the scratchpad thereafter. The scratchpad is passed to the function on each invocation. The function can be re-entrant, and Db2 preserves its state information in the scratchpad.

If NO FINAL CALL was specified or defaulted for a table function, then the scratchpad is initialized as above for each OPEN call, and the scratchpad content is completely under control of the table function between OPEN calls. This can be very important for a table function used in a join or subquery. If it is necessary to maintain the content of the scratchpad across OPEN calls, then FINAL CALL must be specified in your CREATE FUNCTION statement. With FINAL CALL specified, in addition to the normal OPEN, FETCH, and CLOSE calls, the table function will also receive FIRST and FINAL calls, for the purpose of scratchpad maintenance and resource release.

call-type

This argument is set by Db2 before calling the UDF. For scalar functions, it is only present if the CREATE FUNCTION statement for the UDF specified the FINAL CALL keyword. However, for table functions it is always present. It follows the scratchpad argument; or the diagnostic-message argument if the scratchpad argument is not present. This argument takes the form of an INTEGER value.

For scalar functions:

-1

This is the first call to the UDF for this statement. A first call is a normal call in that all SQL argument values are passed.

0

This is a normal call. (All the normal input argument values are passed).

1

This is a final call. No SQL-argument or SQL-argument-ind values are passed. A UDF should not return any answer using the SQL-result, SQL-result-ind arguments, SQL-state, or diagnostic-message arguments. These arguments are ignored by the system when returned from the UDF.

For table functions:

-2

This is the first call to the UDF for this statement. A first call is a normal call in that all SQL argument values are passed.

-1

This is the open call to the UDF for this statement. The scratchpad is initialized if NO FINAL CALL is specified, but not necessarily otherwise. All SQL argument values are passed.

0

This is a fetch call. Db2 expects the table function to return either a row comprising the set of return values, or an end-of-table condition indicated by SQLSTATE value '02000'.

1

This is a close call. This call balances the OPEN call, and can be used to perform any external CLOSE processing and resource release.

2

This is a final call. No SQL-argument or SQL-argument-ind values are passed. A UDF should not return any answer using the SQL-result, SQL-result-ind arguments, SQL-state, or diagnostic-message arguments. These arguments are ignored by the system when returned from the UDF.

dbinfo

This argument is set by Db2 before calling the UDF. It is only present if the CREATE FUNCTION statement for the UDF specifies the DBINFO keyword. The argument is a structure whose definition is contained in the sqludf include.