Test Data Manager

Expand all | Collapse all

SEQLOV and shuffle option

Jump to Best Answer
  • 1.  SEQLOV and shuffle option

    Posted 04-03-2020 03:46 PM
    Hi guys,

    I'm doing data generation by the TDM GENERATOR. I have a question about the SEQLOV function.

    @SEQLOV(PERCNULL, @SQLLIST(CONNECTION, SQL[,S])@)@

    According to the documentation, the SEQLOV(PERCNULL, @SQLLIST(CONNECTION, SQL[,S])@) function can shuffle the data when the optional parameter S is specified.

    "S - (optional) If the literal argument S is specified then the rows returned by the query are randomly shuffled before returning the first item. Subsequent calls do not re-shuffle."

    In my synthetic data generation tests, it seems that shuffle is done at all times and not just the first time.

    Here's what I did...

    I created an Oracle table with 1 column and 5 rows.

    table TEST_SEQLOV
    Column: Company
    Values:
    Company A
    Company B
    Company C
    Company D
    Company E

    Here is the instruction I defined in the generator:


    "@seqlov(0,@sqllist(PORACLE_XXXXXXXX_SCRAMBLE,select company from TEST_SEQLOV,S)@)@"


    At the PUBLISH,

    - the "Table Count" is 4
    - the "Repeat" value is 1
    - the "On Duplicate in Data Target" value is EXIT
    - the "On Generated Duplicate" value is IGNORE

    I would have expected to have a list of 4 different companies with no duplicates but I have the following result:

    ROW1
    Company B
    Company A
    Company A
    Company B

    Is there a problem with the shuffle option in SEQLOV?
    Is it me who doesn't understand? (I'm a newbie)

    Thanks for you help

    JF


  • 2.  RE: SEQLOV and shuffle option

    Posted 04-06-2020 06:11 AM
    Could you change your source table to include a primary key...keep it simple,  1 2 3 4 etc and rerun your test.

    The options
     the "On Duplicate in Data Target" value is EXIT
    - the "On Generated Duplicate" value is IGNORE

    only imply when the primary key is involved, eg  a duplicate primary key do Exit or when generating a duplicate primary key  do Ignore


  • 3.  RE: SEQLOV and shuffle option

    Posted 04-06-2020 09:13 AM
    Billy,

    I did test test you suggested.

    I created a primary key.

    Rerun the test with these values:
    - the "Table Count" is 4
    - the "Repeat" value is 1
    - the "On Duplicate in Data Target" value is EXIT
    - the "On Generated Duplicate" value is IGNORE

    and obtain a result with duplicate data:

    Company C
    Company A
    Company C
    Company B

    My question was not about the options on duplicate data.

    My question is about the S (Shuffle) options on teh SEQLOV function. My understanding is that the shuffle is done at the first invocation (based on documentation) and that afterwards the seqlov selects the lines sequentially. By having 5 lines in my test table and asking for 4 lines, I expected to have 4 different lines (without duplicates).

    Is my way of looking at it right?

    Thanks for your help


  • 4.  RE: SEQLOV and shuffle option

    Posted 04-06-2020 11:32 AM
    Yep I agree with your logic.  It worked for me but I ran my quick test on SQL Server.  Let me give it a go on Oracle.  Is there something in particular you are trying to do.  Why not try the Randlov function?


  • 5.  RE: SEQLOV and shuffle option

    Posted 04-06-2020 11:43 AM
    Randlov will give duplication and when I specify the remove options as "On Generated Duplicate" value the quantity asked is not reach. But this is an another problem :) for which I may open a new discussion.


  • 6.  RE: SEQLOV and shuffle option

    Posted 04-06-2020 01:03 PM
    Jean-Francois I cannot reproduce what you are seeing.  One thing that I should have been stated is to create a primary key for the source and the target.  Have you got a primary key for the target?  I have 5 rows for a customer table and I have a column, cust_name .  When i use the SEQLOV function as you have indicated I get 4 unique cust_name when I publish this to a target.


  • 7.  RE: SEQLOV and shuffle option

    Posted 04-06-2020 02:31 PM
    Billy,

    My need is, for exemple, to generate a list of 20K unique company names from a table containing 50K.
    I found a temporary solution which consists in randomizing the table by a pre-publish action.
    But I wonder why option S doesn't work and I don't want to reshuffle the tables every time.

    For our test, I redo everything from the Oracle tables creation to the publish data file.
    Here's the steps:

    1- Create the data table 

    CREATE TABLE SCRAMBLE.TEST_SEQLOV(COMPANY VARCHAR2(100 BYTE));
    CREATE UNIQUE INDEX SCRAMBLE.IDX ON SCRAMBLE.TEST_SEQLOV(COMPANY);
    ALTER TABLE SCRAMBLE.TEST_SEQLOV ADD (
    CONSTRAINT IDX
    PRIMARY KEY (COMPANY) USING INDEX SCRAMBLE.IDX
    ENABLE VALIDATE);
    INSERT INTO SCRAMBLE.TEST_SEQLOV(company) VALUES ('Company A');
    INSERT INTO SCRAMBLE.TEST_SEQLOV(company) VALUES ('Company B');
    INSERT INTO SCRAMBLE.TEST_SEQLOV(company) VALUES ('Company C');
    INSERT INTO SCRAMBLE.TEST_SEQLOV(company) VALUES ('Company D');
    INSERT INTO SCRAMBLE.TEST_SEQLOV(company) VALUES ('Company E');

    2- Create the report table

    CREATE TABLE GENERATOR.E00556_RAPPORT(ROW1 VARCHAR2(1000 CHAR));
    CREATE UNIQUE INDEX GENERATOR.IDX ON GENERATOR.E00556_RAPPORT(ROW1);
    ALTER TABLE GENERATOR.E00556_RAPPORT ADD (
    CONSTRAINT IDX
    PRIMARY KEY (ROW1) USING INDEX GENERATOR.IDX
    ENABLE VALIDATE);

    3- Regiter the object GENERATOR.E00556_RAPPORT

    4- Set a GENERATOR with the follwing function call

    @seqlov(0,@sqllist(PORACLE_ORA867UP_SCRAMBLE,select COMPANY from TEST_SEQLOV,S)@)@

    5- Publish

    Table Count = 4
    Publish to = File
    File Type = TXT Tab Delimited
    Repeat = 1
    On Duplicate in data Target = Exit
    On Generated Duplicate = Ignore

    6- The resulting file content

    Company A
    Company B
    Company B
    Company A

    I would expect to have 4 different compny names.

    JF


  • 8.  RE: SEQLOV and shuffle option

    Posted 04-06-2020 03:10 PM
    Thanks for the details in your example.  What version of Datamaker are you running and what version of Oracle?  Just want to make sure our environments are the same


  • 9.  RE: SEQLOV and shuffle option

    Posted 04-06-2020 03:45 PM
    Oh boy... my mistake... should have tell that at the beginning

    CA-TDM version 4.9
    Oracle Database 12c Enterprise Edition version 12.2.0.1.0, 64 bit

    JF


  • 10.  RE: SEQLOV and shuffle option

    Posted 04-07-2020 07:37 AM
    to be clear, I'm using the TDM portal not Datamaker.


  • 11.  RE: SEQLOV and shuffle option
    Best Answer

    Posted 04-07-2020 08:15 AM
    Hello Jean-Francois.  I was just preparing a response to say I give up because this is working for me.  I dont know why I assumed you were using Datamaker but for some reason I did.  My mistake.  Switching over to the Portal, I see the problem you are having.  I dont see this problem using Datamaker.  I suggest that you  create a support issue for this problem.


  • 12.  RE: SEQLOV and shuffle option

    Posted 04-07-2020 08:20 AM
    Hi Billy,

    Thank you for your time.