On 8/30/2018 1:08 PM, James H. H. Lampert wrote:
Now suppose user JSMITH, who is not authorized to see the field at all,
changes a record in the file, and user JDOE, who is authorized to see
partially redacted contents, each change a record. In neither case do
they enter a new value for the field that has the FieldProc on it.
What happens to the value of that field?
When are field procedures invoked?
'For field-encoding, when a column value is to be encoded. Encoding
occurs for any value that:
Is inserted in the column by an SQL INSERT statement, SQL MERGE
statement, or native write operation.
Is changed by an SQL UPDATE statement, SQL MERGE statement, or
native update operation.
If the data needs to be copied and the target column has a field
procedure, it is possible that the field procedure may be invoked to
encode the copied data. Examples include the SQL statements ALTER TABLE
or CREATE TABLE (with a LIKE or as-result-table clause) and the CL
commands CPYF or 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 variable, constant, or join column is
encoded.
At CREATE or ALTER TABLE time for the DEFAULT value, if the column
has a field procedure.
If there are any after or read triggers, the field procedure is invoked
before any of these triggers. For before triggers, there may be multiple
invocations of the field procedure with encode and decode operations.
The number of calls to the field procedure depends on many factors
including the type of trigger and if the trigger changes the data in the
trigger buffer. The database manager will ensure that the field
procedure is called to encode the data that will be inserted into the
table.'
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/sqlp/rbafyfpwheninvoked.htm
There is wiggle room with understanding what '...value that is
changed...' might mean. I don't see how this scenario will work:
Current encoded value: ABCDEF
Attempted update: 123456
How could Db2 know that the FieldProc will encode 123456 as ABCDEF?
That, the attempted update is, in fact, not changing the value?
It seems to me that the FieldProc /must/ be invoked for any update that
includes the column. We humans have a logical concept of 'update'
(Sarah didn't press any keys except F3=Exit, so she didn't change
anything) that differs from the database's action during an UPDATE
operation.
I'd ask the question on the Feedback section of the manual and get an
official response.
As an Amazon Associate we earn from qualifying purchases.