Test Data Manager

Tech Tip: Subset: Error running sql : select count(*) from gtsrc_reference_data where rd_ref_id =rstnames.txt and No values for seed data X found in gtsrc_reference_data

  • 1.  Tech Tip: Subset: Error running sql : select count(*) from gtsrc_reference_data where rd_ref_id =rstnames.txt and No values for seed data X found in gtsrc_reference_data

    Posted Nov 08, 2017 11:15 PM

    This document can also be viewed in our TDM knowledge base here: https://support.ca.com/us/knowledge-base-articles.TEC1630578.html 

    Issue:

    When Subset is trying to generate scripts for the 'Build Windows DataPump Masked Export' option, we are receiving the following error: 
    Error running sql : select count(*) from gtsrc_reference_data where rd_ref_id =rstnames.txt 

     

    We also see these other messages: 

    1. Create Scramble Extract 
    No values for seed data NAMEGERMANLAST found in gtsrc_reference_data 

     

    2. Get Seed Data Category Count 
    Error running SQLselect count(*) from gtsrc_reference_data where rd_ref_id = "NAME- FEMALE INDIAN FIRST' 

     

    3. No rowcount found for seed data category:'NAME- FEMALE INDIAN FIRST' 

     

    4. Build Windows DataPump Masked Export 
    The Following Extract Scripts Were Created 
    exportdp.bat 
    importdp.bat 

    Environment:
    CA Test Data Manager (TDM)- Subset
    Cause:

    If you have created a Transformation Map of type 'SDM' and using this to try and generate Oracle scripts in Subset, you will receive an error. If you create a Transformation Map of type Oracle, you will not see references to seed table files as *.txt files are only used for Fast Data Masker.

     

    When generating masking scripts it is important that the correct type of Transformation Map is used. For example Oracle for Oracle scripts, and not SDM or Teradata. In TDM versions 4.2+, the generator will test the correct type of Transformation Map when selected as part of the first step in generating the scripts to avoid this issue. 

     

    This issue can also occur when the the Scramble database is present, but the synonyms to it are not. The SQL to the seed table does not qualify it with a schema name, so this will only work if synonyms are present. 

    Resolution:

    To resolve this, you will need to create the correct type of Transformation Map in Datamaker. 

     

    1. On the 'Transformation Maps' window in Datamaker, click on the + icon to create a new map. 
    2. Under DBMS, you would choose 'Oracle' (versus SDM) so that you get the correct list of functions needed in your scripts. 
    - If you choose SDM, you will get a whole different list of functions. You will see a list of text files under 'Transformation' drop down because it is going to need to reference text files. 
    - If you choose Oracle, you will see just a list of functions under the 'Transformation' drop down list.
    3. Retest the scenario in Subset.

     

    If you are still seeing an issue, and you have the Scramble database installed, the synonyms may be missing. To resolve this, you will need to login as an Oracle database administrator and issue the following commands:


    GRANT SELECT,UPDATE,DELETE,INSERT ON SCRAMBLE.GTSRC_CONTROL_STEPS TO PUBLIC; 
    GRANT SELECT,UPDATE,DELETE,INSERT ON SCRAMBLE.GTSRC_CONTROL TO PUBLIC; 
    GRANT SELECT,UPDATE,DELETE,INSERT ON SCRAMBLE.GTSRC_XREF TO PUBLIC; 
    GRANT SELECT,UPDATE,DELETE,INSERT ON SCRAMBLE.GTSRC_XREF2 TO PUBLIC; 
    GRANT SELECT,UPDATE,DELETE,INSERT ON SCRAMBLE.GTSRC_SHUFFLE TO PUBLIC; 
    GRANT SELECT,UPDATE,DELETE,INSERT ON SCRAMBLE.GTSRC_REFERENCE_DATA TO PUBLIC; 
    GRANT SELECT,UPDATE,DELETE,INSERT ON SCRAMBLE.GTSRC_REFERENCE_LOV TO PUBLIC; 
    GRANT SELECT ANY TABLE TO SCRAMBLE; 
    GRANT SELECT ON SCRAMBLE.GTSRC_REFERENCE_DATA TO PUBLIC; 
    GRANT EXECUTE ON SCRAMBLE.GTSRC_SCRAMBLE2 TO PUBLIC; 
    GRANT EXECUTE ON SCRAMBLE.GTSRC_SCRAMBLEN2 TO PUBLIC; 
    GRANT EXECUTE ON SCRAMBLE.GTSRC_SCRAMBLED2 TO PUBLIC; 
    GRANT EXECUTE ON SCRAMBLE.GTSRC_SCRAMBLED3 TO PUBLIC; 
    GRANT EXECUTE ON SCRAMBLE.GTSRC_CHECKSUM TO PUBLIC; 
    GRANT EXECUTE ON SCRAMBLE.GTSRC_SETCOUNT TO PUBLIC; 
    GRANT EXECUTE ON SCRAMBLE.GTSRC_REPLACE TO PUBLIC; 
    GRANT EXECUTE ON SCRAMBLE.GTSRC_SHUFFLEID TO PUBLIC; 

     

    CREATE PUBLIC SYNONYM GTSRC_CONTROL_STEPS FOR SCRAMBLE.GTSRC_CONTROL_STEPS;
    CREATE PUBLIC SYNONYM GTSRC_CONTROL FOR SCRAMBLE.GTSRC_CONTROL; 
    CREATE PUBLIC SYNONYM GTSRC_XREF FOR SCRAMBLE.GTSRC_XREF; 
    CREATE PUBLIC SYNONYM GTSRC_XREF2 FOR SCRAMBLE.GTSRC_XREF2; 
    CREATE PUBLIC SYNONYM GTSRC_SHUFFLE FOR SCRAMBLE.GTSRC_SHUFFLE; 
    CREATE PUBLIC SYNONYM GTSRC_SHUFFLEID FOR SCRAMBLE.GTSRC_SHUFFLEID; 
    CREATE PUBLIC SYNONYM GTSRC_REPLACE FOR SCRAMBLE.GTSRC_REPLACE; 
    CREATE PUBLIC SYNONYM GTSRC_REFERENCE_DATA FOR SCRAMBLE.GTSRC_REFERENCE_DATA; 
    CREATE PUBLIC SYNONYM GTSRC_REFERENCE_LOV FOR SCRAMBLE.GTSRC_REFERENCE_LOV;
    CREATE PUBLIC SYNONYM GTSRC_CHECKSUM FOR SCRAMBLE.GTSRC_CHECKSUM; 
    CREATE PUBLIC SYNONYM GTSRC_SETCOUNT FOR SCRAMBLE.GTSRC_SETCOUNT; 
    CREATE PUBLIC SYNONYM GTSRC_SCRAMBLE2 FOR SCRAMBLE.GTSRC_SCRAMBLE2; 
    CREATE PUBLIC SYNONYM GTSRC_SCRAMBLED2 FOR SCRAMBLE.GTSRC_SCRAMBLED2; 
    CREATE PUBLIC SYNONYM GTSRC_SCRAMBLED3 FOR SCRAMBLE.GTSRC_SCRAMBLED3; 
    CREATE PUBLIC SYNONYM GTSRC_SCRAMBLEN2 FOR SCRAMBLE.GTSRC_SCRAMBLEN2; 

     


    If you need to install the Scramble database, you can download the Datamaker Repository Installation Kit from https://support.ca.com/us.html using these instructions https://support.ca.com/us/knowledge-base-articles.TEC1903942.html. Then follow the directions below:

     

     

    - Go to the Scramble install folder that came with the Datamaker repository installation kit. 
    - Look at the scramble.ddl. This folder has all the masking routines. 
    - The DDL creates the user, but it did not have the synonyms. 
    - There are two ways to select objects in Oracle: 
    - If you do select*from *.table, you can call that from any user/schema. 
    - If you do select*from table and you're logged into another table, it will not know what table to pull from and it will say 'table unknown' because it doesn't know where to find it. 
    - If you create synonyms, then you tell the database where to find it. 
    - You can have a public synonym saying 'wherever you are in your database, this synonym will point to the correct schema without having to fully qualify it. 
    - If you look at the latest repository install kit, in the Scramble folder, you will see there is a scramble-post-config.ddl file. This creates the synonyms for you. 
    - We generate the scripts by calling the bare functions, we do not qualify them with the schema name. 
    - If you have installed it in a schema not called scramble, then nothing will work. 
    - You will just need to run the post-config ddl in scramble to resolve the issue. 

     

    - In DataMaker, open up the Data Source SQL window. 
    - If you do select * from GTSRC_REFERENCE_DATA , and you do not have the synonyms set up, you will get an error message saying 'ORA-00942: table or view does not exist'. 
    - If you put select * from SCRAMBLE.GTSRC_REFERENCE_DATA , then data is returned. 
    - If you have the synonyms set up, then Datamaker will be looking for SCRAMBLE.GTSRC_REFERENCE_DATA when you say GTSRC_REFERENCE_DATA, and values will be returned. 
    - To resolve this, you need to issues these synonyms as a DBA user. You will need admin rights to resolve this. 

     

    - Issue the commands in the database:
    1. Open SQL Developer. 
    2. Copy the synonym script (scramble-post-config.ddl) 
    3. Paste into the query builder. 
    4. Click execute. 
    - To verify: 
    1. In DataMaker, open up the Data Source SQL window. 
    2. Type in select * from GTSRC_REFERENCE_DATA 
    3. Click execute. 
    - If tables appear automatically, then it worked.

    Additional Information:

    If you are still experiencing any issues, please open a support case by going to https://support.ca.com/us.html