Preface
Understanding Pipeline Partitioning
- Understanding Pipeline Partitioning Overview
- Partitioning Attributes
- Dynamic Partitioning
- Cache Partitioning
- Mapping Variables in Partitioned Pipelines
- Partitioning Rules
  - Partition Restrictions for Editing Objects
    - Before You Create a Session
    - After You Create a Session with Multiple Partitions
  - Partition Restrictions for PowerExchange
- Configuring Partitioning
Partition Points
- Partition Points Overview
- Adding and Deleting Partition Points
  - Rules and Guidelines for Adding and Deleting Partition Points
- Partitioning Relational Sources
  - Entering an SQL Query
  - Entering a Filter Condition
- Partitioning File Sources
- Partitioning Relational Targets
  - Database Compatibility
- Partitioning File Targets
  - Configuring Connection Settings
  - Configuring File Properties
    - Configuring Commands for Partitioned File Targets
    - Configuring Merge Options
- Partitioning Custom Transformations
- Partitioning Joiner Transformations
- Partitioning Lookup Transformations
  - Cache Partitioning Lookup Transformations
    - Sharing Partitioned Caches
  - Partitioning Pipeline Lookup Transformation Cache
- Partitioning Sequence Generator Transformations
- Partitioning Sorter Transformations
  - Configuring Sorter Transformation Work Directories
- Partitioning XML Generator Transformations
- Restrictions for Transformations
  - Restrictions for Numerical Functions
Partition Types
- Partition Types Overview
  - Setting Partition Types in the Pipeline
- Setting Partition Types
- Database Partitioning Partition Type
  - Partitioning Database Sources
  - Target Database Partitioning
    - Rules and Guidelines for Target Database Partitioning
- Hash Auto-Keys Partition Type
- Hash User Keys Partition Type
- Key Range Partition Type
  - Adding a Partition Key
  - Adding Key Ranges
    - Adding Filter Conditions
    - Rules and Guidelines for Creating Key Ranges
- Pass-Through Partition Type
- Round-Robin Partition Type
Pushdown Optimization
- Pushdown Optimization Overview
- Pushdown Optimization Types
- Active and Idle Databases
- Working with Databases
- Pushdown Compatibility
- Error Handling, Logging, and Recovery
- Working with Slowly Changing Dimensions
- Working with Sequences and Views
- Using the $$PushdownConfig Mapping Parameter
- Configuring Sessions for Pushdown Optimization
Pushdown Optimization and Transformations
- Pushdown Optimization and Transformations Overview
  - General Pushdown Restrictions
- Aggregator Transformation
- Expression Transformation
- Filter Transformation
- Joiner Transformation
- Lookup Transformation
  - Unconnected Lookup Transformation
  - Lookup Transformation with an SQL Override
- Router Transformation
- Sequence Generator Transformation
- Sorter Transformation
- Source Qualifier Transformation
  - Source Qualifier Transformation with an SQL Override
- Target
- Union Transformation
- Update Strategy Transformation
Real-time Processing
- Real-time Processing Overview
- Understanding Real-time Data
- Configuring Real-time Sessions
- Terminating Conditions
- Flush Latency
- Commit Type
- Message Recovery
  - Prerequisites
  - Steps to Enable Message Recovery
- Recovery File
- Recovery Table
- Recovery Queue and Recovery Topic
  - Message Processing
  - Message Recovery
- Recovery Ignore List
- Stopping Real-time Sessions
- Restarting and Recovering Real-time Sessions
- Rules and Guidelines for Real-time Sessions
- Rules and Guidelines for Message Recovery
- Real-time Processing Example
- PowerCenter Real-time Products
Commit Points
- Commit Points Overview
- Target-Based Commits
- Source-Based Commits
  - Determining the Commit Source
  - Switching from Source-Based to Target-Based Commit
    - Connecting XML Sources in a Mapping
    - Connecting Multiple Output Group Custom Transformations in a Mapping
- User-Defined Commits
  - Rolling Back Transactions
- Understanding Transaction Control
- Setting Commit Properties
Row Error Logging
- Row Error Logging Overview
  - Error Log Code Pages
- Understanding the Error Log Tables
- Understanding the Error Log File
- Configuring Error Log Options
Workflow Recovery
- Workflow Recovery Overview
- State of Operation
- Recovery Options
- Suspending the Workflow
  - Configuring Suspension Email
- Configuring Workflow Recovery
  - Recovering Stopped, Aborted, and Terminated Workflows
  - Recovering Suspended Workflows
- Configuring Task Recovery
  - Task Recovery Strategies
    - Command Task Strategies
    - Session Task Strategies
  - Automatically Recovering Terminated Tasks
- Resuming Sessions
- Working with Repeatable Data
- Steps to Recover Workflows and Tasks
- Rules and Guidelines for Session Recovery
  - Configuring Recovery to Resume from the Last Checkpoint
  - Unrecoverable Workflows or Tasks
Stopping and Aborting
- Stopping and Aborting Overview
- Types of Errors
  - Threshold Errors
  - Fatal Errors
- Integration Service Handling for Session Failure
- Stopping or Aborting the Workflow
  - Stopping or Aborting a Task
    - Stopping or Aborting a Session Task
- Steps to Stop or Abort
Concurrent Workflows
- Concurrent Workflows Overview
- Configuring Unique Workflow Instances
  - Recovering Workflow Instances by Instance Name
  - Rules and Guidelines for Running Concurrent Instances of the Same Instance Name
- Configuring Concurrent Workflows of the Same Name
- Using Parameters and Variables
  - Accessing the Run Instance Name or Run ID
- Steps to Configure Concurrent Workflows
- Starting and Stopping Concurrent Workflows
- Monitoring Concurrent Workflows
- Viewing Session and Workflow Logs
  - Log Files for Unique Workflow Instances
  - Log Files for Workflow Instances of the Same Name
- Rules and Guidelines for Concurrent Workflows
Grid Processing
- Grid Processing Overview
- Running Workflows on a Grid
- Running Sessions on a Grid
- Working with Partition Groups
- Grid Connectivity and Recovery
- Configuring a Workflow or Session to Run on a Grid
  - Rules and Guidelines for Configuring a Workflow or Session to Run on a Grid
Load Balancer
- Load Balancer Overview
- Assigning Service Levels to Workflows
- Assigning Resources to Tasks
Workflow Variables
- Workflow Variables Overview
- Predefined Workflow Variables
- User-Defined Workflow Variables
- Using Worklet Variables
- Assigning Variable Values in a Worklet
  - Passing Variable Values between Worklets
  - Configuring Variable Assignments
Parameters and Variables in Sessions
- Working with Session Parameters
- Mapping Parameters and Variables in Sessions
- Assigning Parameter and Variable Values in a Session
  - Passing Parameter and Variable Values between Sessions
  - Configuring Variable Assignments
Parameter Files
- Parameter Files Overview
- Parameter and Variable Types
- Where to Use Parameters and Variables
- Overriding Connection Attributes in the Parameter File
- Parameter File Structure
- Configuring the Parameter File Name and Location
  - Using a Parameter File with Workflows or Sessions
  - Using a Parameter File with pmcmd
- Parameter File Example
- Guidelines for Creating Parameter Files
- Troubleshooting Parameters and Parameter Files
- Tips for Parameters and Parameter Files
FastExport
- Using FastExport Overview
- Step 1. Create a FastExport Connection
  - Verifying the Code Page Mapping File
- Step 2. Change the Reader
- Step 3. Change the Source Connection
- Step 4. Override the Control File (Optional)
- Rules and Guidelines for Using FastExport
External Loading
- External Loading Overview
  - Before You Begin
- External Loader Behavior
- Loading to IBM DB2
- Loading to Oracle
- Loading to Sybase IQ
- Loading to Teradata
- Configuring External Loading in a Session
- Troubleshooting External Loading
FTP
- FTP Overview
  - Rules and Guidelines for Using FTP
- SFTP
- Integration Service Behavior
  - Using FTP with Source Files
  - Using FTP with Target Files
- Configuring FTP in a Session
Session Caches
- Session Caches Overview
- Cache Memory
- Cache Files
  - Naming Convention for Cache Files
  - Cache File Directory
- Configuring the Cache Size
- Cache Partitioning
  - Configuring the Cache Size for Cache Partitioning
- Aggregator Caches
- Joiner Caches
- Lookup Caches
  - Sharing Caches
  - Configuring the Cache Sizes for a Lookup Transformation
- Rank Caches
  - Configuring the Cache Sizes for a Rank Transformation
- Sorter Caches
  - Configuring the Cache Size for a Sorter Transformation
- XML Target Caches
  - Configuring the Cache Size for an XML Target
- Optimizing the Cache Size
Incremental Aggregation
- Incremental Aggregation Overview
- Integration Service Processing for Incremental Aggregation
- Reinitializing the Aggregate Files
- Moving or Deleting the Aggregate Files
  - Finding Index and Data Files
- Partitioning Guidelines with Incremental Aggregation
- Preparing for Incremental Aggregation
  - Configuring the Mapping
  - Configuring the Session
Session Log Interface
- Session Log Interface Overview
- Implementing the Session Log Interface
  - The Integration Service and the Session Log Interface
  - Rules and Guidelines for Implementing the Session Log Interface
- Functions in the Session Log Interface
- Session Log Interface Example
  - Building the External Session Log Library
    - Building the Library in UNIX
    - Building the Library in Windows
  - Using the External Session Log Library
Understanding Buffer Memory
- Understanding Buffer Memory Overview
- Automatic Buffer Memory Settings
  - Using Session Configuration Objects for Memory Configuration
- Configuring Buffer Memory
- Configuring Session Cache Memory
  - Session Cache Limits
  - Configuring Automatic Memory Settings for Session Caches
High Precision Data
- High Precision Data Overview
- Bigint
- Decimal

Advanced Workflow Guide

10.5
- 10.5.7
- 10.5.6
- 10.4.1
- 10.4.0

Back Next

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:

In the Designer, edit the target instance, enter session parameter $ParamMyOverride in the Update Override field, and save the mapping.

In the Workflow Manager, configure the workflow or session to use a parameter file.

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	See Where to Use Parameters and Variables.
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.

Rename Saved Search

Table of Contents

Advanced Workflow Guide

Advanced Workflow Guide

Where to Use Parameters and Variables

Where to Use Parameters and Variables