Parameter list for execution of field procedures

The parameter list tells what operation is to be done and allows the field procedure to signal errors. DB2® provides storage for all parameters that are passed to the field procedure. Therefore, parameters are passed to the field procedure by address.

When defining and using the parameters in the field procedure, care should be taken to ensure that no more storage is referenced for a given parameter than is defined for that parameter. The parameters are all stored in the same space and exceeding a given parameter's storage space can overwrite another parameter's value. This, in turn, can cause the field procedure to see invalid input data or cause the value returned to the database to be invalid.

Parameter 1

A small (2 byte) integer that describes the function to be performed. This parameter is input only. Supported values are:

• 0 – field-encoding
• 4 – field-decoding
• 8 – field-definition

Parameter 2

A structure that defines the field procedure parameter value list (FPPVL).

• For function code 8, this parameter is input/output.
• For function code 0 and 4, this parameter contains the output of the function code 8 call. This parameter is input only

Parameter 3

The decoded data attribute that is defined by the Column Value Descriptor (CVD). This is the column attributes that were specified at CREATE TABLE or ALTER TABLE time. This parameter is input only.

Parameter 4

The decoded data. The exact structure is dependent on function code.

• If function code 8, then the NULL value. This parameter is input only.
• If function code 0, then the data to be encoded. This parameter is input only.
• If function code 4, then the location to place the decoded data. This parameter is output only.

Parameter 5

The encoded data attribute that is defined by the Field Value Descriptor (FVD).

• If function code 8, then the structure containing the encoded data attributes. This parameter is output only.
• If function code 0 or 4 , then a structure containing the encoded data attributes that was returned by the function 8 call. This parameter is input only.

Parameter 6

The encoded data that is defined by the Field Value Descriptor (FVD). The exact structure is dependent on function code.

• If function code 8, then the NULL value. This parameter is input only.
• If function code 0, then the location to place the encoded data. This parameter is output only.
• If function code 4, then the encoded form of the data. This parameter is input only.

Parameter 7

The SQLSTATE (character(5)). This parameter is input/output.

This parameter is set by DB2 to '00000' before calling the field procedure. It can be set by the field procedure. While normally the SQLSTATE is not set by a field procedure, it can be used to signal an error to the database as follows:

• If the field procedure detects an error, it should set the SQLSTATE to '38xxx', where xxx may be one of several possible strings. For more information, see DB2 Messages and Codes.

Warnings are not supported for field procedures

Parameter 8

The message text area (varchar(1000)). This parameter is input/output.

This argument is set by DB2 to the empty string before calling the field procedure. It is a VARCHAR(1000) value that can be used by the field procedure to send message text back when an SQLSTATE error is signaled by the field procedure. Message text is ignored by DB2 unless the SQLSTATE parameter is set by the field procedure. The message text is assumed to be in the job CCSID.

Parameter 9

A 128-byte structure containing additional information for the field procedure. This parameter is input only.

This structure is set by DB2 before calling the field procedure.

• sqlfpNoMask - For field procedures that mask data, it indicates that the caller is a system function that requires that the data be decoded without masking. For example, in some cases, RGZPFM and ALTER TABLE may need to copy data. If the field procedure ignores this parameter and masks data when these operations are performed, the column data will be lost. Hence, it is critical that a field procedure that masks data properly handle this parameter.
  Supported values are:

  • '0' - Normal masking applied if needed.
  • '1' - Do not mask, this decode operation is being performed on behalf of the database manager.
• sqlfpOperation - This parameter indicates whether the field procedure is being called for an encode operation that is building a key value for a key positioning operation. Key positioning operations such as RPG SETLL, RPG CHAIN, and COBOL START are common in applications that use the non-SQL interface for data access. This parameter can be useful for field procedure that mask data.
  Supported values are:

  • '0' - Not called for key positioning.
  • '1' - Called for key positioning.
  If a field procedure encounters a masked value on an encode request to build a key value, there are two actions for the field procedure to take:

  • Return SQLSTATE '09501' in parameter 7 which will cause the key positioning operation to fail with a field procedure error.
  • Return the masked value as the encoded value in parameter 6, so that the key positioning operation can continue using the masked value. In this case, the key positioning operation may or may not be successful. For example, an RPG SETLL request will likely be successful. However, an RPG CHAIN operation will likely fail with a record not found error.

Include SQLFP in QSYSINC/H describes these parameters.