4 Replies Latest reply on Aug 15, 2019 11:11 PM by mini mishra

    Mapping variable

    mini mishra Seasoned Veteran



      Firstly, I would like to first explain my requirement. We have nearly 100 of mappings which need DATE filter inside Source Qualifier to filter only latest records and this date is same for all 100 of mappings. But the problem is that Source is Sybase DB and DATE table is in Oracle DB. So, we can not use this date filter inside SQ and in no way we can afford to load entire data in SQ and then use JOINER\LOOKUP and FILTER to load active records because volume of data is very huge (90 Billion records) for each mapping.


      What can be the possible solution for this problem??


      One solution which comes to my mind is creating a separate mapping for DATE table and creating a mapping variable to store that date value and then passing that mapping variable to workflow variable through post & pre session assignment variable and finally using this variable inside main mapping Source Qualifier. ( I already implemented this process )


      But the problem here is that above logic is leading to the creation of 100 DATE mappings for 100 main mappings which is a very bad solution. Can't we make it one mapping and a re-usable session? or Is there any other way to solve for this problem??

        • 1. Re: Mapping variable
          Nico Heinze Guru

          Of course it should be possible to retrieve the DATE value from the Oracle table with one generic mapping. The only problem I see is that mapping variables are bound to their mapping and not to a session, so if more than one of those 100 workflows may be running at the same point in time, you're indeed in trouble, no question.


          In this case a slightly different approach might be better:

          You retrieve the DATE value from the Oracle table and write it to a parameter file. The name of this parameter file is specific per workflow (meaning the workflow name is part of the file name):

          You can retrieve the workflow name as one standard parameter ($PMWorkflowName) to set the value of a workflow variable.

          Now you hand over this workflow variable to the session which retrieves the DATE from Oracle and uses this mapping parameter(!) to create the correct parameter for the actual working session.


          This means that you have to create one new session per workflow, but as mentioned above once you have created this session for one workflow you should be able to simply copy it into each affected workflow.


          Second part of your question: a reusable session won't work because in reusable sessions you cannot use pre- or post-session variable assignments. Sad but true. So the sessions will have to be created "normally", meaning one session per main workflow.

          But it might work to make life easier for yourself by creating such a session in one workflow and then copy it to all other workflows one by one. I haven't tried it myself, but I think it should work.




          • 2. Re: Mapping variable
            mini mishra Seasoned Veteran

            Hi Nico,


            Creating additional session for every workflow is what we are trying to avoid. Is there any other way to resolve this problem

            • 3. Re: Mapping variable
              Nico Heinze Guru

              That depends on the logic of execution for those 100 workflows: do they always refer to the same DATE value?


              What I mean is this: if those 100 workflows run one after the other AND they all use the same start date, then of course you don't need more than one extra session, namely for the very first workflow (to prepare the parameter file for all 100 workflows).

              If, however, e.g. 10 out of the 100 workflows may need one DATE value, the next 20 workflows need a different DATE value, the following 30 workflows need yet another DATE value, and the final 40 workflows need a fourth different DATE value, then you're out of luck.


              At least I don't see (at this moment) how that can be avoided.




              • 4. Re: Mapping variable
                mini mishra Seasoned Veteran

                As you said, I created only one mapping but a new session in all workflows. It is working fine now.


                Thanks a lot ..!!