Troubleshooting
Problem
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.
Resolving The Problem
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:
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.
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.
Notes:
1. For the decoded attribute, this is what was specified for the ALLOCATE keyword on CREATE or ALTER TABLE. For the encoded attribute, this indicates the space reserved in the fixed portion of the row. If the Allocated length is specified in the decoded attribute but not in the encoded attribute, no space will be reserved in the fixed portion of the row and all of it will be placed in the variable-length portion.
2. Data Type not supported or not applicable.
Additional resources:
Manuals:
Database overview
https://www.ibm.com/docs/en/i/7.4?topic=procedures-example-field-procedure-program
7.4 IBM i Database SQL programming for FieldProc programming details
https://www.ibm.com/docs/en/ssw_ibm_i_74/pdf/rbafypdf.pdf
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 |
Notes:
1. For the decoded attribute, this is what was specified for the ALLOCATE keyword on CREATE or ALTER TABLE. For the encoded attribute, this indicates the space reserved in the fixed portion of the row. If the Allocated length is specified in the decoded attribute but not in the encoded attribute, no space will be reserved in the fixed portion of the row and all of it will be placed in the variable-length portion.
2. Data Type not supported or not applicable.
Additional resources:
MCPress
https://www.mcpressonline.com/programming/rpg/db2-field-procedures-finally-support-conditional-masking
https://www.mcpressonline.com/programming/rpg/db2-field-procedures-finally-support-conditional-masking
https://www.mcpressonline.com//index.php?option=com_content&view=article&id=15145&catid=36&Itemid=1179
Manuals:
Database overview
https://www.ibm.com/docs/en/i/7.4?topic=procedures-example-field-procedure-program
7.4 IBM i Database SQL programming for FieldProc programming details
https://www.ibm.com/docs/en/ssw_ibm_i_74/pdf/rbafypdf.pdf
[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0"}]
Historical Number
595520195
Was this topic helpful?
Document Information
Modified date:
05 January 2023
UID
nas8N1011600