7 Replies Latest reply on Apr 15, 2019 11:09 PM by Nico Heinze

    Sequence Generator Transformation

    Amrit Kumar New Member

      i am using reusable sequence generator in in my mapping on INFORMATICA DATA QUALITY  but every execution it is incrementing the series like 500+ sequence.

       

      First Execution:-

      Example:--

      1

      2

      3

      ...Second  Execution:-

      501

      502

      503

      Third Execution

      .....

      1001

      1002

      1003

      ---

      ----

        • 1. Re: Sequence Generator Transformation
          Robert Whelan Guru

          Hi,

          This is as designed as the numbers are generated in batches of 500 so any unused in a particular run of a mapping are discarded.

          Full details in KB 508399

          • 2. Re: Sequence Generator Transformation
            Nico Heinze Guru

            As far as I know, the Sequence Generator in IDQ / platform doesn't work the same way as in PowerCenter. It always reserves a block of 500 numbers whenever it accesses the MRS, no matter whether all these 500 numbers are used or not. There's no way to get a gapless sequence from the SEQ in IDQ (no way which I have heard of so far).

             

            Regards,

            Nico

            • 3. Re: Sequence Generator Transformation
              Amrit Kumar New Member

              If i don't have 500 records then Suppose

              case A ) source system only have 50 records but requirement is like (SCD2  ) keep all the history of all records.

              case B) Need surrogate key if customer id is repeatable.

               

              if Sequence generator is generating 500 + every execution it will reach end value asap

              • 4. Re: Sequence Generator Transformation
                Amrit Kumar New Member

                If i don't have 500 records then Suppose

                case A ) source system only have 50 records but requirement is like (SCD2  ) keep all the history of all records.

                case B) Need surrogate key if customer id is repeatable.

                • 5. Re: Sequence Generator Transformation
                  Amrit Kumar New Member

                  I have raised support case on support portal but reply is not satisfactory.

                   

                  This email is with reference to Case 02524289.

                  As far as I am aware this is the expected behavior with the sequence generator in IDQ.

                  On the first run the sequence generator will generate a list of
                  increasing values from 1 to X (depending on the volume of records from the source this will adjust accordingly).

                  Their is no continuity in the sequence generated between each subsequent run and its previous execution. You will find details on this at https://kb.informatica.com/faq/7/Pages/18/508399.aspx.

                  Kindly let me know if you have any further questions.

                  • 6. Re: Sequence Generator Transformation
                    Amrit Kumar New Member

                    I have raised support case on support portal but reply is not satisfactory.

                     

                    This email is with reference to Case 02524289.

                    As far as I am aware this is the expected behavior with the sequence generator in IDQ.

                    On the first run the sequence generator will generate a list of
                    increasing values from 1 to X (depending on the volume of records from the source this will adjust accordingly).

                    Their is no continuity in the sequence generated between each subsequent run and its previous execution. You will find details on this at https://kb.informatica.com/faq/7/Pages/18/508399.aspx.

                    Kindly let me know if you have any further questions.

                    • 7. Re: Sequence Generator Transformation
                      Nico Heinze Guru

                      If the gaps themselves are no problem, your concern about "exhausting" the number range probably is not a big deal either. Look at it this way.

                      The SEQ generates numbers as signed 64-bit entities. This means that the numbers produced range up to 9,223,372,036,854,775,807 (roughly 9 quintillions in British / US counting).

                      Even if you use only one out of 500 numbers per block per session run, this means you can easily run your session app. 18 quadrillion times.

                      Now let's suppose that each session run takes only 5 seconds. Then you can run this session for a total duration of 90 quadrillion seconds. This roughly equals 2.7 billion years.

                       

                      Now the whole universe is (as of current scientific research) supposed to exist for app. 13.7 billion years. This equals a total of 432 quadrillion seconds.

                      The earliest traces of mankind as we know it today reach back to app. 5 million years.

                      You see, we have more than plenty of time to exhaust the number range of a SEQ. Nothing we have to worry about within the next millions of years.

                       

                      Regards,

                      Nico