Parameters and variables in Informatica PowerCenter

Purpose

A mapping can utilize parameters and variables to store information during the execution. Each parameter and variable is defined with a specific data type and their main purpose is to provide increased development flexibility.
Parameters are different from variables in the fact that:

  • The value of a parameter is fixed during the run of the mapping
  • Variables can change in value during run-time

    Both parameters and variables can be accessed from any component in the mapping which supports it.

    To create a parameter or variable, go to Mapping -> Parameters and Variables from within the Mapping Designer in the Designer client.
    The format is $$VariableName or $$ParameterName

    Changing values of Variables

    To change the value of a variable, one of the following functions can be used within an expression: SETMAXVARIABLE($$Variable, value) , SETMINVARIABLE($$Variable, value), SETVARIABLE($$Variable, value) , SETCOUNTVARIABLE($$Variable), where:

  • SETVARIABLE sets the variable to a value that you specify (executes only if a row is marked as insert or update). At the end of a successful session, the Integration Service saves either the MAX or MIN of (start value.final value) to the repository, depending on the aggregate type of the variable. Unless overridden, it uses the saved value as the start value of the variable for the next session run.
  • SETCOUNTVARIABLE - increments a counter variable. If the Row Type is Insert increment +1, if Row Type is Delete increment -1. A value = 0 is used for Update and Reject.
  • SETMAXVARIABLE - compare current value to value passed into the function. Returns the higher value and sets the current value to the higher value.
  • SETMINVARIABLE - compare current value to the value passed into the function. Returns the lower value and sets the current value to the lower value.

    At the end of a successful session, the values of variables are saved to the repository. The SetVariable function writes the final value of a variable to the repository based on the Aggregation Type selected when the variable was defined.

    Parameter files

    Parameter file is an ASCII file which is used to set values of mapping paramteres and variables. Parameters can be set on workflow, worklet or session level. The physical location of a parameter file is set in Workflow manager in Workflows -> Edit. It can also be specified using the pmcmd command when starting a session task.

    Parameter file structure

    Parameters can be grouped into the following sections:

    • [Global]
    • [Service: service name]
    • [folder name.WF:workflow name]
    • [folder name.WF:workflow name.WT:worklet name]
    • [folder name.WF:workflow name.WT:worklet name.WT:worklet name...]
    • [folder name.WF:workflow name.ST:session name]
    • [folder name.session name]
    • [session name]

    Examples / useful tips

    • The value is initialized by the specification that defines it, however it can be set to a different value in a parameter file, specified for the session task
    • Initialization priority of Parameters: Parameter file, Declared initial value, Default value
    • Initialization priority of Variables: Parameter file, Repository value, Declared initial value, Default value
    • Parameters and variables can only be utilized inside of the object that they are created in.
    • Parameters and variables can be used in pre and post-SQL
    • Sample parameter file:
      [Service:IntegrationSvc_01]
$$SuccessEmail=dwhadmin@etl-tools.info
$$FailureEmail=helpdesk@etl-tools.info
[DWH_PROJECT.WF:wkf_daily_loading]
$$platform=hpux
$$DBC_ORA=oracle_dwh
[DWH_PROJECT.WF:wkf_daily_loading.ST:s_src_sa_sapbw]
$$DBC_SAP=sapbw.etl-tools.info
$$DBC_ORA=oracle_sap_staging




    Back to the Informatica tutorial