Informatica Paramter File

Using logical argument file Using Workflow and Session argumentation File Simultaneously causation Amit Singh Yadav Date written 18/01/10 Declaration I/We hereby declare that this document is based on my/our personal experiences and/or experiences of my/our project members. To the high hat of my/our knowledge, this document does not contain any material that infringes the copyrights of any other individual or organization . Amit Singh Yadav Target readers All Keywords Informatica Parameter File, $PMMergeSessParamFile, work flow, informatica, parametric quantity file, seance, link up filesThe Challenge Using Parameter file The challenge is to use session take aim logical argument file and work flow level parameter file at the same time. The problem being that when you define a workflow parameter file and a session parameter file for a session at heart the workflow, the Integration Service uses the workflow parameter file, and ignores the session parameter file. Session Para meter File It contains session parameters which contain values that can change from session to session, such as database connectors or file names. PMSessionLogFile and $ParamName are user-defined session parameters. Also variables that we use in source queries like date criterion e. g. $$Lastrun Workflow Parameter File It contains workflow level parameters. For example, you can use a workflow variable in a Decision labour to determine whether the previous task ran properly. In a workflow, $TaskName. PrevTaskStatus is a predefined workflow variable and $ $SessionName is a user-defined workflow variable. Problem Statement Using both session and workflow level parameter file.First let me tell you why we need both the parameter files in our workflow. Recently we decided to use third party scheduling tool, Workload passenger car (WLM), scheduling product by CA Inc. WLM schedules jobs across platforms e. g. dependent jobs across Informatica, teradata, UNIX MF can be scheduled together. instanter the requirement of this scheduler is that 1. You need to have a bun in the oven a scheduler level parameter file with all the sessions names used in the workflow. 2. You need to use this same parameter file as workflow level parameter file in your workflow.The problem we faced was that our current situation is The sql query in our sessions is select * from where date $ $lastrun. We have a session level parameter file and every time the session runs it picks $$lastrun from the session parameter file. At the end of the session a script is run which updates the $$lastrun with todays date so that next time when the session is run it picks up data for dates $$lastrun. This is how our session level parameter files looks $ more mStaging_PCare. parm Using Parameter file s_mStaging_PCare $$lastrun=2009-12-20 ? session_name - ? Lastrun dateNow if a workflow level parameter file is used, the variable $$lastrun will have to be acknowledgmented in the workflow parameter file. And If we will mention $$lastrun there, then Informatica will pick up the date from the workflow parameter file, while the session level parameter file variable, $$lastrun, will be updated by a script in the session so, we need something through which we can use both workflow and session parameter file. Solution There is a very useful property in Informatica called $PMMergeSessParamFile which when direct to TRUE will force Informatica to read both session and workflow parameter file.What we did was we created a spick-and-span workflow parameter file with the all the sessions name in it and for every session we have furbish up $PMMergeSessParamFile=TRUE. Now when workflow runs it looks for $$lastrun in workflow parameter file and when it does not find the value there, it looks for $ $lastrun in session parameter file. It gets the value there and session succeeds. In the end the script updates the session level parameter file with todays date. In this way we were able to use both 1. W orkflow level parameter file which was the requirement of WLM. 2.Session train parameter file which was required by our session since the script updates session level parameter file and we did not wanted to change the script. This is how our new workflow level parameter files looks $ more PCare_Staging_WLM. par s_mStaging_PCare ? Session Name -? parameterized connection $DBConnectionSource=Tera_ETL_VIEWS_NE $PMMergeSessParamFile=TRUE ? Property set to true Using Parameter file later using this parameter file Informatica will pick up connection name from the above parameter file and $$lastrun from session parameter file since $PMMergeSessParamFile is set to TRUE.This is what was required. Usage 1. 2. This property will allow users to use both session and workflow level parameter file at same time. Like in the scenario above. Suppose there are sessions which are based on Incremental logic i. e. Session level parameter file is automatically created by UNIX script. Now if users wants to have parameterized connections for such sessions then either they need to change their script which generates parameter file or they can simple use $PMMergeSessParamFile and mention parameterized connection names at workflow level parameter file. . If users want a workflow to run two concurrent instances. If two workflows use same set of re-usable sessions then it helps in making parameter file names parameterized. It removes hard coding of parameter file name from sessions. For more details please uphold Informatica help section. Path given below. Parameter Files Configuring the Parameter File Name and Location References Informatica Help Guide