Grid Processing

When a Power Center domain contains multiple nodes, you can configure workflows and sessions to run on a grid. When you run a workflow on a grid, the Integration Service runs a service process on each available node of the grid to increase performance and scalability. When you run a session on a grid, the Integration Service distributes session threads to multiple DTM processes on nodes in the grid to increase performance and scalability.
You create the grid and configure the Integration Service in the Administration Console. To run a workflow on a grid, you configure the workflow to run on the Integration Service associated with the grid. To run a session on a grid, configure the session to run on the grid.

The Integration Service distributes workflow tasks and session threads based on how you configure the workflow or session to run:

• Running workflows on a grid. The Integration Service distributes workflows across the nodes in a grid. It also distributes the Session, Command, and predefined Event-Wait tasks within workflows across the nodes in a grid.
• Running sessions on a grid. The Integration Service distributes session threads across nodes in a grid.

Note: To run workflows on a grid, you must have the Server grid option. To run sessions on a grid, you must have the Session on Grid option.

Running Workflows on a Grid:

When you run a workflow on a grid, the master service process runs the workflow and all tasks except Session, Command, and predefined Event-Wait tasks, which it may distribute to other nodes. The master service process is the Integration Service process that runs the workflow, monitors service processes running on other nodes, and runs the Load Balancer. The Scheduler runs on the master service process node, so it uses the date and time for the master service process node to start scheduled workflows.

The Load Balancer is the component of the Integration Service that dispatches Session, Command, and predefined Event-Wait tasks to the nodes in the grid. The Load Balancer distributes tasks based on node availability. If the Integration Service is configured to check resources, the Load Balancer also distributes tasks based on resource availability.

For example, a workflow contains a Session task, a Decision task, and a Command task. You specify a resource requirement for the Session task. The grid contains four nodes, and Node 4 is unavailable. The master service process runs the Start and Decision tasks. The Load Balancer distributes the Session and Command tasks to

nodes on the grid based on resource availability and node availability.

Running Sessions on a Grid:

When you run a session on a grid, the master service process runs the workflow and all tasks except Session, Command, and predefined Event-Wait tasks as it does when you run a workflow on a grid. The Scheduler runs on the master service process node, so it uses the date and time for the master service process node to start scheduled workflows. In addition, the Load Balancer distributes session threads to DTM processes running on different nodes.

When you run a session on a grid, the Load Balancer distributes session threads based on the following factors:

•  Node availability :- The Load Balancer verifies which nodes are currently running, enabled, and available for task dispatch.
•  Resource availability :- If the Integration Service is configured to check resources, it identifies nodes that have resources required by mapping objects in the session.
•  Partitioning configuration. The Load Balancer dispatches groups of session threads to separate nodes based on the partitioning configuration.

You might want to configure a session to run on a grid when the workflow contains a session that takes a long time to run.

Grid Connectivity and Recovery

When you run a workflow or session on a grid, service processes and DTM processes run on different nodes. Network failures can cause connectivity loss between processes running on separate nodes. Services may shut down unexpectedly, or you may disable the Integration Service or service processes while a workflow or session is running. The Integration Service failover and recovery behavior in these situations depends on the service process that is disabled, shuts down, or loses connectivity. Recovery behavior also depends on the following factors:

• High availability option:-When you have high availability, workflows fail over to another node if the node or service shuts down. If you do not have high availability, you can manually restart a workflow on another node to recover it.
• Recovery strategy:- You can configure a workflow to suspend on error. You configure a recovery strategy for tasks within the workflow. When a workflow suspends, the recovery behavior depends on the recovery strategy you configure for each task in the workflow.
• Shutdown mode:- When you disable an Integration Service or service process, you can specify that the service completes, aborts, or stops processes running on the service. Behavior differs when you disable the Integration Service or you disable a service process. Behavior also differs when you disable a master service process or a worker service process. The Integration Service or service process may also shut down unexpectedly. In this case, the failover and recovery behavior depend on which service process shuts down and the configured recovery strategy.
• Running mode:-If the workflow runs on a grid, the Integration Service can recover workflows and tasks on another node. If a session runs on a grid, you cannot configure a resume recovery strategy.
• Operating mode:- If the Integration Service runs in safe mode, recovery is disabled for sessions and workflows.

Note: You cannot configure an Integration Service to fail over in safe mode if it runs on a grid.

Incremental Aggregation

When we enable the session option-> Incremental Aggregation the Integration Service performs incremental aggregation, it passes source data through the mapping and uses historical cache data to perform aggregation calculations incrementally.

When using incremental aggregation, you apply captured changes in the source to aggregate calculations in a session. If the source changes incrementally and you can capture changes, you can configure the session to process those changes. This allows the Integration Service to update the target incrementally, rather than forcing it to process the entire source and recalculate the same data each time you run the session.

For example, you might have a session using a source that receives new data every day. You can capture those incremental changes because you have added a filter condition to the mapping that removes pre-existing data from the flow of data. You then enable incremental aggregation.

When the session runs with incremental aggregation enabled for the first time on March 1, you use the entire source. This allows the Integration Service to read and store the necessary aggregate data. On March 2, when you run the session again, you filter out all the records except those time-stamped March 2. The Integration Service then processes the new data and updates the target accordingly.Consider using incremental aggregation in the following circumstances:

• You can capture new source data. Use incremental aggregation when you can capture new source data each time you run the session. Use a Stored Procedure or Filter transformation to process new data.
• Incremental changes do not significantly change the target. Use incremental aggregation when the changes do not significantly change the target. If processing the incrementally changed source alters more than half the existing target, the session may not benefit from using incremental aggregation. In this case, drop the table and recreate the target with complete source data.

Note: Do not use incremental aggregation if the mapping contains percentile or median functions. The Integration Service uses system memory to process these functions in addition to the cache memory you configure in the session properties. As a result, the Integration Service does not store incremental aggregation values for percentile and median functions in disk caches.

Integration Service Processing for Incremental Aggregation

(i)The first time you run an incremental aggregation session, the Integration Service processes the entire source. At the end of the session, the Integration Service stores aggregate data from that session run in two files, the index file and the data file. The Integration Service creates the files in the cache directory specified in the Aggregator transformation properties.

(ii)Each subsequent time you run the session with incremental aggregation, you use the incremental source changes in the session. For each input record, the Integration Service checks historical information in the index file for a corresponding group. If it finds a corresponding group, the Integration Service performs the aggregate operation incrementally, using the aggregate data for that group, and saves the incremental change. If it does not find a corresponding group, the Integration Service creates a new group and saves the record data.

(iii)When writing to the target, the Integration Service applies the changes to the existing target. It saves modified aggregate data in the index and data files to be used as historical data the next time you run the session.

(iv) If the source changes significantly and you want the Integration Service to continue saving aggregate data for future incremental changes, configure the Integration Service to overwrite existing aggregate data with new aggregate data.

Each subsequent time you run a session with incremental aggregation, the Integration Service creates a backup of the incremental aggregation files. The cache directory for the Aggregator transformation must contain enough disk space for two sets of the files.

(v)When you partition a session that uses incremental aggregation, the Integration Service creates one set of cache files for each partition.

The Integration Service creates new aggregate data, instead of using historical data, when you perform one of the following tasks:

• Save a new version of the mapping.
• Configure the session to reinitialize the aggregate cache.
• Move the aggregate files without correcting the configured path or directory for the files in the session properties.
• Change the configured path or directory for the aggregate files without moving the files to the new location.
• Delete cache files.
• Decrease the number of partitions.

When the Integration Service rebuilds incremental aggregation files, the data in the previous files is lost.

Note: To protect the incremental aggregation files from file corruption or disk failure, periodically back up the files.

Preparing for Incremental Aggregation:

When you use incremental aggregation, you need to configure both mapping and session properties:

• Implement mapping logic or filter to remove pre-existing data.
• Configure the session for incremental aggregation and verify that the file directory has enough disk space for the aggregate files.

Configuring the Mapping

Before enabling incremental aggregation, you must capture changes in source data. You can use a Filter or Stored Procedure transformation in the mapping to remove pre-existing source data during a session.

Configuring the Session

Use the following guidelines when you configure the session for incremental aggregation:

(i) Verify the location where you want to store the aggregate files.

• The index and data files grow in proportion to the source data. Be sure the cache directory has enough disk space to store historical data for the session.
• When you run multiple sessions with incremental aggregation, decide where you want the files stored. Then, enter the appropriate directory for the process variable, $PMCacheDir, in the Workflow Manager. You can enter session-specific directories for the index and data files. However, by using the process variable for all sessions using incremental aggregation, you can easily change the cache directory when necessary by changing $PMCacheDir.
• Changing the cache directory without moving the files causes the Integration Service to reinitialize the aggregate cache and gather new aggregate data.
• In a grid, Integration Services rebuild incremental aggregation files they cannot find. When an Integration Service rebuilds incremental aggregation files, it loses aggregate history.

(ii) Verify the incremental aggregation settings in the session properties.

• You can configure the session for incremental aggregation in the Performance settings on the Properties tab.
• You can also configure the session to reinitialize the aggregate cache. If you choose to reinitialize the cache, the Workflow Manager displays a warning indicating the Integration Service overwrites the existing cache and a reminder to clear this option after running the session.

WORKING WITH LINKS

• Use links to connect each workflow task.
• We can specify conditions with links to create branches in the workflow.
• The Workflow Manager does not allow us to use links to create loops in the workflow. Each link in the workflow can run only once.

Valid Workflow :

Example of loop:

Specifying Link Conditions:

• Once we create links between tasks, we can specify conditions for each link to determine the order of execution in the workflow.
• If we do not specify conditions for each link, the Integration Service runs the next task in the workflow by default.
• Use predefined or user-defined workflow variables in the link condition.

Steps:

1. In the Workflow Designer workspace, double-click the link you want to specify.
2. The Expression Editor appears.
3. In the Expression Editor, enter the link condition. The Expression Editor provides predefined workflow variables, user-defined workflow variables, variable functions, and Boolean and arithmetic operators.
4. Validate the expression using the Validate button.

Using the Expression Editor:

The Workflow Manager provides an Expression Editor for any expressions in the workflow. We can enter expressions using the Expression Editor for the following:

• Link conditions
• Decision task
• Assignment task

PARTITIONING

• A pipeline consists of a source qualifier and all the transformations and Targets that receive data from that source qualifier.
• When the Integration Service runs the session, it can achieve higher Performance by partitioning the pipeline and performing the extract, Transformation, and load for each partition in parallel.

A partition is a pipeline stage that executes in a single reader, transformation, or Writer thread. The number of partitions in any pipeline stage equals the number of Threads in the stage. By default, the Integration Service creates one partition in every pipeline stage.

 PARTITIONING ATTRIBUTES

1. Partition points

• By default, IS sets partition points at various transformations in the pipeline.
• Partition points mark thread boundaries and divide the pipeline into stages.
• A stage is a section of a pipeline between any two partition points.

2. Number of Partitions

• we can define up to 64 partitions at any partition point in a pipeline.
• When we increase or decrease the number of partitions at any partition point, the Workflow Manager increases or decreases the number of partitions at all Partition points in the pipeline.
• increasing the number of partitions or partition points increases the number of threads.
• The number of partitions we create equals the number of connections to the source or target. For one partition, one database connection will be used.

3. Partition types

• The Integration Service creates a default partition type at each partition point.
• If we have the Partitioning option, we can change the partition type. This option is purchased separately.
• The partition type controls how the Integration Service distributes data among partitions at partition points.

PARTITIONING TYPES

1. Round Robin Partition Type

• In round-robin partitioning, the Integration Service distributes rows of data evenly to all partitions.
• Each partition processes approximately the same number of rows.
• Use round-robin partitioning when we need to distribute rows evenly and do not need to group data among partitions.

2. Pass-Through Partition Type

• In pass-through partitioning, the Integration Service processes data without Redistributing rows among partitions.
• All rows in a single partition stay in that partition after crossing a pass-Through partition point.
• Use pass-through partitioning when we want to increase data throughput, but we do not want to increase the number of partitions.

3. Database Partitioning Partition Type

• Use database partitioning for Oracle and IBM DB2 sources and IBM DB2 targets only.
• Use any number of pipeline partitions and any number of database partitions.
• We can improve performance when the number of pipeline partitions equals the number of database partitions.

Database Partitioning with One Source

When we use database partitioning with a source qualifier with one source, the Integration Service generates SQL queries for each database partition and distributes the data from the database partitions among the session partitions Equally.

For example, when a session has three partitions and the database has five partitions, 1^st and 2^nd session partitions will receive data from 2 database partitions each. Thus four DB partitions used. 3^rd Session partition will receive Data from the remaining 1 DB partition.

Partitioning a Source Qualifier with Multiple Sources Tables

The Integration Service creates SQL queries for database partitions based on the Number of partitions in the database table with the most partitions.

If the session has three partitions and the database table has two partitions, one of the session partitions receives no data.

4. Hash Auto-Keys Partition Type

• The Integration Service uses all grouped or sorted ports as a compound Partition key.
• Use hash auto-keys partitioning at or before Rank, Sorter, Joiner, and Unsorted Aggregator transformations to ensure that rows are grouped Properly before they enter these transformations.

5. Hash User-Keys Partition Type

• The Integration Service uses a hash function to group rows of data among Partitions.
• we define the number of ports to generate the partition key.
• we choose the ports that define the partition key .

6. Key range Partition Type

• We specify one or more ports to form a compound partition key.
• The Integration Service passes data to each partition depending on the Ranges we specify for each port.
• Use key range partitioning where the sources or targets in the pipeline are Partitioned by key range.
• Example: Customer 1-100 in one partition, 101-200 in another and so on. We Define the range for each partition.

MAPPLETS

• A mapplet is a reusable object that we create in the Mapplet Designer.
• It contains a set of transformations and lets us reuse that transformation logic in multiple mappings.
• Created in Mapplet Designer in Designer Tool.

We need to use same set of 5 transformations in say 10 mappings. So instead of making 5 transformations in every 10 mapping, we create a mapplet of these 5 transformations. Now we use this mapplet in all 10 mappings. Example: To create a surrogate key in target. We create a mapplet using a stored procedure to create Primary key for target table. We give target table name and key column name as input to mapplet and get the Surrogate key as output.

Mapplets help simplify mappings in the following ways:

• Include source definitions: Use multiple source definitions and source qualifiers to provide source data for a mapping.
• Accept data from sources in a mapping
• Include multiple transformations: As many transformations as we need.
• Pass data to multiple transformations: We can create a mapplet to feed data to multiple transformations. Each Output transformation in a mapplet represents one output group in a mapplet.
• Contain unused ports: We do not have to connect all mapplet input and output ports in a mapping.

Mapplet Input:

Mapplet input can originate from a source definition and/or from an Input transformation in the mapplet. We can create multiple pipelines in a mapplet.

• We use Mapplet Input transformation to give input to mapplet.
• Use of Mapplet Input transformation is optional.

Mapplet Output:

The output of a mapplet is not connected to any target table.

• We must use Mapplet Output transformation to store mapplet output.
• A mapplet must contain at least one Output transformation with at least one connected port in the mapplet.

Example1: We will join EMP and DEPT table. Then calculate total salary. Give the output to mapplet out transformation.

· EMP and DEPT will be source tables.

· Output will be given to transformation Mapplet_Out.

Steps:

1. Open folder where we want to create the mapping.
2. Click Tools -> Mapplet Designer.
3. Click Mapplets-> Create-> Give name. Ex: mplt_example1
4. Drag EMP and DEPT table.
5. Use Joiner transformation as described earlier to join them.
6. Transformation -> Create -> Select Expression for list -> Create -> Done
7. Pass all ports from joiner to expression and then calculate total salary as described in expression transformation.
8. Now Transformation -> Create -> Select Mapplet Out from list –> Create -> Give name and then done.
9. Pass all ports from expression to Mapplet output.
10. Mapplet -> Validate
11. Repository -> Save

Use of mapplet in mapping:

• We can mapplet in mapping by just dragging the mapplet from mapplet folder on left pane as we drag source and target tables.
• When we use the mapplet in a mapping, the mapplet object displays only the ports from the Input and Output transformations. These are referred to as the mapplet input and mapplet output ports.
• Make sure to give correct connection information in session.

Making a mapping: We will use mplt_example1, and then create a filter

transformation to filter records whose Total Salary is >= 1500.

· mplt_example1 will be source.

· Create target table same as Mapplet_out transformation as in picture above. Creating Mapping

1. Open folder where we want to create the mapping.
2. Click Tools -> Mapping Designer.
3. Click Mapping-> Create-> Give name. Ex: m_mplt_example1
4. Drag mplt_Example1 and target table.
5. Transformation -> Create -> Select Filter for list -> Create -> Done.
6. Drag all ports from mplt_example1 to filter and give filter condition.
7. Connect all ports from filter to target. We can add more transformations after filter if needed.
8. Validate mapping and Save it.

• Make session and workflow.
• Give connection information for mapplet source tables.
• Give connection information for target table.
• Run workflow and see result.

MAPPING PARAMETERS & VARIABLES

Mapping parameters and variables represent values in mappings and mapplets.

When we use a mapping parameter or variable in a mapping, first we declare the mapping parameter or variable for use in each mapplet or mapping. Then, we define a value for the mapping parameter or variable before we run the session.

MAPPING PARAMETERS

• A mapping parameter represents a constant value that we can define before running a session.
• A mapping parameter retains the same value throughout the entire session.

Example: When we want to extract records of a particular month during ETL process, we will create a Mapping Parameter of data type and use it in query to compare it with the timestamp field in SQL override.

• After we create a parameter, it appears in the Expression Editor.
• We can then use the parameter in any expression in the mapplet or mapping.
• We can also use parameters in a source qualifier filter, user-defined join, or extract override, and in the Expression Editor of reusable transformations.

MAPPING VARIABLES

• Unlike mapping parameters, mapping variables are values that can change between sessions.
• The Integration Service saves the latest value of a mapping variable to the repository at the end of each successful session.
• We can override a saved value with the parameter file.
• We can also clear all saved values for the session in the Workflow Manager.

We might use a mapping variable to perform an incremental read of the source. For example, we have a source table containing time stamped transactions and we want to evaluate the transactions on a daily basis. Instead of manually entering a session override to filter source data each time we run the session, we can create a mapping variable, $$IncludeDateTime. In the source qualifier, create a filter to read only rows whose transaction date equals $$IncludeDateTime, such as:

TIMESTAMP = $$IncludeDateTime

In the mapping, use a variable function to set the variable value to increment one day each time the session runs. If we set the initial value of $$IncludeDateTime to 8/1/2004, the first time the Integration Service runs the session, it reads only rows dated 8/1/2004. During the session, the Integration Service sets $$IncludeDateTime to 8/2/2004. It saves 8/2/2004 to the repository at the end of the session. The next time it runs the session, it reads only rows from August 2, 2004.

Used in following transformations:

• Expression
• Filter
• Router
• Update Strategy

Initial and Default Value:

When we declare a mapping parameter or variable in a mapping or a mapplet, we can enter an initial value. When the Integration Service needs an initial value, and we did not declare an initial value for the parameter or variable, the Integration Service uses a default value based on the data type of the parameter or variable.

Data ->Default Value

Numeric ->0

String ->Empty String

Date time ->1/1/1

Variable Values: Start value and current value of a mapping variable

Start Value:

The start value is the value of the variable at the start of the session. The Integration Service looks for the start value in the following order:

1. Value in parameter file
2. Value saved in the repository
3. Initial value
4. Default value

Current Value:

The current value is the value of the variable as the session progresses. When a session starts, the current value of a variable is the same as the start value. The final current value for a variable is saved to the repository at the end of a successful session. When a session fails to complete, the Integration Service does not update the value of the variable in the repository.

Note: If a variable function is not used to calculate the current value of a mapping variable, the start value of the variable is saved to the repository.

Variable Data type and Aggregation Type When we declare a mapping variable in a mapping, we need to configure the Data type and aggregation type for the variable. The IS uses the aggregate type of a Mapping variable to determine the final current value of the mapping variable.

Aggregation types are:

•  Count: Integer and small integer data types are valid only.
•  Max: All transformation data types except binary data type are valid.
•  Min: All transformation data types except binary data type are valid.

Variable Functions

Variable functions determine how the Integration Service calculates the current value of a mapping variable in a pipeline.

SetMaxVariable: Sets the variable to the maximum value of a group of values. It ignores rows marked for update, delete, or reject. Aggregation type set to Max.

SetMinVariable: Sets the variable to the minimum value of a group of values. It ignores rows marked for update, delete, or reject. Aggregation type set to Min.

SetCountVariable: Increments the variable value by one. It adds one to the variable value when a row is marked for insertion, and subtracts one when the row is Marked for deletion. It ignores rows marked for update or reject. Aggregation type set to Count.

SetVariable: Sets the variable to the configured value. At the end of a session, it compares the final current value of the variable to the start value of the variable. Based on the aggregate type of the variable, it saves a final value to the repository.

Creating Mapping Parameters and Variables

1. Open the folder where we want to create parameter or variable.
2. In the Mapping Designer, click Mappings > Parameters and Variables. -or- In the Mapplet Designer, click Mapplet > Parameters and Variables.
3. Click the add button.
4. Enter name. Do not remove $$ from name.
5. Select Type and Data type. Select Aggregation type for mapping variables.
6. Give Initial Value. Click ok.

Example: Use of Mapping of Mapping Parameters and Variables

• EMP will be source table.
• Create a target table MP_MV_EXAMPLE having columns: EMPNO, ENAME, DEPTNO, TOTAL_SAL, MAX_VAR, MIN_VAR, COUNT_VAR and SET_VAR.
• TOTAL_SAL = SAL+ COMM + $$BONUS (Bonus is mapping parameter that changes every month)
• SET_VAR: We will be added one month to the HIREDATE of every employee.
• Create shortcuts as necessary.

Creating Mapping

1. Open folder where we want to create the mapping.
2. Click Tools -> Mapping Designer.
3. Click Mapping-> Create-> Give name. Ex: m_mp_mv_example
4. Drag EMP and target table.
5. Transformation -> Create -> Select Expression for list -> Create –>  Done.
6. Drag EMPNO, ENAME, HIREDATE, SAL, COMM and DEPTNO to Expression.
7. Create Parameter $$Bonus and Give initial value as 200.
8. Create variable $$var_max of MAX aggregation type and initial value 1500.
9. Create variable $$var_min of MIN aggregation type and initial value 1500.
10. Create variable $$var_count of COUNT aggregation type and initial value 0. COUNT is visible when datatype is INT or SMALLINT.
11. Create variable $$var_set of MAX aggregation type.

12. Create 5 output ports out_ TOTAL_SAL, out_MAX_VAR, out_MIN_VAR,

out_COUNT_VAR and out_SET_VAR.

13. Open expression editor for TOTAL_SAL. Do the same as we did earlier for SAL+ COMM. To add $$BONUS to it, select variable tab and select the parameter from mapping parameter. SAL + COMM + $$Bonus

14. Open Expression editor for out_max_var.

15. Select the variable function SETMAXVARIABLE from left side pane. Select

$$var_max from variable tab and SAL from ports tab as shown below.SETMAXVARIABLE($$var_max,SAL)

17. Open Expression editor for out_min_var and write the following expression:

SETMINVARIABLE($$var_min,SAL). Validate the expression.

18. Open Expression editor for out_count_var and write the following expression:

SETCOUNTVARIABLE($$var_count). Validate the expression.

19. Open Expression editor for out_set_var and write the following expression:

SETVARIABLE($$var_set,ADD_TO_DATE(HIREDATE,'MM',1)). Validate.

20. Click OK. Expression Transformation below:

21. Link all ports from expression to target and Validate Mapping and Save it.

22. See mapping picture on next page.

PARAMETER FILE

• A parameter file is a list of parameters and associated values for a workflow, worklet, or session.
• Parameter files provide flexibility to change these variables each time we run a workflow or session.
• We can create multiple parameter files and change the file we use for a session or workflow. We can create a parameter file using a text editor such as WordPad or Notepad.
• Enter the parameter file name and directory in the workflow or session properties.

A parameter file contains the following types of parameters and variables:

• Workflow variable: References values and records information in a workflow.
• Worklet variable: References values and records information in a worklet. Use predefined worklet variables in a parent workflow, but we cannot use workflow variables from the parent workflow in a worklet.
•  Session parameter: Defines a value that can change from session to session, such as a database connection or file name.
• Mapping parameter and Mapping variable

USING A PARAMETER FILE

Parameter files contain several sections preceded by a heading. The heading identifies the Integration Service, Integration Service process, workflow, worklet, or session to which we want to assign parameters or variables.

• Make session and workflow.
• Give connection information for source and target table.
• Run workflow and see result.

Sample Parameter File for Our example:

In the parameter file, folder and session names are case sensitive.

Create a text file in notepad with name Para_File.txt

[Practice.ST:s_m_MP_MV_Example]

$$Bonus=1000

$$var_max=500

$$var_min=1200

$$var_count=0

CONFIGURING PARAMTER FILE

We can specify the parameter file name and directory in the workflow or session properties.

To enter a parameter file in the workflow properties:

1. Open a Workflow in the Workflow Manager.

2. Click Workflows > Edit.

3. Click the Properties tab.

4. Enter the parameter directory and name in the Parameter Filename field.

5. Click OK.

To enter a parameter file in the session properties:

1. Open a session in the Workflow Manager.

2. Click the Properties tab and open the General Options settings.

3. Enter the parameter directory and name in the Parameter Filename field.

4. Example: D:\Files\Para_File.txt or $PMSourceFileDir\Para_File.txt

5. Click OK.

Target Load Order

When you use a mapplet in a mapping, the Mapping Designer lets you set the target load plan for sources within the mapplet.

Setting the Target Load Order

You can configure the target load order for a mapping containing any type of target definition. In the Designer, you can set the order in which the Integration Service sends rows to targets in different target load order groups in a mapping. A target load order group is the collection of source qualifiers, transformations, and targets linked together in a mapping. You can set the target load order if you want to maintain referential integrity when inserting, deleting, or updating tables that have the primary key and foreign key constraints.

The Integration Service reads sources in a target load order group concurrently, and it processes target load order groups sequentially.

To specify the order in which the Integration Service sends data to targets, create one source qualifier for each target within a mapping. To set the target load order, you then determine in which order the Integration Service reads each source in the mapping.

The following figure shows two target load order groups in one mapping:

In this mapping, the first target load order group includes ITEMS, SQ_ITEMS, and T_ITEMS. The second target load order group includes all other objects in the mapping, including the TOTAL_ORDERS target. The Integration Service processes the first target load order group, and then the second target load order group.

When it processes the second target load order group, it reads data from both sources at the same time.

To set the target load order:

1. Create a mapping that contains multiple target load order groups.
2. Click Mappings > Target Load Plan.
3. The Target Load Plan dialog box lists all Source Qualifier transformations in the mapping and the targets that receive data from each source qualifier.
4. Select a source qualifier from the list.
5. Click the Up and Down buttons to move the source qualifier within the load order.
6. Repeat steps 3 to 4 for other source qualifiers you want to reorder. Click OK.