In a recent post, we voiced our concerns about the potential for application error when taking advantage of the masking support related to RCAC in V7.2 of DB2 for i.
One of our concerns was about the potential loss of data when updating a row (aka record) that contained a masked column (aka field) value. For example, a credit card number might be masked like XXXX-XXX-XXX-1057 when retrieved in an RPG program. When that row is updated by the program, it’s quite possible that the actual value of the credit card number might be lost (i.e., overwritten by the masked value as seen by the RPG program).
Our friend, Kent Milligan, commented on the blog that there was support added to DB2 for i to allow the database itself to prevent data loss in that situation. Since we don’t yet have a 7.2 system to “play with” ourselves, we haven’t been able to pursue that and/or other potential solutions for that particular issue. We did, however, recently see an article Kent wrote on the subject.
The basic idea is that DB2 for i has implemented an enhanced capability for check constraints. Typically, if an “ordinary” check constraint were in place, an update to the row would be prevented if the check constraint were violated. That basic support could be used to prevent accidental loss of data by preventing updates if, for example, the first 12 characters of the credit card number were ‘XXXX-XXX-XXX’ (i.e., the mask value). But the problem is that we wouldn’t be able to update any other values in the row either if we had a masked credit card value in our program.
So the new support has added syntax on the check constraint “On Update Violation Preserve <column-name>.” This enhanced support means that if DB2 detects the specified value (i.e., the mask value), then updates will be allowed for other columns in the row but the original unmasked value for the credit card number will be retained. Whew!
Note that it will be necessary to set up this new check constraint option in addition to setting up the RCAC support to mask the column value. Also it doesn’t actually detect that it’s a mask put in by DB2; it only checks for a specific value. So potentially there could be other uses for this support beyond dealing with the RCAC-masked columns—although we can’t think of any.
We encourage you to learn more about this from Kent’s article. This new support doesn’t do away with all our concerns about masked values but at least, if implemented correctly, it would appear to avoid the worst of the potential nightmare scenarios! It may not be the solution we’d have preferred but it should help to prevent many of the mistakes we feared.