Design and Parameter Information for DB2 Field Procedures

In V7R1M0 of i5/OS, DB2 for i was enhanced to introduce field procedures for column level encoding (such as encryption). A field procedure is a user-written exit routine to transform values in a single column. When values in the column are changed, or new values inserted, the field procedure is invoked for each value. The field procedure can transform that value (encode it) in any way. The encoded value is then stored. When values are retrieved from the column, the field procedure is invoked for each encoded value. The field procedure decodes each value back to the original value. Any indexes defined on a column that uses a field procedure are built with encoded values.


Field procedures are assigned to a table by the FIELDPROC clause of CREATE TABLE and ALTER TABLE. A field procedure that is specified for a column is invoked in three general situations; so when you write your field procedure, you need to handle each of these situations:

o	For field-definition, when the CREATE TABLE or ALTER TABLE statement that names the procedure is executed. During this invocation, the procedure is expected to: -- Determine whether the data type and attributes of the column are valid. -- Verify the literal list, and change it if wanted. -- Provide the internal field description of the column (for example, a longer VARCHAR for an encrypted value).
o	For field-encoding, when a column value is field-encoded. That occurs for any value that: -- is inserted in the column by an SQL INSERT statement, SQL MERGE statement, or native write. -- is changed by an SQL UPDATE statement, SQL MERGE statement, or native update. -- is the target column for a copy with an associated field procedure. The field procedure might be invoked to encode the copied data. Examples include SQL Statements ALTER TABLE or CREATE TABLE LIKE/AS and CL commands CPYF and RGZPFM. -- is compared to a column with a field procedure. The QAQQINI option FIELDPROC_ENCODED_COMPARISON is used to determine if the column value is decoded, or the host variable, constant, or join column is encoded. -- is the DEFAULT value for a column with an associated field procedure in a CREATE or ALTER TABLE statement. If there are any after or read triggers, the field procedure is invoked before any of these triggers. If there are any before triggers, the field procedure is invoked after the before trigger.
o	For field-decoding, when a stored value is field-decoded back into its original value. Field-decoding occurs for any value that: -- is retrieved by an SQL SELECT or FETCH statement, or by a native read. -- is a column with an associated field procedure that is copied. The field procedure might be invoked to decode the data before making the copy. Examples include SQL Statements ALTER TABLE, CREATE TABLE LIKE/AS, and CL commands CPYF and RGZPFM. -- is compared to a column with a field procedure. The QAQQINI option FIELDPROC_ENCODED_COMPARISON is used by the optimizer to decide if the column value is decoded, or if the host variable or constant is encoded.

The following two charts describe the structure of the decoded (parameter 3, also known as the CVD) and encoded (parameter 5, also known as the FVD) in the parameter list that DB2 passes to the field procedure. For further details, please see the SQL Programming manual.
 

Table 1. sqlfpParameterDescription_T
Name	Offset	Data Type	Description
sqlfpSqlType	0	2-byte integer	SQL data type of this parameter. See Appendix D of the SQL Reference for supported values.
sqlfpByteLength	2	unsigned 4-byte integer	Length in bytes of this parameter. For datetime parameters, the length of the string representation of the parameter.
sqlfpLength	6	unsigned 4-byte integer	Length in characters of this parameter. If this is a not a character or graphic type, sqlfpLength and sqlfpByteLength are the same value.
sqlfpPrecision	10	2-byte integer	Precision if this is a numeric parameter that has precision (decimal, zoned, binary with precision and scale).
sqlfpScale	12	2-byte integer	Scale if this is a numeric parameter that has scale (decimal, zoned, binary with precision and scale). Scale of 0 if this is a date or time parameter. Scale of 6 if this is a timestamp parameter.
sqlfpCcsid	14	unsigned 2-byte integer	CCSID of this parameter if character or graphic or XML.
sqlfpAllocatedLength	16	unsigned 2-byte integer	The allocated length specified for the column on the CREATE TABLE or ALTER TABLE statement.
reserved1	18	character(14)	Reserved.

These charts also briefly describe what is supported for the decoded and encoded attributes of the Field Procedure invocation parameters.

The following chart will help you when coding your field procedure because you will need to know the supported values for each part of the sqlfpParameterDescription for both the decode and encoded data type that you wish to use. For a more detailed explanation of each supported data type, you should refer to CREATE TABLE and the SQLDA appendix in the SQL Reference manual.
 

Table 2.
sqlfpSqlType	Data Type	sqlfpByteLength	sqlfpLength	sqlfpPrecision	sqlfpScale	sqlfpCcsid	sqlfpAllocatedLength (Note 2)
384/385	Date	10	10	N/A	N/A	SBCS CCSID	N/A
388/389	Time	8	8	N/A	N/A	SBCS CCSID	N/A
392/393	Timestamp	26	26	N/A	N/A	SBCS CCSID	N/A
396/397	DataLink (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A
400/401	NUL-terminated graphic string (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A
404/405	BLOB(X)	X, where X is length of BLOB	X, where X is length of BLOB	N/A	N/A	65535	Allocated Length
408/409	CLOB(X)	X, where X is length of CLOB	X, where X is length of BLOB	N/A	N/A	SBCS, Mixed, or UTF8 CCSID	Allocated Length
412/413	DBCLOB(X)	# of bytes (2*X)	X, where X is the # of graphic characters	N/A	N/A	DBCS, UCS2 , or UTF16 CCSID	Allocated Length
448/449	VARCHAR(X)	X, where X is the length of the VARCHAR	X, where X is the length of the VARCHAR	N/A	N/A	SBCS, Mixed, or UTF8 CCSID	Allocated Length
452/453	CHAR(X)	X, where X is the length of the CHAR	X, where X is the length of the CHAR	N/A	N/A	SBCS, Mixed, or UTF8 CCSID	N/A
456/457	VARCHAR(X)	X, where X is the length of the VARCHAR	X, where X is the length of the VARCHAR	N/A	N/A	SBCS, Mixed, or UTF8 CCSID	Allocated Length
460/461	Nul-terminated character string (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A
464/465	VARGRAPHIC(X)	# of bytes (2*X)	X, where X is the # of graphic characters	N/A	N/A	DBCS, UCS2, or UTF16 CCSID	Allocated Length
468/469	GRAPHIC(X)	# of bytes (2*X)	X, where X is the # of graphic characters	N/A	N/A	DBCS, UCS2, or UTF16 CCSID	N/A
472/473	VARGRAPHIC(X)	# of bytes (2*X)	# of bytes (2*X)	N/A	N/A	DBCS, UCS2, or UTF16 CCSID	Allocated Length
476/477	Pascal L String (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A
480/481	FLOAT	4 for single precision, 8 for double precision	N/A	N/A	N/A	N/A	N/A
484/485	DECIMAL(X,Y)	(X+1)/2	N/A	X	Y	N/A	N/A
488/489	NUMERIC(X,Y)	X	N/A	X	Y	N/A	N/A
492/493	BIGINT	8	N/A	N/A	N/A	N/A	N/A
496/497	INTEGER	4	N/A	N/A	N/A	N/A	N/A
500/501	SMALLINT	2	N/A	N/A	N/A	N/A	N/A
504/505	Display Leading Sign (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A
904/905	ROWID (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A
908/909	VARBINARY(X)	X, where X is the length of the VARBINARY	X, where X is the length of the VARBINARY	N/A	N/A	65535	Allocated Length
912/913	BINARY(X)	X, where X is the length of the BINARY	X, where X is the length of the BINARY	N/A	N/A	65535	Allocated Length
916/917	Blob File reference (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A
920/921	Clob File reference (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A
924/925	DbClob File reference (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A
960/961	Blob Locator (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A
964/965	Clob Locator (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A
968/969	DbClob Locator (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A
972	Result Set Locator (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A
988/989	XML	2147483647	Dependent on the CCSID	N/A	N/A	Any CCSID except 65535	Allocated Length
996/997	DECFLOAT(16) DECFLOAT(34) DECFLOAT(7)(2)	8 16 N/A	N/A	N/A	N/A	N/A	N/A
2452/2453	XML Locator (Note 2)	N/A	N/A	N/A	N/A	N/A	N/A