Issue in Maskviews Script generated from Scrambled Extract in Subset

Document ID : KB000098172
Last Modified Date : 26/05/2018
Show Technical Document Details
Issue:
I see issues in the maskviews script generated out of Scrambled Extract in Subset. The database name is not picked up consistently due to which the table is not found.
Below is a sample:
 
use [Scramble]
GO
IF EXISTS(SELECT 1 FROM sys.views
WHERE object_id = OBJECT_ID('[dbo].[t_Node_v]'))
BEGIN
DROP VIEW [dbo].[t_Node_v]
END
GO
CREATE VIEW [dbo].[t_Node_v] as select Y1.rd_ref_value [node_pk],
L0.[node_ctry_fk] [node_ctry_fk],
L0.[node_Parent_Id] [node_Parent_Id],
L0.[node_Name] [node_Name],
L0.[node_Number] [node_Number],
L0.[node_Contact_Email] [node_Contact_Email],
L0.[node_Administrative_Email] [node_Administrative_Email],
L0.[node_Officer_Code] [node_Officer_Code],
L0.[node_Prime_Number] [node_Prime_Number],
L0.[node_Language] [node_Language],
L0.[node_Enumerated] [node_Enumerated],
L0.[node_TP_Server_Id] [node_TP_Server_Id],
L0.[node_TP_Unit_Id] [node_TP_Unit_Id],
L0.[node_TP_Parent_Unit_Id] [node_TP_Parent_Unit_Id],
L0.[node_Type] [node_Type],
L0.[node_Purge_Flag] [node_Purge_Flag],
Y1.rd_ref_value [node_usre_Created_fk],
Y1.rd_ref_value [node_usre_Updated_fk],
L0.[node_TP_Created] [node_TP_Created],
L0.[node_TP_Updated] [node_TP_Updated],
L0.[node_Active] [node_Active],
L0.[node_System_Comment] [node_System_Comment],
L0.[node_Record_Created] [node_Record_Created],
L0.[node_Record_Last_Updated] [node_Record_Last_Updated],
L0.[node_Active_Modified_Date] [node_Active_Modified_Date],
L0.[Node_Contact_Phone] [Node_Contact_Phone],
L0.[node_Corp_Parent_id] [node_Corp_Parent_id],
L0.[node_Bank_id] [node_Bank_id],
L0.[node_Client_Product_Code] [node_Client_Product_Code],
L0.[node_Cycle_Day] [node_Cycle_Day],
L0.[node_Emulator_User_Created_fk] [node_Emulator_User_Created_fk],
L0.[node_Emulator_User_Updated_fk] [node_Emulator_User_Updated_fk],
L0.[node_Unique_Number] [node_Unique_Number]
from [procard_centre_scrubbed].[dbo].[t_Node] L0,Scramble.dbo.gtsrc_reference_data Y1
WHERE Y1.rd_ref_id = 'CSPKID'
AND Y1.rd_index = (ABS(CHECKSUM(node_pk)) % 99998) + 1
AND (L0.node_bank_id =(SELECT L0.node_pk FROM [dbo].[t_Node] where L0.node_number = '6040')) UNION SELECT * FROM [procard_centre_scrubbed].dbo.[t_Node] L170 WHERE EXISTS (SELECT * FROM [procard_centre_scrubbed].[dbo].[t_User] L168 WHERE L168.[usre_pk] = L170.[node_usre_Created_fk] AND L168.[usre_pk] = L170.[node_usre_Updated_fk] and exists (SELECT * FROM [procard_centre_scrubbed].[dbo].[t_Node] L0 WHERE L0.[node_pk] = L168.[usre_node_fk] AND L0.node_bank_id =(SELECT L0.node_pk FROM [dbo].[t_Node] where L0.node_number = '6040'))) UNION SELECT * FROM [procard_centre_scrubbed].dbo.[t_Node] L171 WHERE EXISTS (SELECT * FROM [procard_centre_scrubbed].[dbo].[t_User] L168 WHERE L168.[usre_pk] = L171.[node_usre_Created_fk] and exists (SELECT * FROM [procard_centre_scrubbed].[dbo].[t_Node] L0 WHERE L0.[node_pk] = L168.[usre_node_fk] AND L0.node_bank_id =(SELECT L0.node_pk FROM [dbo].[t_Node] where L0.node_number = '6040'))) UNION SELECT * FROM [procard_centre_scrubbed].dbo.[t_Node] L172 WHERE EXISTS (SELECT * FROM [procard_centre_scrubbed].[dbo].[t_User] L168 WHERE L168.[usre_pk] = L172.[node_TP_Updated] and exists (SELECT * FROM [procard_centre_scrubbed].[dbo].[t_Node] L0 WHERE L0.[node_pk] = L168.[usre_node_fk] AND L0.node_bank_id =(SELECT L0.node_pk FROM [dbo].[t_Node] where L0.node_number = '6040'))) UNION SELECT * FROM [procard_centre_scrubbed].dbo.[t_Node] L436 WHERE EXISTS (SELECT * FROM [procard_centre_scrubbed].[dbo].[t_User] L168 WHERE L168.[usre_pk] = L436.[node_usre_Created_fk] and exists (SELECT * FROM [procard_centre_scrubbed].[dbo].[t_Node] L0 WHERE L0.[node_pk] = L168.[usre_node_fk] AND L0.node_bank_id =(SELECT L0.node_pk FROM [dbo].[t_Node] where L0.node_number = '6040'))) UNION SELECT * FROM [procard_centre_scrubbed].dbo.[t_Node] L493 WHERE EXISTS (SELECT * FROM [procard_centre_scrubbed].[dbo].[t_User] L168 WHERE L168.[usre_pk] = L493.[node_usre_Updated_fk] and exists (SELECT * FROM [procard_centre_scrubbed].[dbo].[t_Node] L0 WHERE L0.[node_pk] = L168.[usre_node_fk] AND L0.node_bank_id =(SELECT L0.node_pk FROM [dbo].[t_Node] where L0.node_number = '6040')))
GO

Error log is Changed database context to 'Scramble'.
Msg 208, Level 16, State 1, Server WEV-PCRD-DSQL05, Procedure t_Node_v, Line 38
Invalid object name 'dbo.t_Node'.

If you see the script above (from maskviews), [dbo].[t_node] and [procard_centre_scrubbed].[dbo].[t_node] are randomly used to refer to same table. The correct one should be [procard_centre_scrubbed].[dbo].[t_node] at all places.

This continues for all subsequent tables.
Cause:
Code needs to be fixed.
Resolution:
There is now a patch for this at http://www.grid-tools-downloads.com/downloads/CA/SUBSET23MAY2018.zip

The issue was twofold
1) Incorrect SQL was generated for masked tables that occur more than once in an extract (the UNION SQL)
2) Slightly incorrect driving table SQL

In your extract, the driving table SQL was
SELECT *
FROM [dbo].[t_Node]
WHERE [dbo].[t_Node].node_bank_id = (
SELECT [dbo].[t_Node].node_pk FROM [dbo].[t_Node] where [dbo].[t_Node].node_number = '6040')

please change this to
SELECT *
FROM [dbo].[t_Node]
WHERE [dbo].[t_Node].node_bank_id = (
SELECT [dbo].[t_Node].node_pk
FROM [TEST].[dbo].[t_Node] where [dbo].[t_Node].node_number = '6040')

in other words, because the masked views are created in the context of the database where your scramble components are installed (usually scramble), the table in the sub select MUST be fully qualified with your database name.
Substitute your database name for TEST.
Note, the outer query should NOT be qualified with a database name, Subset will qualify that automatically.

In summary: 

1) Download patch
2) Edit driving table SQL as above
3) Resave extract
4) Regenerate scripts.

This is a result of so many unions required to get the superset of the rows. I think you have 5 or 6 instances of t_account in your extract?

Things to consider
1) The deeper a table is in the extract, the more expensive it is to get the subset rows as it has to join on all the tables above it
2) Similarly, the more times a table appears in an extract, the more expensive it is to get the rows, if it appears multiple times and at a depth from driving table then it’s even more expensive

Try to prune your extract if possible to avoid 1 and 2. If this is not possible, consider running the insert statements instead of the extractandload, this would require the existence of a temp database next to source, after the inserts complete, you could do a straight BCP of all the tables (as they are already subsetted and masked)
Additional Information:

Video URL on how to open a Support Case - https://communities.ca.com/videos/5898-demo-how-to-open-a-support-case

You can download the latest version of TDM by following the directions in this document: https://support.ca.com/us/knowledge-base-articles.TEC1903942.html.   

To contact support, go to https://www.ca.com/us/services-support/ca-support/contact-support.html