Table of Contents

Search

  1. Preface
  2. Understanding Pipeline Partitioning
  3. Partition Points
  4. Partition Types
  5. Pushdown Optimization
  6. Pushdown Optimization Transformations
  7. Real-time Processing
  8. Commit Points
  9. Row Error Logging
  10. Workflow Recovery
  11. Stopping and Aborting
  12. Concurrent Workflows
  13. Grid Processing
  14. Load Balancer
  15. Workflow Variables
  16. Parameters and Variables in Sessions
  17. Parameter Files
  18. FastExport
  19. External Loading
  20. FTP
  21. Session Caches
  22. Incremental Aggregation
  23. Session Log Interface
  24. Understanding Buffer Memory
  25. High Precision Data
  26. POWERCENTERHELP

Advanced Workflow Guide

Advanced Workflow Guide

Where to Use Parameters and Variables

Where to Use Parameters and Variables

You can use parameters and variables to assign values to properties in the Designer and Workflow Manager and to override some service and service process properties. For example, you can use a parameter to specify the Lookup cache file name prefix or the default remote directory for an FTP connection.
If the property is a SQL statement or command, you can either use parameters and variables within the statement or command, or you can enter a parameter or variable in the input field for the property, and set the parameter or variable to the entire statement or command in the parameter file.
For example, you want to use a parameter or variable in a relational target override. You can enter a parameter or variable within the UPDATE statement of a relational target override and define the parameter or variable below the appropriate heading in the parameter file. Or, to define the UPDATE statement in a parameter file, complete the following steps:
  1. In the Designer, edit the target instance, enter session parameter $ParamMyOverride in the Update Override field, and save the mapping.
  2. In the Workflow Manager, configure the workflow or session to use a parameter file.
  3. Set $ParamMyOverride to the SQL UPDATE statement below the appropriate heading in the parameter file.
You can also use a parameter file to override service and service process properties defined in the Administrator tool. For example, you can override the session log directory, $PMSessionLogDir. To do this, configure the workflow or session to use a parameter file and set $PMSessionLogDir to the new file path in the parameter file.
You can specify parameters and variables for the following PowerCenter objects:
  • Sources.
    You can use parameters and variables in input fields related to sources.
  • Targets.
    You can use parameters and variables in input fields related to targets.
  • Transformations.
    You can use parameters and variables in input fields related to transformations.
  • Tasks.
    You can use parameters and variables in input fields related to tasks in the Workflow Manager.
  • Sessions.
    You can use parameters and variables in input fields related to Session tasks.
  • Workflows.
    You can use parameters and variables in input fields related to workflows.
  • Connections.
    You can use parameters and variables in input fields related to connection objects.
The following table lists the input fields related to sources where you can specify parameters and variables:
Source Type
Field
Valid Parameter and Variable Types
Relational
Source Table Name
Workflow variables, worklet variables, session parameters, mapping parameters, and mapping variables.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
PeopleSoft
SetID,
Effective date,
Tree name,
Set control value,
Extract date
All.
TIBCO
TIB/Adapter SDK repository URL
Service and service process variables.
Web Service
Endpoint URL
Mapping parameters and variables
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
The following table lists the input fields related to targets where you can specify parameters and variables:
Target Type
Field
Valid Parameter and Variable Types
Relational
Update override
Pre- and post-session SQL commands
All.
You can specify parameters and variables in these fields when you override them in the session properties (Mapping tab) in the Workflow Manager.
Relational
Target Table Name
Workflow variables, worklet variables, session parameters, mapping parameters, and mapping variables.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
XML
Cache directory
Service and service process variables.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
TIBCO
TIB/Adapter SDK repository URL
Service and service process variables.
Web Service
Endpoint URL
Mapping parameters and variables.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
The following table lists the input fields related to transformations where you can specify parameters and variables:
Transformation Type
Field
Valid Parameter and Variable Types
Transformations that allow you to use the Expression Editor
Transformation expressions
Mapping parameters and variables.
Aggregator, Joiner, Lookup, Rank, XML Generator
Cache directory
Service and service process variables.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
Aggregator, Joiner, Lookup, Rank, Sorter
Cache sizes
Mapping parameters.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
Custom, External Procedure, HTTP, XML Parser
Runtime location
Service and service process variables.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
Data Masking
Seed
Mapping parameters and variables.
External Procedure
Initialization properties
Service and service process variables.
HTTP
Base URL
Mapping parameters and variables.
Lookup
SQL override
Cache file name prefix
All.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
Lookup
Connection information
Session parameters $DBConnection
Name
and $AppConnection
Name
, connection variables $Source and $Target, mapping parameters and variables.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
Sorter
Default work directory
Service and service process variables.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
Source Qualifier (relational source)
SQL query
User-defined join
Source filter condition
Pre- and post-session SQL commands
All.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
SQL
Script file name
Mapping parameters and variables.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
Stored Procedure
Call text (unconnected Stored Procedure)
All.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
Stored Procedure
Connection information
Session parameter $DBConnection
Name
, connection variables $Source and $Target.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
Web Services Consumer
Endpoint URL
Mapping parameters and variables.
You can specify parameters and variables in this field when you override it in the session properties (Mapping tab) in the Workflow Manager.
The following table lists the input fields related to Workflow Manager tasks where you can specify parameters and variables:
Task Type
Field
Valid Parameter and Variable Types
Assignment task
Assignment (user defined variables and expression)
Workflow and worklet variables
Command task
Command (name and command)
Service, service process, workflow, and worklet variables
Command task
Pre- and post-session shell commands
All
Decision task
Decision name (condition to be evaluated)
Workflow and worklet variables
Email task
Email user name, subject, and text
Service, service process, workflow, and worklet variables
Event-Wait task
File watch name (predefined events)
Service, service process, workflow, and worklet variables
Link
Link condition
Service, service process, workflow, and worklet variables
Session
Timer task
Absolute time: Workflow date-time variable to calculate the wait
Workflow and worklet variables
The following table lists the input fields related to sessions where you can specify parameters and variables:
Tab
Field
Valid Parameter and Variable Types
Properties tab
Session log file name
Built-in session parameter $PMSessionLogFile.
Properties tab
Session log file directory
Service and service process variables.
Properties tab
Parameter file name
Workflow and worklet variables.
Properties tab
$Source and $Target connection values
Session parameters $DBConnection
Name
and $AppConnection
Name
, connection variables $Source and $Target.
Properties tab
Pushdown optimization session property
Mapping parameter $$PushdownConfig.
Config Object tab
Session log count
Service variable $PMSessionLogCount.
Config Object tab
Session error threshold
Service variable $PMSessionErrorThreshold.
Config Object tab
Table name prefix for relational error logs
All.
Config Object tab
Error log file name and directory
Service variables, service process variables, workflow variables, worklet variables, session parameters.
Config Object tab
Number of partitions for dynamic partitioning
Built-in session parameter $DynamicPartitionCount.
Mapping tab
Transformation properties that override properties you configure in a mapping
Varies according to property. For more information, see Where to Use Parameters and Variables.
Mapping tab
Relational connection values
Session parameter $DBConnection
Name
, connection variables $Source and $Target.
Mapping tab
Queue connection values
Session parameter $QueueConnection
Name.
You can override connection attributes for this connection type in the parameter file.
Mapping tab
FTP connection values
Session parameter $FTPConnection
Name.
You can override connection attributes for this connection type in the parameter file.
Mapping tab
Application connection values
Session parameter $AppConnection
Name.
You can override connection attributes for this connection type in the parameter file.
Mapping tab
External loader connection values
Session parameter $LoaderConnection
Name.
You can override connection attributes for this connection type in the parameter file.
Mapping tab
FTP remote file name
All.
Mapping tab
Lookup source file name and directory
Service variables, service process variables, workflow variables, worklet variables, session parameters.
Mapping tab
Pre- and post-session SQL commands (source and target)
All.
Mapping tab
Code page for file sources and targets
Workflow variables, worklet variables, session parameter $Param
Name.
Mapping tab
Source input file name and directory
Service variables, service process variables, workflow variables, worklet variables, session parameters.
Mapping tab
Source input file command
Service variables, service process variables, workflow variables, worklet variables, session parameters.
Mapping tab
Table owner name for relational sources
All.
Mapping tab
Target merge file name and directory
Service variables, service process variables, workflow variables, worklet variables, session parameters.
Mapping tab
Target merge command
Service variables, service process variables, workflow variables, worklet variables, session parameters.
Mapping tab
Target header and footer commands
Service variables, service process variables, workflow variables, worklet variables, session parameters.
Mapping tab
Target output file name and directory
Service variables, service process variables, workflow variables, worklet variables, session parameters.
Mapping tab
Target reject file name and directory
Service variables, service process variables, workflow variables, worklet variables, session parameters.
Mapping tab
Target table name prefix
All.
Mapping tab
Teradata FastExport temporary file
Service and service process variables.
Mapping tab
Control file content override for Teradata external loaders
All.
Mapping tab
Recovery cache directory for WebSphere MQ, JMS, SAP ALE IDoc, TIBCO, webMethods, Web Service Provider sources
Service and service process variables.
Mapping tab
Durable Subscription Name
Session parameter $Param
Name.
Mapping tab
MQ Source Qualifier filter condition
All.
Mapping tab
SAP stage file name and directory
Service variables, service process variables, workflow variables, worklet variables, session parameters.
Mapping tab
SAP source file directory
Service variables, service process variables, workflow variables, worklet variables, session parameters.
Components tab
Post-session email (user name, subject, and text)
All.
Components tab
Post-session email attachment file name
All.
The following table lists the input fields related to workflows where you can specify parameters and variables:
Tab
Field
Valid Parameter and Variable Types
Properties tab
Workflow log file name and directory
Service, service process, workflow, and worklet variables.
Properties tab
Workflow log count
Service variable $PMWorkflowLogCount.
General tab
Suspension email (user name, subject, and text)
Service, service process, workflow, and worklet variables.
The following table lists the input fields related to connection objects in the Workflow Manager where you can specify parameters and variables:
Connection Type
Field
Valid Parameter and Variable Types
Relational
Database user name, password
Session parameter $Param
Name.
Encrypt the password in the parameter file using the pmpasswd command line program with the CRYPT_DATA encryption type.
Relational: Source, Target, Lookup, Stored Procedure
Connection and transaction environment SQL
All.
FTP
User name, password for host machine
Session parameter $Param
Name.
Encrypt the password in the parameter file using the pmpasswd command line program with the CRYPT_DATA encryption type.
FTP
Default remote directory
All.
Application
Application user name, password
Session parameter $Param
Name.
Encrypt the password in the parameter file using the pmpasswd command line program with the CRYPT_DATA encryption type.
Application: Web Services Consumer
Endpoint URL
Session parameter $Param
Name
, mapping parameters and variables.
Application: HTTP
Base URL
Session parameter $Param
Name.
Application: JMS
JMS Destination
Session parameter $Param
Name.
Loader
Database user name, password
Session parameter $Param
Name.
Encrypt the password in the parameter file using the pmpasswd command line program with the CRYPT_DATA encryption type.


Updated July 04, 2018