A variable defined in a parameter file cannot be used in a post-session command alone. It must be used along with other session properties like Output file directory or Source file directory for it to be used in the post-session command.
Consider a variable $OutputFileRepSybase that is defined as /home/test in the parameter file.
If this variable is used in a post-session command alone, as in the case of rm $OutputFileRepSybase/f1.txt, the variable will not be expanded and consequently it will fail. However, if the variable is used in Output file directory and then in the post-session command, it works fine.
We needs to create workflow variables (Workflow->Edit->Variables) with their corresponding names mentioned in parameter file. Then use them as $$Variablename.
For example we define 4 variables in parameter file:
Firstly we create corresponding workflow variables, and then we can use them in Post-Session Failure Command like this:
Osql -S $$SERVERNAME -d $$DATABASENAME -U $$USERNAME -P $$PASSWORD -Q”EXEC insert_test” -b >> c:\myscript.log 2>&1
Mapping parameters and variables in Informatica are very useful. These function similarly to other programming languages like Java and C++. We can leverage parameters and variables to re-use a value within a mapping or mapplet. These values can be constant (parameter) or dynamic (variable). This flexibility gives us control as developers to create more versatile mappings and workflows.
Lets take a closer look at the differences between mapping parameters and variables in Informatica PowerCenter.
Parameters vs Variables in Informatica
Parameters in Informatica are constant values (datatypes strings, numbers, etc…). Variables on the other hand can be constant or change values within a single session run. We declare a parameter or variable within a mapping by selecting the Parameters and Variables Mappings menu item within the Mapping Designer.
Informatica Parameters Variables Mappings Menu
After selecting the Parameters and Variables Mappings menu item, a Declare Parameters and Variables dialog box will open up.
Declare Parameters Variables
We define the value of the parameter in a parameter file prior to running a session.
We can do this for a variable as well, but is not necessary. If we define a variables value within a parameter file, the mapping/session will use the parameter file’s value as the variables initial/start value. If we do not declare the variables value in a parameter file, the last value saved by the session/mapping will be the variables initial/start value.
One final place the Informatica Integration Service will look for an initial value is in the “Initial Value:” setting of the Declare Parameters and Variables dialog box. If a parameter has not been defined within a parameter file, this initial value will be used as the start value. Similarly, if a variable has not been defined within a parameter file and the Integration Service cannot find a saved variable value within the repository.
If none of these parameter/variable initial values are set explicitly, Informatica will default a string datatypes to an empty string, numbers to 0, and dates to 1/1/1753 A.D. or 1/1/1.
Parameter and Variable Start Value Setting Order
|1. Value in parameter file|
2. Value in pre-session variable assignment
3. Initial value saved in the repository
4. Datatype default value
|1. Value in parameter file|
2. Value in pre-session variable assignment
3. Value saved in the repository
4. Initial value
5. Datatype default value
Where to Use Parameters and Variables
We can use the a parameter or variable in the Expression editor of any transformation in a mapping or mapplet. Source Qualifier transformations and reusable transformation are also places that can leverage parameters and variables. I have personally used parameters in many SQL override statements in Source Qualifier transformations.
One use case is to create a parameter for your schema in case the schema was to change for the tables in your SQL statement. For example, lets say you are migrating from DB2 to an Oracle database. Your schema definition for a set of tables might be DB2.
SELECT * DB2.Contract WHERE CONTRACT_NUM LIKE ‘ABC%’
When migrating to Oracle, the DBAs may insist on changing our schema from DB2 to ORACLE. So in order for a our custom SQL statement to work within our Source Qualifier transformation, we will need to update every SQL statement and table being referenced with DB2 to ORACLE.
Now if we consider the above scenario and only have a handful of mappings referencing DB2 tables, then its probably not that big a deal to make this update. However, if our entire data warehouse resides on DB2 and we have hundreds of mappings to analyze and update, then we have a bunch of work to do. So, if we were to think ahead about this scenario, we could have easily created a parameter file to get around this problem.
For example, lets say we created a parameter called $$DW_SCHEMA and set the parameter value in a parameter file to DB2. Now our SQL statement referenced before would look like this…
SELECT * ‘$$DW_SCHEMA’.Contract WHERE CONTRACT_NUM LIKE ‘ABC%’
Now, when the DBA’s say they are going to migrate to Oracle and switch the schema name from DB2 to ORACLE, this issue become a simple value change in our parameter file. All we have to do is change DB2 to ORACLE…
$$DW_SCHEMA = DB2
gets updated to…
$$DW_SCHEMA = ORACLE
While this does take some forward thinking, it is a real world scenario that can happen and should be considered as part of your ETL architecture.
More About Variables
While, I want to reserve the nitty gritty details of Informatica variables for a later post, I do not want to share a few more things.
As already stated, variables can change values throughout a session. At the start of a session the variable’s initial value is set according the order specified in our table listed earlier in the post. Once the Integration Service reads in the initial value, the value can be changed within the session and saved for use for the next session run.
We can set or change our variable with use of variable function like SetVariable, SetMaxVariable, SetMinVariable, and SetCountVariable. These functions can be used within the following transformations…
4. Update Strategy
The only time the Integration Service will not save our variable is due to one of the below conditions…
1. The session fails to complete.
2. The session is configured for a test load.
3. The session is a debug session.
4. The session runs in debug mode and is configured to discard session output.
Remember, variables stored in a parameter file will override our saved session variables.
Most databases require a single quote around string values, so make sure to add these in any custom SQL override statments. If $$Country is defined in a parameter file as USA for example, add single quotes around $$COUNTRY…
SELECT * FROM STATE WHERE COUNTRY = ‘$$COUNTRY’
SELECT * FROM STATE WHERE COUNTRY = ‘USA’
After reading through this post, I hope you can see the value of Parameters and Variables in Informatica. With parameters and variables we can simplify architecture and reduce rework. There are many more use cases for parameters and variables within Powercenter.
I invited you to share a creative way you have been able to leverage Informatica parameters and variables!
Senior Consultant & Data Architect at Eccella
As a data architect, I have the privilege of solving challenging data problems on a daily basis.
I hold a B.S. in Mathematics and M.S. in System Engineering. I have also earned a Big Data and Social Analytics Certification from MIT.
Eccella is a high end, boutique style consulting firm focused, on creating data driven companies.
Find Aaron Gendle on LinkedIn -