Informatica Paramter File

Using Parameter file Using Workflow and Session Parameter File Simultaneously Author: 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 best 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, workflow, informatica, parameter file, session, merge files

The Challenge: Using Parameter file The challenge is to use session level parameter file and workflow 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 within the workflow, the Integration Service uses the workflow parameter file, and ignores the session parameter file. Session Parameter File: It contains session parameters which contain values that can change from session to session, such as database connections 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 task 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 Manager (WLM), scheduling product by CA Inc. WLM schedules jobs across platforms e. g. dependent jobs across Informatica, teradata, UNIX MF can be scheduled together. Now the requirement of this scheduler is that 1. You need to have 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 today’s 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 date

Now if a workflow level parameter file is used, the variable $$lastrun will have to be mentioned 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 set to TRUE will force Informatica to read both session and workflow parameter file.

What we did was we created a new workflow parameter file with the all the sessions name in it and for every session we have set $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 today’s date. In this way we were able to use both 1. Workflow level parameter file which was the requirement of WLM. 2.

Session Level 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 After 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 refer Informatica help section. Path given below. Parameter Files > Configuring the Parameter File Name and Location References: Informatica Help Guide