Skip to main content
Delphix

Troubleshoot DMS_ROW_ID in Masking Jobs (KBA6311)

 

KBA

KBA# 6311

 

At a Glance 

Summary: This page describes what 'DMS_ROW_ID' is, when it is used, possible issues, and resolutions.
What is: 'DMS_ROW_ID' is an indexed IDENTITY column created by the Masking Engine and the index is called 'i_DMS_ROW_ID'. This column is created if there is no 1. IDENTITY, 2. Primary Key, or 3. Logical Key on the masked table. 

Should there be an IDENTITY column but no index, the engine will create the index 'i_DMS_ROW_ID'.

The 'DMS_ROW_ID' and 'i_DMS_ROW_ID' are removed at the end of the masking operation. 
Applies to:  The IDENTITY feature, which creates 'DMS_ROW_ID' is supported on the following databases: 
  • MS SQL Server.
  • SAP ASE (Sybase).
  • MySQL.
  • PostgreSQL.

Notes:

  • Oracle has the same feature but will automatically use 'ROWID' (no need to create 'DMS_ROW_ID').
  • For PostgreSQL, it is possible to bypass 'DMS_ROW_ID' by using 'ctid' as a Logical Key
Possible Issues: Issues that can surface with 'DMS_ROW_ID' are:
  • During the creation of the IDENTITY column 'DMS_ROW_ID':
    • This can take time and will require resources.
       
  • When a job fails, it can leave the 'DMS_ROW_ID' on the table.
    • Resolve why the job failed and the job can be restarted - the 'DMS_ROW_ID' will be reused.
       
  • The 'DMS_ROW_ID' can appear in the Rule Set.
    • Make sure the Rule Set is not Refreshed. 
    • And make sure that 'DMS_ROW_ID' has not been added to the Rule Set.
       
  • The existing IDENTITYPrimary Key, or Logical Key is masked:
    • Please see links to referenced KBAs at the end of the page.
       
  • The appropriate permissions are lacking for the user to create and drop the 'DMS_ROW_ID' column and index.
Solution: Follow these steps to resolve these issues:

If this is an existing job, it is recommended to rewind/refresh the database as data could be partially masked.
  1. On the Database. check for 'i_DMS_ROW_ID' indexes and for 'DMS_ROW_ID' columns and remove these. 
  2. Make sure there are no 'DMS_ROW_ID' columns in the Rule Set. If there are, refresh the Rule Set.
  3. Make sure there are no 'DMS_ROW_ID' in Custom SQL, Filter, or Logical Key.
  4. If still an issue, check that it is possible to create an Identity column on the Database. 
  5. If still an issue and if the PK or the Identity column is masked see the steps below. 
Notes: Never manually create 'DMS_ROW_ID'. This will result in errors.

The logs can sometimes list an escaped version, shown as 'DMS\_ROW\_ID'.
Improved process: From version 6.0.0.0, this has been improved and will check if the 'DMS_ROW_ID' already exists. This will resolve some issues automatically. 

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 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0

5.3

5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

4.2

4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1

4.1

4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

 

What is DMS_ROW_ID?

The 'DMS_ROW_ID' is a temporary column created by the Masking Engine on the database if there is no Unique Row Identifier (URI) on an In-Place masking job.

In order to mask a table using In-Place (IP) the table needs a column that uniquely identifies each row - a Unique Row Identifier (URI). If a URI is missing, the Masking Engine will create 'DMS_ROW_ID' (as an Identity column) and create an index called 'i_DMS_ROW_ID'.

When the masking job has finished, the Masking Engine will remove the index and the column.

When is it created?

The 'DMS_ROW_ID' is created if there is no 1. IDENTITY, 2. Primary Key (PK), or 3. Logical Key (LK) on the table.

The creation and using the 'DMS_ROW_ID' in the masking job is automatic and no actions are required. 

When is it not created?

The 'DMS_ROW_ID' is not created when a Primary Key or an Identity column is present or a Logical Key has been specified in the Rule Set

So what to do if you need to mask a Primary Key or an Identity column?

See the KBA articles (links at the bottom of the page): 

  • Masking Primary Key - see KBA1776.
  • Masking IDENTITY column - see KBA7266.

When is it removed?

The 'DMS_ROW_ID' and the index 'i_DMS_ROW_ID' are removed from the database after the Masking operation has been completed.

Should this operation fail (due to a severe error while masking) this can cause the 'DMS_ROW_ID' to be left on the database. Version 6 onwards will automatically reuse the IDENTITY column when the job is restarted. On older Masking Engine versions, manual interventions are needed to manually remove 'DMS_ROW_ID' on the table. This article will describe more details.

Solutions

The 'DMS_ROW_ID' is created by the Masking Engine. Errors usually stem from:

  • Database Permission creating 'DMS_ROW_ID'.
  • 'DMS_ROW_ID' has accidentally been left on the database.
  • 'DMS_ROW_ID' has accidentally been included in the Rule Set.
  • A manual process interfering with the creation and use of 'DMS_ROW_ID' and its index.

Steps - Existing Job

If this is an existing job, the data in the table could have been partially masked, hence the best way forward is to rewind/refresh that database.

  1. On the Database:
    • For a vDB, rewind.
    • For a physical database, refresh the database.
  2. See steps 1 to 5 below (section 'Steps - Creating/Modifying the Job').

Steps - Creating/Modifying the Job

When creating or modifying the Job the essential part is to not have 'DMS_ROW_ID' in the Rule Set, Inventory, or in the table on the database. It is a temporary column, created and managed by the Masking Engine. 

If there are an error and 'DMS_ROW_ID' is in the error message: 

  1. On the Database, check for 'i_DMS_ROW_ID' indexes and for 'DMS_ROW_ID' columns. If found, remove these. 
  2. Make sure there are no 'DMS_ROW_ID' columns in the Inventory. If there are, refresh the Rule Set.
  3. Make sure 'DMS_ROW_ID' is not in any Custom SQL, Filter, or Logical Key.
  4. If the issue persists, check that it is possible to create an IDENTITY column on the Database. 
  5. If the issue continues to persists and if the PK or the Identity column is masked, see the steps below. 

Steps - Permission Errors

For permission errors, ensure the credentials used in the Masking Connector has sufficient privileges. See details about required permissions in the document linked at the bottom of the page. 

Steps - Using Custom SQL

If Custom SQL needs to be used on a Heap Table (a table that will need 'DMS_ROW_ID') the only way to currently do this is to manually create an IDENTITY column and Refresh the Rule Set before masking. 

 

Error Examples and Troubleshooting

Errors related to 'DMS_ROW_ID' can appear in all types of jobs and at different stages: 

  1. Profiling (only Data Level Profiling)
    • Job Execution
       
  2. Masking and Tokenization/Re-Identify 
    • Job Creation
    • Execute Create Identity
    • Job Execution
    • Execute Drop Identity

Example - Profiling

Issue related to 'DMS_ROW_ID' that only affects Data Level Profiling

If there is an error related to 'DMS_ROW_ID", the error will only happen at Job Execution.

Note

Note:

Since the Masking Engine does not need to access the database to create a Profile Job there are no Creation errors.

 

Error: "Couldn't find field"

If the profiled Rule Set incorrectly has 'DMS_ROW_ID' as a column in one of the tables (and 'DMS_ROW_ID' isn't in the database), then an error similar to the one below can be seen.

 

The example below is from MS SQL Server. 

...ProfilerThread - [JOB_ID_xx_yy] 2021/03/29 04:43:11 - Select Values.0 - ERROR : Couldn't find field 'DMS_ROW_ID' in row!
...ProfilerThread - [JOB_ID_xx_yy] 2021/03/29 04:43:11 - Select Values.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
Resolution

To resolve this issue:

  1. On the Database, check for 'i_DMS_ROW_ID' indexes and for 'DMS_ROW_ID' columns. If found, remove these.  
  2. On the Engine, check Custom SQL for 'DMS_ROW_ID' - remove if found. 
  3. After removal - Refresh the Rule Set.
  4. Run profiling. 

Examples - Masking and Tokenization

The errors related to 'DMS_ROW_ID' are the same on Masking and on Tokenization/Re-Identification.

Most of the errors happen when a table is masked. The error can also happen when the job is created, the 'DMS_ROW_ID' is created, and when the 'DMS_ROW_ID' is dropped. 

 

The table below shows what happens when 'DMS_ROW_ID' is in the table, Inventory, Custom SQL, or in the Logical Key (LK).

  • 'Status': 'Success' has two colors - green and amber:
    • Green: indicates operation completed as intended.
    • Amber: indicates that the job will succeed but there is an undetected issue.
  • 'With' indicates that the Custom SQL has 'DMS_ROW_ID'.
  • 'Without' indicates there is a Custom SQL but without 'DMS_ROW_ID'.
  • 'Create DMS' stands for 'Create DMS_ROW_ID' and 'n.o.p' indicates "No Operation".
  • 'Drop DMS' stands for 'Drop DMS_ROW_ID'. 

KBA6311 - DMS_ROW_ID Error Use Cases v4.png

Creation: "Error while fetching"

When a masking job starts, the first action is to create the job. In this stage, the engine collects details about tables and columns to be masked. In this case, the error will happen if the Custom SQL in the Rule Set has 'DMS_ROW_ID' as a column in one of the tables.

This error happens in the Job Creation stage. 

 

The error message looks similar to this (example from SQL Server):

... ERROR c.dmsuite.common.utils.DatabaseUtil - Error while fetching Column List ::
java.sql.SQLException: Invalid column name 'DMS_ROW_ID'.
Resolution

To resolve this: 

  1. Check the Rule Set and delete the Custom SQL.
  2. It can be good to also check the database for 'DMS_ROW_ID' and refresh the Rule Set. 

Mask: "Invalid column name"

This happens because the SELECT reading the data has 'DMS_ROW_ID' in it and 'DMS_ROW_ID' isn't in the table. The reason the 'DMS_ROW_ID' was not created is that it is in the Rule Set - either as a Logical Key or in the Inventory.  

This error happens in the Job Execution stage. 

 

There is likely an error in the Job Creation similar to the one below ('DMS_ROW_ID' is escaped in this example):

ERROR c.d.d.DataPlatform - Failed to retrieve the column type for table=..., column=DMS\_ROW\_ID

The error that causes the job to fail (example from SQL Server): 

[JOB_ID_xx_yy] [date] - Table input.0 - SELECT "DMS_ROW_ID", ... FROM ...
[JOB_ID_xx_yy] [date] - Table input.0 - Invalid column name 'DMS_ROW_ID'.
Resolution

To resolve this: 

  1. Check the Rule Set, check the Logical Key, and remove 'DMS_ROW_ID'.
  2. Refresh the Rule Set.

Mask: "Couldn't find field"

This happens because the Logical Key has defined 'DMS_ROW_ID' and the job expects it to be present but the Custom SQL does not have 'DMS_ROW_ID'.  

 

The error message looks similar to this (example from SQL Server):

[JOB_ID_xx_yy] [date] - Select values.0 - ERROR (...): Couldn't find field 'DMS_ROW_ID' in row!
Resolution

To resolve this: 

  1. Check the Rule Set:
    1. Check the Logical Key, and remove 'DMS_ROW_ID'.
    2. Delete the Custom SQL.
  2. Refresh the Rule Set.

Mask: "Field DMS_ROW_ID is required"

This happens because a Custom SQL has been specified but it does not contain 'DMS_ROW_ID' which is needed to Update the masked row. 

 

The error message looks similar to this (example from SQL Server):

[JOB_ID_xx_yy] [date] - DelphixTableUpdate.0 - Field [DMS_ROW_ID] is required and couldn't be found!
Resolution

To resolve this: 

  1. Check the Rule Set:
    1. Delete the Custom SQL.
  2. Refresh the Rule Set.

Examples - Execute Create Identity

These are examples of errors when creating the IDENTITY column 'DMS_ROW_ID'. 

Create: "Cannot find the object"

This error happens if the masked table does not exist on the database or does not have appropriate permissions. 

 

An error message similar to this is shown (example from SQL Server):

ERROR c.d.common.utils.MonitorJobUtil - error :
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'Tbl_xyz'.

The job then fails with an error similar to:

[JOB_ID_xx_yy] - Execute_Create_Identity.0 - Cannot find the object "Tbl_xyz" because it does not exist or you do not have permissions.
Resolution
  1. The first step is to check if the table is on the database.
    • If the table should be masked, verify why the table is not on the database. 
    • If this table should not be masked anymore, then remove the table from the Rule Set
  2. Check that sufficient permissions are granted to the Masking Connector user. 

Create: "Column names must be unique"

Prior to Masking Engine version 6.0, it could be that the 'DMS_ROW_ID' was left on the table and rerunning the job fails when the Masking Engine tries to create 'DMS_ROW_ID', as it is already defined in the table.

 

SQL Server has an error message similar to this:

[JOB_ID_xx_yy] ... Column names in each table must be unique. Column name 'DMS_ROW_ID' in table '...' is specified more than once.

MySQL has an error similar to this: 

[JOB_ID_xx_yy] ... Duplicate column name 'DMS_ROW_ID'.
Resolution - Upgrade

The best resolution is to upgrade to the latest version of the Masking Engine. This is automatically resolved from version 6 forward.

Resolution - Remove DMS_ROW_ID

If an upgrade is not possible, then:

  1. On the Database, check for 'i_DMS_ROW_ID' indexes and for 'DMS_ROW_ID' columns. If found, remove these.  
  2. On the Engine, check Rule Set and the Inventory for 'DMS_ROW_ID' - remove if found. 
  3. After removal - Refresh the Rule Set.
  4. Run the masking job. 

Create: "The EXECUTE permission was denied"

This error happens when the user does not have permission to the EXECUTE SQL command.

[JOB_ID_xx_yy] - Execute_Create_Identity.0 - The EXECUTE permission was denied on the object ...
Resolution
  • Make sure the Masking Connector user has sufficient privileges. 

 

Create: "Multiple IDENTITY columns specified"

A table can only have one Identity column. This error happens when there is already an IDENTITY column on the table and the masking Engine tries to create a 'DMS_ROW_ID', which is an IDENTITY column. 

The error happens in the transformation 'Execute_Create_Identity'.

 

The error message looks similar to this (example from SQL Server):

[JOB_ID_xx_yy] [date] - Execute_Create_Identity.0 - Multiple identity columns specified for table '...'. Only one identity column per table is allowed.
Resolution

The main solution is to:

  1. On the Database, verify that there is an IDENTITY column on the table. 
  2. For consistency, check Rule Set and the Inventory on the engine for 'DMS_ROW_ID' - remove if found. 
  3. Refresh the Rule Set.
  4. Make sure the IDENTITY column is shown with and tagged with 'ID' in the Inventory. 
  5. Run the masking job. 
Resolution - if the IDENTITY column is masked

If the original Identity column is masked, please review KBA7266 (see link at the bottom of this page).

 

Note

Note:

The IDENTITY property of the masked column needs to be dropped or removed. In order to do that and mask the data, the data might need to be recreated in a new column. After masking, it might not be possible to recreate the IDENTITY property. The reason for this is that an identity column is incrementally auto-generated by the database. There are some database dependent rules and exceptions. Please consult the database documentation for your version for exact details. 

 

Example - Execute Drop Identity

Drop: "ALTER TABLE ... cannot be done"

When the 'DMS_ROW_ID' is dropped, it might be required to perform some additional operations which require permissions or features turned on.

 

The example below is from SAP ASE (Sybase):

  • The issue here is that the operation performs a 'data copy', which must have select into/bulkcopy/pllsort turned on.
[JOB_ID_xx_yy] [date] - Execute_Drop_Identity.0 - Neither the 'select into' nor the 'full logging for alter table' database options are enabled for database '...'. ALTER TABLE with data copy cannot be done.
Resolution

To resolve this: 

  1. Check the error message and related database documentation.
    • For Sybase, check: 'Altering existing tables' and 'Data copying' for your database version.
  2. Resolve and run the job.

 

 

Related Articles

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