Built-in Variables

The transformation language provides built-in variables. Built-in variables return either run-time or system information. Run-time variables return information such as source and target table name, folder name, session run mode, and workflow run instance name. System variables return session start time, system date, and workflow start time.

You can use built-in variables in expressions in the Designer or Workflow Manager. For example, you can use the system variable SYSDATE in a DATE_DIFF function. You can use run-time variables in expressions and in input fields that accept mapping or workflow variables. For example, you can use run-time variable $PMWorkflowRunInstanceName as part of a target output file name. The Data Integration Service sets the values of built-in variables. You cannot define values for built-in variables in a workflow or session parameter file.

You can use built-in variables in expressions. For example, you can use the system variable SYSDATE in a DATE_DIFF function.

The following built-in variables provide run-time information:

The following built-in variables provide system information:

The following table describes where you use built-in variables in the Designer and Workflow Manager:

$PM<SourceName>@TableName and $PM<TargetName>@TableName return the source and target table names for relational source and target instances as string values. Use these variables with any function that accepts string datatypes.

The variable name depends on the source or target instance name. For example, for a source instance named “Customers,” the built-in variable name is $PMCustomers@TableName. If the relational source or target is part of a mapplet within a mapping, the built-in variable name includes the mapplet name:

Use $PM<SourceName>@TableName and $PM<TargetName>@TableName in a mapping or a mapplet. For example, in a mapping that contains multiple relational sources, you can use $PM<SourceName>@TableName in the output port of an Expression transformation to write the source table name for each row to the target. You can also use these variables in input fields that accept mapping variables.

$PMFolderName returns the name of the repository folder as a string value. Use $PMFolderName with any function that accepts string datatypes.

Use $PMFolderName in a mapping, a mapplet, workflow links, or in workflow tasks such as Assignment and Decision tasks. You can also use $PMFolderName in input fields that accept mapping or workflow variables.

$PMIntegrationServiceName returns the name of the Data Integration Service that runs the session. Use $PMIntegrationServiceName with any function that accepts string datatypes. $PMIntegrationServiceName returns the Data Integration Service name as a string value.

Use $PMIntegrationServiceName in a mapping, a mapplet, workflow links, or in workflow tasks such as Assignment and Decision tasks. You can also use $PMIntegrationServiceName in input fields that accept mapping or workflow variables.

$PMMappingName returns the mapping name as a string value. Use $PMMappingName with any function that accepts string datatypes.

Use $PMMappingName in a mapping or a mapplet. You can also use $PMMappingName in input fields that accept mapping variables.

$PMRepositoryServiceName returns the name of the Model Repository Service as a string value. Use $PMRepositoryServiceName with any function that accepts string datatypes.

Use $PMRepositoryServiceName in a mapping, a mapplet, workflow links, or in workflow tasks such as Assignment and Decision tasks. You can also use $PMRepositoryServiceName in input fields that accept mapping or workflow variables.

$PMRepositoryUserName returns the name of the repository user that runs the session. Use $PMRepositoryUserName with any function that accepts string datatypes. $PMRepositoryUserName returns the repository user name as a string value.

Use $PMRepositoryUserName in a mapping, a mapplet, workflow links, or in workflow tasks such as Assignment and Decision tasks. You can also use $PMRepositoryUserName in input fields that accept mapping or workflow variables.

$PMSessionName returns the session name as a string value. Use $PMSessionName with any function that accepts string datatypes.

Use $PMSessionName in a mapping or a mapplet. You can also use $PMSessionName in input fields that accept mapping variables.

$PMSessionRunMode returns the session run mode, normal or recovery, as a string value. Use $PMSessionRunMode with any function that accepts string datatypes.

Use $PMSessionRunMode in a mapping or a mapplet. You can also use $PMSessionRunMode in input fields that accept mapping variables.

$PMWorkflowName returns the name of the workflow as a string value. Use $PMWorkflowName with any function that accepts string datatypes.

Use $PMWorkflowName in a mapping, a mapplet, workflow links, or in workflow tasks such as Assignment and Decision tasks. You can also use $PMWorkflowName in input fields that accept mapping or workflow variables.

Each workflow run has a unique run ID. $PMWorkflowRunId returns the workflow run ID as a string value. Use $PMWorkflowRunId with any function that accepts string datatypes.

Use $PMWorkflowRunId in a mapping, a mapplet, workflow links, or in workflow tasks such as Assignment and Decision tasks. You can also use $PMWorkflowRunId in input fields that accept mapping or workflow variables. For example, you configure a workflow to run concurrently with the same instance name, and you want to track the status of each workflow run using a third-party application. Use $PMWorkflowRunId in a post-session shell command to pass the run ID to the application.

$PMWorkflowRunInstanceName returns the workflow run instance name as a string value. Use $PMWorkflowRunInstanceName with any function that accepts string datatypes.

Use $PMWorkflowRunInstanceName in a mapping, a mapplet, workflow links, or in workflow tasks such as Assignment and Decision tasks. You can also use $PMWorkflowRunInstanceName in input fields that accept mapping or workflow variables. For example, for a concurrent workflow with unique instance names, you can create unique target files for each run instance by setting the target output file name in the session properties to “OutFile_$PMWorkflowRunInstanceName.txt.”

Or, you want to use a post-session shell command to create an indicator file used by a predefined Event-Wait task. In the shell command that generates the indicator file, use $PMWorkflowRunInstanceName in the indicator file name to ensure that one workflow run instance does not delete an indicator file needed by another workflow run instance.

SESSSTARTTIME returns the current date and time value on the node that runs the session after the Integration Service initializes the session. Use SESSSTARTTIME with any function that accepts transformation date/time data types. SESSSTARTTIME is stored as a transformation date/time data type value.

Use SESSSTARTTIME in a mapping or a mapplet. You can reference SESSSTARTTIME only within the expression language.

The following expression uses $$$SessStartTime in the source filter condition of a source qualifier to perform an incremental extraction. The expression specifies a range of dates of all days in the week prior to when the Data Integration Service initializes the session. The expression uses the function DATE_DIFF to find the difference in the number of days between the value ORDER_DATE and $$$SessStartTime. If the difference between the two dates is less than or equal to seven days, the Data Integration Service extracts that row from the source:

SYSDATE returns the current date and time up to seconds on the node that runs the session for each row passing through the transformation. SYSDATE is stored as a transformation date/time datatype value.

To capture a static system date, use the SESSSTARTTIME variable instead of SYSDATE.

The following expression uses SYSDATE to find orders that have shipped in the last two days and flag them for insertion. Using DATE_DIFF, the Data Integration Service subtracts DATE_SHIPPED from the system date, returning the difference between the two dates. Because DATE_DIFF returns a double value, the expression truncates the difference. It then compares the result to the integer literal 2. If the result is greater than 2, the expression flags the rows for rejection. If the result is 2 or less, it flags them for insertion.

WORKFLOWSTARTTIME returns the current date and time on the node hosting the Integration Service when the Data Integration Service initializes the workflow. Use WORKFLOWSTARTTIME with any function that accepts transformation date/time datatypes. WORKFLOWSTARTTIME is stored as a transformation date/time datatype value.

Use WORKFLOWSTARTTIME in workflow links and tasks such as Assignment and Decision tasks. You can reference WORKFLOWSTARTTIME only within the expression language.

The following expression uses WORKFLOWSTARTTIME to display the number of minutes between the workflow start time and the start time of a task in the workflow. Using the SQL function DATE_DIFF, the Data Integration Service subtracts the task start time from WORKFLOWSTARTTIME and returns the result as a number of days:

Built-in Variables

$PM<SourceName>@TableName, $PM<TargetName>@TableName

$PMFolderName

$PMIntegrationServiceName

$PMMappingName

$PMRepositoryServiceName

$PMRepositoryUserName

$PMSessionName

$PMSessionRunMode

$PMWorkflowName

$PMWorkflowRunId

$PMWorkflowRunInstanceName

SESSSTARTTIME

Example

SYSDATE

Example

WORKFLOWSTARTTIME

Example

Variable Name	Designer	Workflow Manager
$PM<SourceName>@TableName, $PM<TargetName>@TableName,	- Expressions - Input fields that accept mapping variables	- Input fields that accept mapping variables
$PMFolderName	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables
$PMIntegrationServiceName	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables
$PMMappingName	- Expressions - Input fields that accept mapping variables	- Input fields that accept mapping variables
$PMRepositoryServiceName	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables
$PMRepositoryUserName	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables
$PMSessionName	- Expressions - Input fields that accept mapping variables	- Input fields that accept mapping variables
$PMSessionRunMode	- Expressions - Input fields that accept mapping variables	- Input fields that accept mapping variables
$PMWorkflowName	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables
$PMWorkflowRunId	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables
$PMWorkflowRunInstanceName	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables	- Expressions - Input fields that accept mapping variables - Input fields that accept workflow variables
$$$SessStartTime	- Mapping or mapplet filter conditions - User-defined joins - SQL overrides	- Mapping or mapplet filter conditions - User-defined joins - SQL overrides
SESSSTARTTIME	- Expressions	n/a
SYSDATE	- Expressions	- Expressions
WORKFLOWSTARTTIME	n/a	- Expressions