Troubleshoot MASKING_GENERATED_IDENTITY_TMP (KBA8902)

Order of events

The order of these transformations differs between the type of database.

<Start Job Action>
1. Optional: Disable Constraints
2. Optional: Drop Indexes
3. Optional: Disable Triggers
4. CREATE IDENTITY TRANSFORMATION
5. Optional: PRESCRIPT TRANSFORMATION
<Start Masking/Tokenization>

<Start Job Action>
1. CREATE IDENTITY TRANSFORMATION
2. Optional: DROP INDEX TRANSFORMATION
3. Optional: PRESCRIPT TRANSFORMATION
     a. Custom PreScript SQL
     b. Disable Triggers
     c. Disable Constraints
<Start Masking/Tokenization>

Canceled or terminated jobs

Should the job be canceled (killed) or crashed - the IDENTITY columns and the indexes created will be left on the database.   

Order of events

The order of these transformations differs between the type of database.

<Start Job Action>
1. Optional: Enable Triggers
2. Optional: Create Indexes
3. Optional: Enable Constraints
4. CREATE IDENTITY TRANSFORMATION
5. Optional: POSTSCRIPT TRANSFORMATION
<Start Masking/Tokenization>

<Start Job Action>
1. Optional: POSTSCRIPT TRANSFORMATION
     a. Custom PostScript SQL
     b. Enable Triggers
     c. Enable Constraints
2. Optional: CREATE INDEX TRANSFORMATION
3. DROP IDENTITY TRANSFORMATION
<Start Masking/Tokenization>

The i_MGIT index

SELECT s.name as "Schema", t.name as "Table", c.name as "Column", i.name as "Index"
FROM sys.indexes i, sys.index_columns ic, sys.columns c, sys.tables t, sys.schemas s
WHERE
  i.object_id = ic.object_id AND
  i.index_id = ic.index_id AND
  ic.object_id = c.object_id AND
  ic.column_id = c.column_id AND
  i.object_id = t.object_id AND
  t.schema_id = s.schema_id AND
  i.name = i_MASKING_GENERATED_IDENTITY_TMP;

The MGIT column

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE 
   COLUMN_NAME = MASKING_GENERATED_IDENTITY_TMP;

The i_MGIT index

SELECT su.name as "Schema", so.name as "Table", si.name as "Index"
FROM sysobjects so, sysusers su, sysindexes si
WHERE 
  so.uid  = su.uid AND
  si.id = so.id AND
  si.name = i_MASKING_GENERATED_IDENTITY_TMP

The MGIT column

SELECT su.name "Schema", so.name "Table", sc.name "Column"
FROM sysobjects so, sysusers su, syscolumns sc
WHERE
  so.uid  = su.uid AND
  sc.id = so.id AND
  sc.name = MASKING_GENERATED_IDENTITY_TMP

The i_MGIT index

Note: The i_MGIT index has the table name concatenated to the end of the index name.

SELECT n.nspname "Schema", c.relname "Index_Table"
FROM PG_CLASS C JOIN PG_NAMESPACE N ON N.OID = C.RELNAMESPACE
WHERE C.RELNAME ~ i_MASKING_GENERATED_IDENTITY_TMP;

The MGIT column

SELECT table_catalog, table_schema, table_name, column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME=MASKING_GENERATED_IDENTITY_TMP;

Create IDENTITY (MS SQL Server)

The create statement is defined in the following Job XML file:

KETTLE_CREATE_IDENTITY_TRANSFORMATION_[job_id].xml

The create statement looks like this (example):

IF NOT EXISTS (
  SELECT 1 
  FROM INFORMATION_SCHEMA.COLUMNS  
  WHERE 
    TABLE_SCHEMA = 'dbo' AND 
    TABLE_NAME = 'tbl_mask' AND 
    COLUMN_NAME = MASKING_GENERATED_IDENTITY_TMP)
BEGIN
   ALTER TABLE [Test].[dbo].[tbl_mask] ADD "MASKING_GENERATED_IDENTITY_TMP" INT IDENTITY(1,1)
END;


IF NOT EXISTS (
  SELECT 1 
  FROM sys.indexes i, sys.index_columns ic, sys.columns c, sys.tables t, sys.schemas s
  WHERE
    i.object_id = ic.object_id AND
    i.index_id = ic.index_id AND
    ic.object_id = c.object_id AND
    ic.column_id = c.column_id AND
    i.object_id = t.object_id AND
    t.schema_id = s.schema_id AND
    s.name = 'dbo' AND
    t.name = 'tbl_mask' AND
    c.name = MASKING_GENERATED_IDENTITY_TMP  AND
    i.name = i_MASKING_GENERATED_IDENTITY_TMP)
BEGIN
   CREATE INDEX i_MASKING_GENERATED_IDENTITY_TMP ON [Test].[dbo].[tbl_mask] ("MASKING_GENERATED_IDENTITY_TMP")
END;

Drop IDENTITY (MS SQL Server)

The drop statement is defined in the following Job XML file:

KETTLE_DROP_IDENTITY_TRANSFORMATION_[job_id].xml

The create statement looks like this (example):

IF EXISTS (
  SELECT 1 
  FROM sys.indexes i, sys.index_columns ic, sys.columns c, sys.tables t, sys.schemas s
  WHERE
    i.object_id = ic.object_id AND
    i.index_id = ic.index_id AND
    ic.object_id = c.object_id AND
    ic.column_id = c.column_id AND
    i.object_id = t.object_id AND
    t.schema_id = s.schema_id AND
    s.name = 'dbo' AND
    t.name = 'tbl_mask' AND
    c.name = MASKING_GENERATED_IDENTITY_TMP  AND
    i.name = i_MASKING_GENERATED_IDENTITY_TMP)
BEGIN
    DROP INDEX i_MASKING_GENERATED_IDENTITY_TMP on  [Test].[dbo].[tbl_mask]
END;


IF EXISTS (
  SELECT 1 
  FROM INFORMATION_SCHEMA.COLUMNS  
  WHERE 
    TABLE_SCHEMA = 'dbo' AND 
    TABLE_NAME = 'tbl_mask' AND 
    COLUMN_NAME = MASKING_GENERATED_IDENTITY_TMP)
BEGIN
   ALTER TABLE [Test].[dbo].[tbl_mask] DROP COLUMN  [MASKING_GENERATED_IDENTITY_TMP]
END;

Finding errors in INFO.log for specific JOB 

This limits the search for errors to a specific job and execution (change xx and yy to the job and execution as required). 

grep -E "E=1|Caused by|Job with execution.*submitted|Unable to load the job|Couldn't execute|Failed|updating batch|for job|Masking Engine|MASKING_GENERATED" info.log | grep "JOB_ID_xx_yy"

Finding errors in INFO.log for specific JOB 

This limits the search for errors to a specific job and execution (change xx and yy to the job and execution as required). 

Select-String "E=1|ORA-|Caused by|Job with execution.*submitted|Unable to load the job|Couldn't execute|Failed|updating batch|for job|Masking Engine|MASKING_GENERATED" info.log | Select-String "JOB_ID_xx_yy"