10 Replies Latest reply on Jan 30, 2020 12:16 PM by Arun Kumar

    Loop session multiple times

    Arun Kumar Guru

      Hi all,

       

      I have a requirement to loop informatica session multiple times dynamically, is there a way? My requirement is like below -

       

      A table will have bunch of records, the count of records could vary based on the day ( a Informatica workflow loads this table).

       

      I need to take 100 records from the above table each at a time and load to webservices through a Java transform. I am struggling how this can be done dynamically. For example If the table has 1200 records, 100 records have to be passed 12 times (session or mapping has to loop through for 12 times to load 100 records in each cycle).

       

      Appreciate if any one has any idea on how to do this.

       

      Thanks,

      Arun

        • 1. Re: Loop session multiple times
          Nagamohan Chowdam New Member

          You can apply this looping concept through Unix script.

           

          Add a sequence to the records you are going to pass to Webservices if the sequence in the table is not there.

           

          Take 2 variables in Unix script - Tot_Rec_Cnt and Web_Serv_Cnt. Deduct this 'Web_Serv_Cnt for every 100 records you pass to Web services and loop through until 'Tot_Rec_Cnt > 0'.

           

          Hope this helps !

           

           

           

          Thanks,

          Mohan

          • 2. Re: Loop session multiple times
            Nico Heinze Guru

            May I ask why you want to execute the session N number of times. Wouldn't it be easier in your case to simply "collect" 100 records, issue a Web Service call for these, then "collect" the next 100 records, issue the next call, and so on?

            Granted, it is a little tricky to implement that in a mapping, but it can be done, and it would save you the hassle of repeatedly executing the session.

             

            BTW you can do that both using Unix scripts and Windows batch or PowerShell scripts, no question.

             

            Regards,

            Nico

            • 3. Re: Loop session multiple times
              Arun Kumar Guru

              Thanks Mohan. Will explore the idea.

              • 4. Re: Loop session multiple times
                Arun Kumar Guru

                Thanks Nico for the suggestion, but how can Informatica dynamically pick up 100 records?

                • 5. Re: Loop session multiple times
                  Nico Heinze Guru

                  There are different ways to achieve that.

                  First way is in the Source Qualifier. Many SQL dialects offer some way to restrict the total count of records. For example, in Oracle you could use the following WHERE filter:

                   

                  SELECT ...

                  FROM ...

                  WHERE ...

                  AND ROWNUM <= 100

                   

                  In SQL Server you can use the following means:

                   

                  SELECT TOP 100 ...

                  FROM ...

                  WHERE ...

                   

                  I seem to recall that in Db2 there's also a way, but at this moment I don't recall how. Please ask some DB2 specialist if necessary.

                   

                  Again, this is DB specific.

                   

                  The second way is to build your mapping such that you can restrict the number of records processed. However, before explaining how to do that it might be worth examining how exactly this web service is called. Do you want to invoke this web service several times, each time with a set of 100 input records?

                  If that's the case, then I can describe it in my next post how that can be achieved within the mapping.

                   

                  Regards,

                  Nico

                  1 of 1 people found this helpful
                  • 6. Re: Loop session multiple times
                    Arun Kumar Guru

                    Do you want to invoke this web service several times, each time with a set of 100 input records?

                     

                    Yes this is our exact requirement. Currently web services pick up complete set of records and load it, we need to break it into chunks.The looping needs to be dynamic until the last record in the table.

                    • 7. Re: Loop session multiple times
                      Nico Heinze Guru

                      The rough outline of the process goes like this:

                       

                      You start by feeding all records into an Expression transformation.

                      Within this EXP, you set up a variable port of type Bigint to count all incoming records from 1 to whatever count of input records. Output the value of this variable port via an output port COUNTER of type Bigint.

                      Make sure that this same EXP also outputs one more output port GROUP_NO of type Integer with the constant value of 1; GROUP_NO should be above COUNTER in the port list (this isn't strictly necessary but makes life easier a little later).

                       

                      Next you need a "dummy" source, for example, a flat file with just one single port of type String, set the input type in the session to Command instead of File, hit the Tab key (this ensures that the Workflow Manager will switch from the File output details to the Command output details), and enter this command:

                         echo bla

                       

                      Feed the string port from this "dummy" source into an EXP which outputs the following two ports:

                      1.: GROUP_NO, integer, constant value 2.

                      2.: COUNTER, bigint, constant value 1.

                       

                      Now join the two data streams based on GROUP_NO followed by COUNTER. The Joiner must be set to Sorted Input and performs a Full Outer Join. The stream from the "dummy" source is the Master source.

                       

                      Now comes the tricky part which I cannot describe in detail. Simply because it's way too long ago that I've worked with a web service consumer transformation. What I can tell you is that you have to feed 100 records into a WSC as the "detail" information and that for each 100th record AND for the very last record which comes from the "dummy" source you have to create the envelope of the web service call and actually do the call.

                      How that can be done in detail: again, I can't tell because first I don't know the structure of your web service and second because it's simply too long ago.

                       

                      So I only can hope that you can take the description above to some use.

                       

                      Whatever I left unclear, please ask for more details / explanation. As long as you don't ask me about the web service consumer transformation, I'll do my best.

                       

                      Regards,

                      Nico

                      1 of 1 people found this helpful
                      • 8. Re: Loop session multiple times
                        Arun Kumar Guru

                        Thanks Nick. That's a great explanation. Will try and see how it goes and get back.

                        • 9. Re: Loop session multiple times
                          Nico Heinze Guru

                          I hope this works out well for you. Please let us know, I would really like to learn whether my suggestion was correct.

                           

                          Thanks,

                          Nico

                          1 of 1 people found this helpful
                          • 10. Re: Loop session multiple times
                            Arun Kumar Guru

                            Though we tried but eventually decided to go with Java transformation for some other reason.

                             

                            Thanks for the suggestion, i will keep this in mind for future requirement