Workflow Manager and General Tuning Tips

1
Workflow Manager and General Tuning Tips
2
Topics to discuss

• Working with Workflows
• Working with Tasks
• General Tuning Tips

3
Working with Workflows
A workflow is a set of instructions that tells
the PowerCenter Server how to execute tasks
such as sessions, email notifications, and
shell Commands.
4
Working with Workflows
Developing New Workflows. Everybody has
created more than 1 workflow !!
5
Working with Workflows - Links

• Working with Links
• Use links to connect each workflow task. You can
  specify conditions with links to create branches
  in the workflow. The Workflow Manager does not
  allow you to use links to create loops in the
  workflow. Each link in the workflow can execute
  only once.
• a valid workflow

6
Working with Workflows - Links

• The Workflow Manager does not allow you to create
  a workflow that contains a loop
• Invalid workflow-

7
Working with Workflows - Links

• Specifying Link Conditions
• Once you create links between tasks, you can
  specify conditions for each link to determine the
  order of execution in the workflow. If you do not
  specify conditions for each link, the PowerCenter
  Server executes the next task in the workflow by
  default.
• You can use pre-defined or user-defined
  workflow variables(?) in the link condition. If
  the link condition evaluates to True, the
  PowerCenter Server executes the next task in the
  workflow. If the link condition evaluates to
  False, the PowerCenter Server does not execute
  the next task in the workflow.
• You can view results of link evaluation
  during workflow runs in the workflow log file.

8
Working with Workflows - Variables

• Pre-defined workflow variables. The Workflow
  Manager provides pre-defined workflow variables
  for tasks within a workflow.
• User-defined workflow variables. You create
  user-defined workflow variables when you create a
  workflow.

9
Working with Workflows - Variables

• Pre-defined workflow variables
• Task-specific variables 2. System
  variables
• -Condition -SrcSuccessRows -SYSDATE
• -EndTime -StartTime -WORKFLOWSTARTTIME
• -ErrorCode -Status
• -ErrorMsg -TgtFailedRows
• -FirstErrorCode -TgtSuccessRows
• -FirstErrorMsg -TotalTransErrors
• -PrevTaskStatus
• -SrcFailedRows

10
Working with Workflows - Variables

• User-defined workflow variables

11
Working with Workflows - Variables

• Start and current value-
• Value in parameter file
• Value saved in the repository (if the variable is
  persistent)
• User-specified default value
• Datatype default value
• Datatype
  Workflow Manager Default Value
• Date/time 1/1/1753 A.D.
• Double 0
• Integer 0
• Nstring Empty string

12
Working with Workflows - Scheduler

• You can schedule a workflow to run continuously,
  repeat at a given time or
• interval, or you can manually start a workflow.
  The PowerCenter Server runs a
• scheduled workflow as configured.
• By default, the workflow runs on demand. You can
  change the schedule settings
• by editing the scheduler. If you change schedule
  settings, the PowerCenter Server
• reschedules the workflow according to the new
  settings.
• When a workflow fails, the PowerCenter Server
  removes the workflow from the
• schedule, and you must manually reschedule it.

13
Working with Workflows - Scheduler
14
Working with Workflows - Suspending

• When a task in the workflow fails, you might want
  to suspend the workflow, fix the
• error, and resume or recover the workflow. The
  PowerCenter Server suspends the
• workflow if you enable the Suspend On Error
  option in the workflow properties.
• You can optionally set a suspension email so the
  PowerCenter Server sends an
• email when it suspends a workflow.
• When you enable the Suspend On Error option, the
  PowerCenter Server
• suspends the workflow when one of the following
  fails
• Session
• Command
• Worklet
• Email

15
Questions

• ?

16
Working with Tasks

• Assignment
• Command
• Control
• Decision
• Email
• Event-Raise
• Event-Wait
• Session
• Timer

17
Working with Tasks - Assignment

• The Assignment task allows you to assign a value
  to a user-defined workflow variable. To use an
  Assignment task in the workflow, first create and
  add the Assignment task to the workflow. Then
  configure the Assignment task to assign values or
  expressions to user-defined variables. After you
  assign a value to a variable using the Assignment
  task, the PowerCenter Server uses the assigned
  value for the variable during the remainder of
  the workflow.

18
Working with Tasks - Commands

• If you select the Run If Previous Completed
  option, when one of the commands in the Command
  task fails, the PowerCenter Server stops running
  the rest of the commands and fails the task. If
  you do not select the Run If Previous Completed
  option, the PowerCenter Server runs all the
  commands in the Command task and treats the task
  as completed, even if a command fails.

19
Working with Tasks - Example

• Example-

20
Working with Tasks - Control

• You can use the Control task to stop, abort, or
  fail the top-level workflow or the parent
  workflow based on an input link condition. A
  parent workflow or worklet is the workflow or
  worklet that contains the Control task.

21
Working with Tasks - Control

• Example-
• This can be used in a worklet. And the condition
  given in the control task will be Stop top-level
  workflow. So whenever the decision routs the flow
  towards the control task, an email will be
  generated and then the workflow containing the
  below worklet will stop and not proceed further.

22
Working with Tasks - Decision

• Nothing new under the sun

23
Working with Tasks - Events

• Event-Raise task. Event-Raise task represents a
  user-defined event. When the PowerCenter Server
  runs the Event-Raise task, the Event-Raise task
  triggers the event. Use the Event-Raise task with
  the Event-Wait task to define events.
• Event-Wait task. The Event-Wait task waits for an
  event to occur. Once the event triggers, the
  PowerCenter Server continues executing the rest
  of the workflow.
• Pre-defined event
  User-defined event.

24
Working with Tasks - Events

• Pre-defined event. A pre-defined event is a
  file-watch event. For pre-defined events, use an
  Event-Wait task to instruct the PowerCenter
  Server to wait for the specified indicator file
  to appear before continuing with the rest of the
  workflow. When the PowerCenter Server locates the
  indicator file, it starts the next task in the
  workflow.
• User-defined event. A user-defined event is a
  sequence of tasks in the workflow. Use an
  Event-Raise task to specify the location of the
  user-defined event in the workflow. A
  user-defined event is sequence of tasks in the
  branch from the Start task leading to the
  Event-Raise task.

25
Working with Tasks - Events

• Example Used Defined Event !

26
Questions

• ?

27
Performance Tuning

• Identifying the Performance Bottleneck
• 1gtIdentifying Target Bottlenecks
• 2gtIdentifying Source Bottlenecks
• - Using a Filter Transformation
• - Using a Read Test Session
• - Using a Database Query
• 3gtIdentifying Mapping Bottlenecks
• - High Rowsinlookupcache Counters
• - High Errorrows Counters
• 4gtIdentifying a Session Bottleneck
• Any value other than zero in the readfromdisk and
  writetodisk counters for Aggregator, Joiner, or
  Rank transformations indicate a session
  bottleneck.
• 5gtIdentifying a System Bottleneck

28
Performance Tuning

• Optimizing the Target Database
• Drop indexes and key constraints.
• Increase checkpoint intervals.
• Use bulk loading.
• Use external loading.
• Increase database network packet size.
• Optimize Oracle target databases.

29
Performance Tuning

• Optimizing the Source Database
• Optimize the query.
• Create tempdb as in-memory database.
• Use conditional filters.
• Increase database network packet size.
• Connect to Oracle databases using IPC protocol.

30
Performance Tuning

• Optimizing the Mapping
• - Configure single-pass reading.
• - Optimize datatype conversions.
• - Eliminate transformation errors.
• - Optimize transformations.
• - Reduce the number of transformations.
• - Optimize expressions.
• - Figure out common logic and place in a
  variable flag instead of calculating each time.

31
Performance Tuning

• Optimizing Lookup Transformations
• Caching Lookups
• - Shared cache.
• - Persistent cache
• Reducing the Number of Cached Rows
• Optimizing the Lookup Condition
• Indexing the Lookup Table

32
Performance Tuning

• Optimizing the Session
• Increase the number of partitions.
• Reduce errors tracing.
• Remove staging areas.
• Tune session parameters.

33
Performance Tuning

• Optimizing the System
• Improve network speed.
• Use multiple PowerCenter Servers.
• Use a server grid.
• Improve CPU performance.
• Configure the PowerCenter Server for ASCII data
  movement mode.
• Check hard disks on related machines.
• Reduce paging.
• Use processor binding

34
Questions

• ?

35

• Thank you !!!