Masking Job Monitor, Negative Row Count, Calculating (KBA6686)

Investigation

• Rows in the UI might show -1 or 0 depending on the Masking Engine version. This indicates that the Row Count has not yet been calculated. 

# Cause 1
# Values at the End of Execution
+-------------+--------+------------------------+------------------------+--------+
|             |   DB   |           UI           |          Logs          |   DB   |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+
| IP + OTF    | Source | Rows | rpm | Remaining | Count | Input | Output | Target |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+
| Table count |      = |    0 |   0 |        -X |     = |     = |      = |      = |
| Table count |      = |   -1 |   0 |        -X |     = |     = |      = |      = |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+

Resolution

This is caused by the count query taking a long time to count the rows in the table. If the table is a 'scattered' heap table, then the count can be significantly delayed. Also, check for deadlocks in the database logs.

One solution would be to optimize the table (re-order the data or add a Clustered Index).

Investigation

• The same as with RC-1. The UI might show -1 or 0 depending on the version. This indicates that the Row Count did not arrive.

# Cause 2
# Values at the End of Execution
+-------------+--------+------------------------+------------------------+--------+
|             |   DB   |           UI           |          Logs          |   DB   |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+
| IP + OTF    | Source | Rows | rpm | Remaining | Count | Input | Output | Target |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+
| Table count |      = |    0 |   0 |        -X |     = |     = |      = |      = |
| Table count |      = |   -1 |   0 |        -X |     = |     = |      = |      = |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+

Resolution

The SELECT COUNT query can deadlock if a required object is also accessed by another query. This can be because the data in the table is a 'scattered' heap table and rows are read in a non-optimal way.

1. Check the database (error) logs for deadlock.
2. If this needs to be resolved - the solution could be to:
   • Optimize the table - using Shrink or Rebuild database commands.
   • Re-order the table.
   • Add a Clustered Index.

Note:

A fix for this is being investigated and may be available in a future release.

Investigation

# Cause 3
# Values at the End of Execution
+-------------+--------+------------------------+------------------------+--------+
|             |   DB   |           UI           |          Logs          |   DB   |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+
| IP          | Source | Rows | rpm | Remaining | Count | Input | Output | Target |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+
| Table count |      = |    = |   n |        -X |     = |    +X |     +X |     +X |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+

Resolution

To mask a column that is part of a Primary Key, please read KBA1776 about PK masking (link provided in the Related Articles section).

Investigation

# Cause 4
# Values at the End of Execution
+-------------+--------+------------------------+------------------------+--------+
|             |   DB   |           UI           |          Logs          |   DB   |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+
| IP          | Source | Rows | rpm | Remaining | Count | Input | Output | Target |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+
| Table count |      = |    = |   n |        -X |     = |    +X |     +X |      = |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+

Resolution

Drop the Clustered Index (as it could be part of the primary key, please read KBA1776 about PK masking (link provided in the Related Articles section)). It might be necessary to create a new Clustered Index and Logical Key.

Investigation

# Cause 5
# Values at the End of Execution
+-------------+--------+------------------------+------------------------+--------+
|             |   DB   |           UI           |          Logs          |   DB   |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+
| IP          | Source | Rows | rpm | Remaining | Count | Input | Output | Target |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+
| Table count |      = |    = |   n |        -X |     = |    +X |     +X |     +X |
+-------------+--------+------+-----+-----------+-------+-------+--------+--------+

Resolution

There should be no additional operation running while masking the data. Please check the table for:

• Triggers.
• SQL Scripts.
• External processes (including replications).

Investigation

• IP job will have the same number of records before and after masking but negative rows Remaining. 
• OTF job will have X additional number of records after masking. The same number will show up as negative Remaining. 

$ Cause 6
# Values at the End of Execution
+-------------------+--------+------+-----+-----------+-------+-------+--------+--------+
|                   |   DB   |           UI           |          Logs          |   DB   |
+-------------------+--------+------+-----+-----------+-------+-------+--------+--------+
|                   | Source | Rows | rpm | Remaining | Count | Input | Output | Target |
+-------------------+--------+------+-----+-----------+-------+-------+--------+--------+
| Table count (IP)  |      = |    = |   n |        -X |     = |    +X |     +X |      = |
| Table count (OTF) |      = |    = |   n |        -X |     = |    +X |     +X |     +X |
+-------------------+--------+------+-----+-----------+-------+-------+--------+--------+

Resolution - IP

The root cause is likely to be the Isolation Level (Dirty Reads). The SELECT statement read some records twice due to read-consistency issues on the database. 

As the issue is not understood, please raise a support case when encountering this issue.

Solutions to try:

1. Set the Isolation Level to Read Committed. 
2. Ensure the table is ordered:
   1. Create a new Clustered Index on a URI (any existing Clustered Index needs to be dropped).
   2. Optimize the table (Reorder).
3. Use On-The-Fly (OTF).

Comment: Do not use nolock on SELECT. 

Resolution - OTF

The issue here is not yet known. One possible solution could be to be cast the input value(s). The solution will depend on the algorithm used - for Secure Lookup, casting the value to HEX (using BINTOHEX) or HASH might resolve the issue.

Otherwise, please raise a support ticket with Delphix Support.

Investigation

The issue here is that the masking engine generates additional records. This can be seen by looking at the logs and if only the output has additional records this is the issue. 

# Cause 7
# Values at the End of Execution
+-------------------+--------+------+-----+-----------+-------+-------+--------+--------+
|                   |   DB   |           UI           |          Logs          |   DB   |
+-------------------+--------+------+-----+-----------+-------+-------+--------+--------+
|                   | Source | Rows | rpm | Remaining | Count | Input | Output | Target |
+-------------------+--------+------+-----+-----------+-------+-------+--------+--------+
| Table count (IP)  |      = |    = |   n |        -X |     = |     = |     +X |      = |
| Table count (OTF) |      = |    = |   n |        -X |     = |     = |     +X |     +X |
+-------------------+--------+------+-----+-----------+-------+-------+--------+--------+

Resolution

• If you are using Custom algorithms, please contact the author of the algorithm. 
• If you are using Out-of-the-Box algorithms, please raise a support ticket with Delphix Support.

Investigation

• x indicates the number of records on a small table.
• X indicates the number of records on a large table.
• y is a factor related to how many times the input is fetching the output before the job finishes. 

# Cause 8
# Values at the End of Execution
+------------------+--------+------+-----+-----------+-------+-------+--------+--------+
|                  |   DB   |           UI           |          Logs          |   DB   |
+------------------+--------+------+-----+-----------+-------+-------+--------+--------+
|                  | Source | Rows | rpm | Remaining | Count | Input | Output | Target |
+------------------+--------+------+-----+-----------+-------+-------+--------+--------+
| Table count (s)  |      x |    x |   n |         0 |     x |     x |      x |    x+x |
| Table count (l)  |      X |    X |   n |     X-y*X |     X |   y*X |    y*X |  y*X+X |
+------------------+--------+------+-----+-----------+-------+-------+--------+--------+

# s = Small table (few records)
# l = Large table (a large number of records)

Resolution

This issue is due to the Target being set to the Source. 

1. Change the Target and Source connectors to connect to the correct databases/instances.
2. Job Configuration - set Batch Mode to on (performance) and usually Truncate should be on.