2

Parameters and Variables in Informatica

Declare Parameters Variables

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

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

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

Parameter Variable
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.

For example…

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…

1. Expression
2. Filter
3. Router
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.

Quick Tip

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’

Will become…

SELECT * FROM STATE WHERE COUNTRY = ‘USA’

Summary

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!

Aaron Gendle

Aaron Gendle

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 -
Aaron Gendle

Comments 2

  1. Hi,

    I have some questions about PCX, and I ask for help, if you can and have time for it:

    1) Update strategy:

    I want to make a typical uptaing process in wich I have a database table with a PK: var1,var2,var3.
    If a row of my datastraem have a key var1-var2-var3 that is actually in the table I want to update it, if noy I want to insert.
    What expression sintax do iI have to use?
    iif( var1-var2-var2 exists……, DD_UPDATE, DD_INSERT)

    I have tried several options, but I don’ get the right sintax. It may be simple, help please!!

    2) Logs

    I am chanching the log level in a workflow, but It doesn’t have effect.

    I chanhe: info -> trace -> debug -> error

    but in the log I get by:

    infacmd.bat wfs startworkflow -dn Domain_localhost -sn DIS -un Administrator -pd Administrator -sdn Native -a Application_name -wf workflow_name > output.log

    It doesn’t change……. any idea?, any hepl?

    3) Loops:

    How do I make a loop in a workflow??

    I want to execute a mapping for all the periods in a range of periodos. That is period from 201401 to 201412.
    Do I have to put the condition in a door link and change the var value in a attribution taks?

    4) vars from workflow to mapping

    How can I use a var defined in a workflow in a mapping?
    The var period from the former question. I define it in a workflow, but I need to use in a mapping.

    Thanks in advance for your help.

    Juan

    1. Post
      Author

      First of all, thanks for taking a look at my blog and thanks for the comments.

      Let me see if I can help you with some of these.

      1. You first questions is asking about syntax in a Update Strategy Transformation to check for update if a pipeline record already exist in the target table, then a insert if not. For this to work, you will need to setup the data appropriately before it gets to the Update Strategy Transformation. You will need to use a lookup transformation to lookup PK values in your target table to determine if your pipeline record already exist (A joiner with your pipeline records and your target table will also work). Next you want to leverage another record, possible the surrogate key of your target table from the lookup return results as your avenue to perform the update strategy logic. Lets call this surrogate key S_PK1. This attribute will either be NULL or have a value based on your lookup results. So once you get to your update strategy transformation, your logic should look something like this…

      IIF (ISNULL(S_PK1), DD_INSERT, DD_UPDATE)

      Hopefully that makes sense…

      2. Not sure on this one, I would have to do some looking myself. Sorry…

      3. Here is a snippet from the PowerCenter help guide, “The Workflow Manager does not allow you to use links to create loops. Each link in the workflow or worklet can run only once.”

      I have personally performed looping in a more table data driven fashion with specific scheduling logic. For example, I could tell a scheduling tool (ESP, Tivoli, etc..) to call a particular job flow (one or more workflows). We can leverage a physical DB table (PERIOD_TRACKER) to track our periods in your scenario and whether we have run for that period (Table columns PERIOD, and COMPLETE). So our scheduling tool could be setup to go down two paths, one path to execute our primary workflow along with a session that could update our PERIOD_TRACKER tracker as each period is executed. Our second path would say we are done since our PERIOD_TRACKER table shows all records as COMPLETE = Yes or 1. Our scheduler is the key as it must be setup to re-trigger the job flow until all PERIOD_TRACKER rows are complete. Certain wait scheduling jobs will need to be put in place. Its a bigger discussion than a simple reply here, but hopefully that gets your wheels spinning. Hope that helps…

      4. I took a snippet from the PowerCenter help guide as seen below to help you with this one. Yes it is possible.

      Passing Parameters and Variables Between Sessions

Leave a Reply

Your email address will not be published. Required fields are marked *