At a Glance
|Description:||This KBA describes possible issues and errors when data is masked to a string with characters that are not valid due to the character set in the file or database column storing the masked value.
This issue can be hard to detect and it can also be silent (as with Oracle and MS SQL Server).
|Applicable Versions:||This KBA applies to all known Continuous Compliance Engine versions. The issue is not bound to any version but to how character sets are handled.|
|Issue 1: Error 1||Character conversion errors.|
|Issue 2: Error 2||Masked value too large for the column.|
|Issue 3: Silent||Oracle and SQL Server will convert some characters automatically. This might cause issues with uniqueness when masking and with Re-Identify when using Tokenization.|
|Algorithms:||All new algorithms (new Framework) are UTF-8 internally on the Continuous Compliance Engine.|
|Resolution:||Ensure that the Characters defined in the Algorithm are valid for the Character Set on the masked database column or masked file. If not - remove unsupported characters from the algorithm.|
|Recommendation:||It is recommended to create your own algorithm using the built-in framework. The algorithm will then be created based on your specific requirements. The out of the box (OOTB) algorithms are created to demonstrate capabilities.
For example, the built-in Character Mapping (dlpx-core:CM Alpha-Numeric) has a large set of extended Unicode characters which might not work on all character sets.
Applicable Delphix Versions
- Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases 6.0 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199
188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199
188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206
220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11
18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168, 22.214.171.124, 126.96.36.199
The solution to these issues is changing the algorithm so the algorithm masks to a supported set of characters.
- Go to the database and get the Character Set.
- Go to the algorithm with the issue and:
- Remove any unsupported characters; or
- Recreate the Algorithm ensuring lookup values do not have unsupported characters; or
- For a Custom Algorithm, please contact the author.
The root cause here is that the masked value contains a character that is not supported. This can come from Character Mappings that includes a character in the Character Group that is not supported. It can also be an unsupported character in a Secure Lookup or Mapping Algorithm.
Since different locals accept different characters the only solution is to create an algorithm that is correct for a specific local. Even US names are not immune from this issue where some names contain unsupported values. To see the extent of this - Google US town names that have diacritics.
There are three different types of issues identified related to the character set.
- Character conversion errors - the masked value cannot be converted into the database/file character set.
- Silent conversion - the unsupported character is converted into a supported character which causes referential integrity or re-identification errors.
- Value too long errors - the masked value gets an extra character and the value is too long for the field/column.
Below are some examples of errors. See above for Resolution.
Sybase is known to throw an error if a character is not matching the character set.
SAP ASE (Sybase)
On Sybase a character conversion issue usually results in the following error:
[JOB_ID_xx_yy] - DelphixTableUpdate.0 - JZ0BE: BatchUpdateException: Error occurred while executing batch statement: Error converting characters into server's character set. Some character(s) could not be converted. ... [JOB_ID_xx_yy] - DelphixTableUpdate.0 - Next Exception: SQLState( ZZZZZ) ErrorCode(2402)
Oracle and SQL Server are known to silently convert the unsupported character to a similar character, a '?', or similar.
This might mask a value in a way that causes duplicates, referential integrity errors, re-identification errors, or other. The main issue here is that it is silent and the error/issue might be identified long after the data is masked.
For example (this depends on LNS settings), Oracle might convert the following:
Ū > U ₨ > ?
For SQL Server the following is an example (once again - this depends on code page settings):
Ū > U ₨ > ?
In some instances, the new masked character can be converted and contains extra characters (up to 3 characters and 9 bytes have been noted on Oracle). If the data type is exactly defined for the value, this could cause the value to be too large for the column (as seen in the examples below).
[JOB_ID_xx_yy] - DelphixTableUpdate.0 - ORA-12899: value too large for column "..." (actual: x, maximum: y)
[JOB_ID_xx_yy] - DelphixTableUpdate.0 - Next Exception: SQLState( 22001) ErrorCode(-404)
To troubleshoot this issue, begin with the following:
- The column Data Type.
- The Character Set.
- Check if there are any errors.
- Check all characters in the algorithm:
- See below for known characters in out of the box (OOTB) algorithms.
- Check characters in the character mapping algorithms.
- Check source file for Mapping and Secure Lookup algorithms.
- Contact the author of Custom Algorithms.
The following statement is found on Oracle's docs site:
Because Java works internally in Unicode, the client character set is always set to Unicode.
Continuous Compliance Engine
Alpha-Numeric Character Mapping OOTB
Alpha-Numeric Character Mapping has the following extended characters defined in the OOTB algorithm: