Skip to main content
Delphix

Rule Set Feature: Custom SQL (KBA4455)

 

KBA

KBA# 4455

At a Glance   

Versions: Applicable Delphix Masking versions: 4.x, 5.0.x, 5.1.x, 5.2.x, 5.3.x
Description: Used to Filter and Alter (including casting) data before masking using SQL statement.
Location: UI: Rule Set > Edit Table Settings > Custom SQL
API: [not available]
Configuration: The following parameter is used to configure this feature: 
  • Custom SQL: Complete SELECT SQL statement. 
RS Indicator: Table Indicator in the Rule Set: 'SQL'.

Usage   

This feature is used to specifically define the SQL statement used to read the data from the database table. The columns needed in the statement are the URI and the masked columns. Data types and values can be altered (as in casting). 

Make sure that only include columns used in masking as any other field will consume memory. 

In earlier versions, it is critical that the Custom SQL text entered in the popup box not end with or contain any newline characters.  This will cause odd behavior with the GUI and will require a support interaction to clear up.

URI is the Unique Row Identifier, which is required to update the masked row in In-Place masking. This URI is usually a Primary Key or an Index of some kind. At times it is though required to manually set this URI or to define a key other than the Primary Key.

 

Warning

Warning:

Do not include fields that are not URI or Masked as these will consume additional memory.

 

UI Examples  

In the examples below have the following names been used: 

  • Rule Set: 'Rule Set Features RS'
  • Original Tabe Name: 'Main_Table'

Rule Set page  

Please note the 'SQL' indicator when the table has a Custom SQL defined. 

UI RS - SQL.png

Edit Table Settings popup  

This shows an example of a Custom SQL. In this case, it is the engine generated SQL. 
UI RS - Popup - Custom SQL .png

No Invalid input error  

Note: there is no verification if the Custom SQL is valid or not. The product assumes the SQL is valid and that it can be used in the Masking Job. 

Related Articles  

The following articles may provide more information or related information to this article: