Challenge
Third-party Enterprise Schedulers are widely used by organizations to manage PowerCenter Data Integration jobs. Maestro, AutoSys, Control-M and Tidal are a few examples of third-party schedulers that can be used for scheduling PowerCenter workflows. An increasing number of Informatica users adopt the PowerCenter Advanced Edition for scalability and reliability. This edition includes High Availability, Server Grid, Session on Grid and Partitioning features. When grid and high availability features are used together they are known as the Enterprise Grid. Configuring the Enterprise Grid requires planning for external components such as third-party schedulers that interact with PowerCenter. The challenge is to ensure that the job scheduling is also highly available and capable of running workflows in different failover scenarios.
Description
First, it is important to explain the typical architecture for a third-party scheduler and how it interacts with the Enterprise Grid option. Third-party schedulers consist of application servers and agents. Agents are the components that run on PowerCenter servers. An agent is a component, installed in the client machine. The agent waits for commands from the Master, executes them, then returns the exit code back to the Master. Agent installations are generally done on the Informatica server to utilize the command line utility installed on the server. Informatica packages and ships the command line utility separately as well.
This best practice document addresses the configuration of the agents for high availability. It does not consider the high availability of application servers.
In an Enterprise Grid implementation that includes two or more servers running PowerCenter software, the Load Balancer dispatches the tasks across the servers. To execute the tasks, workflows need to be started using the pmcmd command line interface. High availability eliminates a single point of failure in a domain and provides minimal service interruption in the event of failure. With a high availability configuration for a domain, the domain can continue to run despite temporary network, hardware or service failures. For PowerCenter Integration Services (PCIS) that run on a grid, an alias is assigned to a group of nodes that run sessions and workflows. When a workflow runs on a grid, scalability and performance are improved by distributing session and command tasks to service processes running on nodes in the grid.
In order for the PowerCenter Advanced Edition option to work seamlessly with third-party schedulers, it is necessary to install the third-party scheduler agent for each node running in the domain. Additionally, an IP load balancer can be configured to provide a virtual IP address for the nodes running in the domain. The application server for the third-party scheduler will use this virtual IP address to connect to the PowerCenter domain. Using a round-robin method, the third-party scheduler will dispatch the jobs to the PowerCenter nodes.
As an example (not a recommendation) F5 BIG-IP setup creates a load balancing pool which routes the incoming requests to the nodes available in the pool. Pool members are logical objects which represents a node in the Informatica Domain. The request sent by the scheduler master will be routed to any of the available nodes, based on the algorithm set by the network administrators.
Note: The third-party scheduler dispatch mechanism will not affect the dispatch mechanism of tasks on the PCIS running on a grid.
Another method of configuring the virtual IP address is to bind it to one of the nodes. The third-party scheduler agent running on this node will be used for executing the workflows. If the server hosting this node is down, the virtual IP address will switch to another node in the domain. Now the third-party scheduler agent running on the second node will be responsible for executing the workflows. When the pmcmd command is invoked on one of the nodes, pmcmd will connect to the domain to run the workflow on the grid PCIS. The PCIS is responsible for running tasks within the workflow. The distribution of the sessions within the workflow is handled by the PCIS load balancer.
Agent environment Configuration
Scheduler agents on an Informatica server require the below environment variables to execute the pmcmd utility:
export INFA_HOME=<INFA_HOME Path>
export INFA_DOMAINS_FILE=$INFA_HOME/domains.infa
export PATH=$INFA_HOME/server/bin:$PATH
export LD_LIBRARY_PATH=$INFA_HOME/server/bin:$LD_LIBRARY_PATH
Note: If Client codepage compatibility is an issue, please reach out to your Informatica admin for Informatica server codepage details and export those variables as well in the scheduler agent user profile.
In command line mode, pmcmd indicates the success or failure of a command with a return code. Return code “0” indicates that the command succeeded. Any other return code indicates that the command failed. If a command is issued in wait mode, a return value of “0” indicates the command ran successfully. If a command is issued in the nowait mode, a return value of “0” indicates that the request was successfully transmitted to the PCIS, and it acknowledged the request.
Therefore two options exist for utilizing the pmcmd command interface with third-party schedulers.
- The wait mode can be used. In this option, the workflow will run and send back a “0” return code to the third-party scheduler when it succeeds.
- The, nowait mode can be used and as soon as the PCIS acknowledges the request, a “0” return code will be sent back to the third-party scheduler.
The pmcmd commands can be placed in a wrapper script that can be invoked by the third-party scheduler agent. The workflow name can be a variable that can be changed for each workflow. The options for the pmcmd command are as follows:
pmcmd startworkflow <<-service|-sv> service [<-domain|-d> domain] [<-timeout|-t> timeout]>
<<-user|-u> username|<-uservar|-uv> userEnvVar> <<-password|-p> password|
<-passwordvar|-pv> passwordEnvVar> [<<-usersecuritydomain|-usd> usersecuritydomain|
<-usersecuritydomainvar|-usdv> userSecuritydomainEnvVar>] [<-folder|-f> folder] [<-startfrom>
taskInstancePath [<-recovery|-norecovery>]] [<-paramfile> paramfile]
[<-localparamfile|-lpf> localparamfile] [<-osprofile|-o> OSUser] [-wait|-nowait] [<-runinsname|-rin> runInsName] workflow
For example:
pmcmd startworkflow -sv "$IS_ASCII” -d "$DOMAINNAME” -uv PM_USER -pv PM_PASSWD -f "$FOLDERNAME" "$WORKFLOWNAME"
All the variables provided in the example will be provided by the master. The script will be generic in nature.
Workflow Recovery
With high availability enabled, PowerCenter recovers a workflow automatically if a service process that is running the workflow fails over to a different node. A workflow can be configured to recover a task automatically when the task terminates. PowerCenter also recovers a session and workflow after a database connection interruption. Workflow recovery allows the workflow and workflow tasks to continue processing from the point of interruption. A workflow can be recovered if the PCIS can access the workflow state of operation. The workflow state of operation includes the status of tasks in the workflow and workflow variable values. The PCIS stores the state in memory or on disk, based on how the workflow was configured. The workflow can be recovered if it terminates stops or aborts. The workflow does not have to be running.
When a workflow is enabled for recovery, the PCIS stores the workflow state of operation in the shared location, $PMStorageDir. Using a cache coherent POSIX compliant shared file system such as, Veritas CFS, it is extremely important for the recovery process. The PCIS can restore the state of operation to recover a stopped, aborted, or terminated workflow. When it performs recovery, it restores the state of operation to recover the workflow from the point of interruption. When the workflow completes, the PCIS removes the workflow state of operation from the shared folder. The workflow state of operation includes the following information:
- Active service requests
- Completed and running task status
- Workflow variable values
Each task in a workflow has a recovery strategy. When the PCIS recovers a workflow, it recovers tasks based on the recovery strategy Restart task. When the PCIS recovers a workflow, it restarts each recoverable task that is configured with a restart strategy. Session and Command tasks can be configured with a restart recovery strategy. All other tasks have a restart recovery strategy by default. With the restart task option, the PCIS runs the session again when it recovers the workflow. When recovering with restart task, the partially loaded data in the target may need to be removed or a mapping to skip the duplicate rows may need to be designed. In summary, if a third-party scheduler is used with pmcmd wait mode, a failed session will be recovered automatically and the workflow will succeed after performing recovery. As a result, the third-party scheduler will receive a return code of zero (“0”) that indicates successful completion of the workflow.
Suspended workflows can be recovered with the following pmcmd syntax:
pmcmd recoverworkflow <<-service|-sv> service [<-domain|-d> domain] [<-timeout|-t> timeout]>
<<-user|-u> username|<-uservar|-uv> userEnvVar> <<-password|-p> password|<-passwordvar|-pv>
passwordEnvVar> [<<-usersecuritydomain|-usd> usersecuritydomain|<-usersecuritydomainvar|-usdv>
userSecuritydomainEnvVar>] [<-folder|-f> folder] [<-paramfile> paramfile] [<-localparamfile|-lpf> localparamfile] [-wait|-nowait]
[<-runinsname|-rin> runInsName] [-wfrunid workflowRunId] workflow
Note: Tidal Software has developed Tidal Enterprise Adapter for Informatica. This is an API-level integration solution that can connect to the PowerCenter repository to schedule, monitor and capture exit codes. Tidal scheduler can collect and consolidate log file data from the workflow and all of its tasks to make it available from a centralized location.
Active Batch and Control M have developed solutions, which can connect to the Informatica IPCIS environment using the Web Service Hub. They do not need a scheduler agent on the Informatica server. These solutions do require a Web Service Hub attached to the IPCIS and PowerCenter Repository Services.
Marketplace Apps
The Informatica Marketplace offers apps, frameworks, tools and utilities that help to make data integration projects and their deliverables easier, faster and more manageable. Search the Informatica Marketplace to find schedulers that can support this Best Practice.