This chapter describes how to run and schedule integration processes.
This chapter includes the following sections:
An execution takes place when an integration task needs to be performed by Oracle Data Integrator. This integration task may be one of the following:
An operation on a model, sub-model or a datastore, such as a customized reverse-engineering, a journalizing operation or a static check started from the Oracle Data Integrator Studio
The execution of a design-time object, such as an interface, a package or a procedure, typically started from the Oracle Data Integrator Studio
The execution of a run-time scenario that was launched from the Oracle Data Integrator Studio, from a command line, via a schedule or a web service interface
Oracle Data Integrator generates the code for an execution in the form of a session. A run-time Agent processes this code and connects to the sources and targets to perform the data integration. These sources and targets are located by the Agent using a given execution context.
When an execution is started from Oracle Data Integrator Studio, the Execution Dialog is displayed. This dialog contains the execution parameters listed in Table 19-1.
Table 19-1 Execution Parameters
Properties | Description |
---|---|
Context |
The context into which the session is started. |
Agent |
The agent which will execute the interface. The object can also be executed using the agent that is built into Oracle Data Integrator Studio, by selecting Local (No Agent). |
Log Level |
Level of logging information to retain. All session tasks with a defined log level lower than or equal to this value will be kept in the Session log when the session completes. However, if the object execution ends abnormally, all tasks will be kept, regardless of this setting. |
Simulation |
Check Simulation if you want to simulate the execution and create an execution report. Refer to Section 19.6, "Simulating an Execution" for more information. |
The lifecycle of a session is as follows:
An execution request is sent to the agent, or the agent triggers an execution from a schedule.
Note that if the execution is triggered from Oracle Data Integrator Studio on a design-time object (interface, package, etc.), Studio pre-generates in the work repository the code for the session before sending the request. If the execution is started from a scenario, this phase is not necessary as the scenario already contains pre-generated code.
The agent completes code generation for the session: It uses the context provided to resolve the physical information such as data server connections and fully qualified tables names. This resulting code is written into the work repository as a session in Waiting status.
The agent initializes the connections to the source and target data servers that are required for the execution of the session.
The agent acknowledges the execution request. If the execution was started from the Studio, the Session Started Dialog is displayed.
The agent executes each of the tasks contained in this session, using the capabilities of the database servers, operating systems, or scripting engines to run the code contained in the session's tasks.
While processing the session, the agent updates the execution log in the repository, reports execution statistics and error messages.
Once the session is started, you can monitor it in the log, using for example Operator Navigator. Refer to Chapter 20, "Monitoring Integration Processes" for more information on session monitoring.
When the session completes, tasks are preserved or removed from the log according to the log level value provided when starting for this session.
Note:
A Session is always identified by a unique Session Number (or Session ID). This number can be viewed when monitoring the session, and is also returned by the command line or web service interfaces when starting a session.When starting an execution from other locations such as a command line or a web service, you provide similar execution parameters, and receive a similar Session Started feedback. If the session is started synchronously from a command line or web service interface, the command line or web service will wait until the session completes, and provide the session return code and an error message, if any.
Interfaces, procedures, and packages are design-time objects that can be executed from the Designer Navigator of Oracle Data Integrator Studio:
For more information on interfaces execution, refer to Section 11.3.8, "Execute the Integration Interface".
For more information on procedures execution, refer to Section 12.1.3, "Using Procedures".
For more information on packages execution, refer to Section 10.5, "Running the Package".
For more information on model operations, refer to Section 5.2, "Creating and Reverse-Engineering a Model", Section 5.6, "Checking Data Quality in a Model" and Section 7.2, "Setting up Journalizing".
Scenarios can be executed in several ways:
From a Web Service. See Section 19.7.2, "Executing a Scenario Using a Web Service" for more information.
Note:
Before running a scenario, you need to have the scenario generated from Designer Navigator or imported from a file. Refer to Chapter 13, "Working with Scenarios" for more information.You can start a scenario from Oracle Data Integrator Studio from Designer or Operator Navigator.
To start a scenario from Oracle Data Integrator Studio:
Select the scenario in the Projects accordion (in Designer Navigator) or the Scenarios accordion (in Operator Navigator).
Right-click, then select Execute.
In the Execution dialog, set the execution parameters. Refer to Table 19-1 for more information. To execute the scenario with the agent that is built into Oracle Data Integrator Studio, select Local (No Agent).
Click OK.
If the scenario uses variables as parameters, the Variable values dialog is displayed. Select the values for the session variables. Selecting Latest value for a variable uses its current value, or default value if none is available.
When the agent has started to process the session, the Session Started dialog appears.
You can start a scenario from a command line.
Before executing a scenario from a command line, read carefully the following important notes:
The command line scripts, which are required for performing the tasks described in this section, are only available if you have installed the Oracle Data Integrator Standalone Agent. See the Oracle Fusion Middleware Installation Guide for Oracle Data Integrator for information about how to install the Standalone Agent.
To use this command the connection to your repository must be configured in the odiparams file. See Chapter 4, "Managing Agents" for more information.
When starting a scenario from a command line, the session is not started by default against a remote run-time agent, but is executed by a local Java process started from the command line. This process can be aborted locally, but cannot receive a session stop signal as it is not a real run-time agent. As a consequence, sessions started this way cannot be stopped remotely.
This process will be identified in the Data Integrator log after the Local Agent
name. You can change this name using the NAME
parameter.
If you want to start the session against a run-time agent, you must use the AGENT_URL
parameter.
To start a scenario from a command line:
Change directory to the /agent/bin
directory of the Oracle Data Integrator installation.
Enter the following command to start a scenario.
On UNIX systems:
./startscen.sh <scenario_name> <scenario_version> <context_code> [<log_level>] [-AGENT_URL=<remote_agent_url>] [-NAME=<local_agent_name>] [-SESSION_NAME=<session_name>] [-KEYWORDS=<keywords>] [<variable>=<value>]*
On Windows systems:
startscen.bat <scenario_name> <scenario_version> <context_code> [<log_level>] [-AGENT_URL=<remote_agent_url>] ["-NAME=<local_agent_name>"] ["-SESSION_NAME=<session_name>"] ["-KEYWORDS=<keywords>"] ["<variable>=<value>"]*
Note:
On Windows platforms, it is necessary to "delimit" the command arguments containing "=" signs or spaces, by using double quotes. The command call may differ from the Unix command call. For example:On Unix
./startscen.sh DWH 001 GLOBAL SESSION_NAME=MICHIGAN
On Windows
startscen.bat DWH 001 GLOBAL "SESSION_NAME=MICHIGAN"
Table 19-2 lists the different parameters, both mandatory and optional. The parameters are preceded by the "-" character and the possible values are preceded by the "=" character. You must follow the character protection syntax specific to the operating system on which you enter the command.
Table 19-2 Startscen command Parameters
Parameters | Description |
---|---|
|
Name of the scenario (mandatory). |
|
Version of the scenario (mandatory). If the version specified is -1, the latest version of the scenario is executed. |
|
Code of the execution context (mandatory). |
|
Level of logging information to retain. This parameter is in the format Example: |
|
URL of the run-time agent that will run this session. If this parameter is set, then |
|
Agent name that will appear in the execution log for this session, instead of Note that Using an existing physical agent name in the
It is recommended to use a If you want to start a session on a given physical agent, you must use the |
|
Name of the session that will appear in the execution log. |
|
List of keywords attached to this session. These keywords make session identification easier. The list is a comma-separated list of keywords. |
|
Allows one to assign a This parameter can be repeated to assign several variables. Do not use a hash sign (#) to prefix the variable name on the startscen command line. |
Any session that has encountered an error, or has been stopped by the user can be restarted.
Session can be restarted in several ways:
From a Web Service. See Section 19.7.4, "Restarting a Session Using a Web Service" for more information.
Only sessions in status Error or Waiting can be restarted. The session will resume from the last non-completed task (typically, the one in error).
WARNING:
When restarting a session, all connections and transactions to the source and target systems are re-created, and not recovered from the previous session run. As a consequence, uncommitted operations on transactions from the previous run are not applied, and data required for successfully continuing the session may not be present.
To restart a session from Operator Navigator:
In Operator Navigator, select the session that you want to restart.
Right-click and select Restart.
In the Choose Restart Mode dialog, select the agent you want to use for running the new session.
Click OK.
When Oracle Data Integrator has restarted the session, the Session Started dialog appears.
Note:
The command line scripts, which are required for performing the tasks described in this section, are only available if you have installed the Oracle Data Integrator Standalone Agent. See the Oracle Fusion Middleware Installation Guide for Oracle Data Integrator for information about how to install the Standalone Agent.To restart a session from a command line:
Change directory to the /agent/bin
directory of the Oracle Data Integrator installation.
Enter the following command to start a scenario.
On UNIX systems:
./restartsession.sh <session_number> [-AGENT_URL=<remote_agent_url>]
On Windows systems:
restartsession.bat <session_number> ["-AGENT_URL=<remote_agent_url>"]
Table 19-3 lists the different parameters of this command, both mandatory and optional. The parameters are preceded by the "-" character and the possible values are preceded by the "=" character. You must follow the character protection syntax specific to the operating system on which you enter the command.
Table 19-3 restartsess command Parameters
Parameters | Description |
---|---|
|
Number (ID) of the session to be restarted. |
|
URL of the run-time agent that will run this session. By default the session is executed by a local Java process started from the command line. |
Note:
To use this command the connection to your repository must be configured in the odiparams file. See Section 4.3, "Managing Agents" for more information.Note:
When restarting a session from a command line, the session is not started by default against a remote run-time agent, but is executed by a local Java process started from the command line.If you want to start the session against a run-time agent, you must use the AGENT_URL
parameter.
You can schedule the executions of your scenarios using the Oracle Data Integrator built-in scheduler or an external scheduler. Both methods are detailed in this section:
Section 19.5.1, "Scheduling a Scenario with the Built-in Scheduler"
Section 19.5.2, "Scheduling a Scenario with an External Scheduler"
You can attached schedules to scenarios Oracle Data Integrator. Such schedules are managed by the scheduler built-in run-time agent.
It is important to understand that a schedule concerns only one scenario, while a scenario can have several schedules and can be scheduled in several ways. The different schedules appear under the Scheduling node of the scenario. Each schedule allows a start date and a repetition cycle to be specified.
For example
Schedule 1: Every Thursday at 9 PM, once only
Schedule 2: Every day from 8 am to 12 noon, repeated every 5 seconds.
Schedule 3: Every day from 2 PM to 6 PM, repeated every 5 seconds, with a maximum cycle duration of 5 hours.
To schedule a scenario from Oracle Data Integrator Studio.
Right-click the Scheduling node under a scenario in the Designer or Operator Navigator.
Select New Scheduling. The Scheduling editor.
On the Definition tab of the Scheduling editor specify the parameters as follows:
Properties | Description |
---|---|
Context | Context into which the scenario is started. |
Agent | Agent executing the scenario. |
Log Level | Level of logging information to retain. |
The Status parameters define the activation of the schedule.
Properties | Description |
---|---|
Active | The scheduling will be active when the agent is restarted or when the scheduling of the physical agent is updated. |
Inactive | The schedule is not active and will not run. |
Active for the period | Activity range of the schedule. A schedule active for a period of time will only run within this given period. |
The Execution parameters define the frequency of execution for each execution cycle.
Properties | Description |
---|---|
Execution | Frequency of execution option (annual, monthly,... simple). This option is completed by a set of options that depend on this main option. |
On the Execution Cycle tab, specify the parameters for the repeat mode of the scenario as follows:
Properties | Description |
---|---|
None (Execute once) | The scenario is executed only one time. |
Many times | The scenario is repeated several times.
|
Constraints | Allows limitations to be placed on one cycle iteration, in the event of a problem during execution.
|
On the Variables tab, unselect Latest Value for variables for which you want to provide a Value. Only variables used in the scenario and flagged as parameters for this scenario appear in this tab.
From the File menu, click Save.
The new schedule appears under the Scheduling node of the scenario.
The schedule changes are taken into account by the run-time agent when it starts or when it receives a schedule update request.
An agent reads schedules when starting on all the repositories attached to the master repository it connects to. It is possible, if a schedule was added for this agent in a given repository, to refresh the agent schedule.
To update an agent's schedule:
In Topology Navigator expand the Agents node in the Physical Architecture accordion.
Select the Physical Agent you want to update the schedule.
Right-click and select Update Scheduling...
In the Select Repositories dialog, select the repositories from which you want to read scheduling information. Check Select All Work Repositories to read scheduling information from all these repositories.
Click OK.
The agent refreshes and re-computes its in-memory schedule from the schedules defined in these repositories.
You can view the scheduled tasks of all your agents or you can view the scheduled tasks of one particular agent.
Note:
The Scheduling Information is retrieved from the Agent's in-memory schedule. The Agent must be started and its schedule refreshed in order to display accurate schedule information.Displaying the Schedule for All Agent
To display the schedule for all agents:
Select Connect Navigator >Scheduling... from the Operator Navigator toolbar menu.
The View Schedule dialog appears, displaying the schedule for all agents.
Displaying the Schedule for One Agent
To display the schedule for one agent:
In Topology Navigator expand the Agents node in the Physical Architecture accordion.
Select the Physical Agent you want to update the schedule.
Right-click and select View Schedule.
The Schedule Editor appears, displaying the schedule for this agent.
Note:
The Scheduling Information is retrieved from the Agent's schedule. The Agent must be started and its schedule refreshed in order to display accurate schedule information.Using the View Schedule Dialog
The schedule is displayed in form of a Gantt diagram. Table 19-4 lists the details of the Schedule dialog.
Selected Agent |
Agent for which the Schedule is displayed. You can display also the schedule of all agents by selecting All Agents. |
---|---|
Selected Work Repository |
Only the scenarios executed in the selected Work Repository are displayed in the schedule. Default is All Work Repositories. |
Scheduling from... to... |
Time range for which the scheduling is displayed. Click Refresh to refresh this schedule. |
Update |
Click Update to update the schedule for the selected agent(s) |
Time Range |
The time range specified (1 hour, 2 hours) allows you to center the diagram on the current time plus this duration. This feature provides a vision of the sessions in progress plus the incoming sessions. You can use the arrows to move the range forward or backward. |
Scenarios details |
This panel displays the details and execution statistics for each scheduled scenario. |
If you select a zone in the diagram (keep the mouse button pressed), you automatically zoom on the select zone.
By right-clicking in the diagram, you open a context menu for zooming, saving the diagram as an image file, printing or editing the display properties.
To start a scenario with an external scheduler, you can either use the command line command startscen from the external scheduler or use the web service interface for triggering the scenario execution. See Section 19.3.2, "Executing a Scenario from a Command Line" and Section 19.7.2, "Executing a Scenario Using a Web Service" for more information.
If a scenario completes successfully, the return code will be 0. If not, the return code will be different than 0. This code will be available in:
The return code of the command line call. The error message, if any, is available on the standard error output.
The SOAP response of the web service call. The web service response includes also the session error message, if any.
In Oracle Data Integrator you have the possibility at design-time to simulate an execution. Simulating an execution generates and displays the code corresponding to the execution without running this code. Execution simulation provides reports suitable for code review.
Note:
No session is created in the log when the execution is started in simulation mode.To simulate an execution:
In the Project view of the Designer Navigator, select the object you want to execute.
Right-click and select Execute.
In the Execution dialog, set the execution parameters and select Simulation. See Table 19-1 for more information.
Click OK.
The Simulation report is displayed.
You can click Save to save the report as.xml
or.html
file.
This section explains how to use a web service to perform run-time operations. it contains the following sections.
Section 19.7.3, "Monitoring a Session Status Using a Web Service"
Section 19.7.7, "Accessing the Web Service from a Command Line"
Oracle Data Integrator includes web services for performing run-time operations. These web services are located in two places:
In the run-time agent, a web service allows starting a scenario, monitoring a session status and restarting a session. To use operations from this web service, you must first install and configure a standalone or a Java EE agent.
A dedicated public web service component provides operations to list the contexts and scenarios available. To use operations from this web service, you must first install and configure this component in a Java EE container.
The following applies to the SOAP request used against the agent and public web services
The web services operations accept password in a plain text in the SOAP request. Consequently, it is strongly recommended to use secured protocols (HTTPS) to invoke web services over a non-secured network.
Repository connection information is not necessary in the SOAP request as the agent or public web service component is configured to connect to a master repository. Only an ODI user and the name of a work repository are required to run most of the operations.
The invokeStartScen
operation of the agent web service starts a scenario in synchronous or asynchronous mode; in a given work repository. The session is executed by the agent providing the web service.
<OdiStartScenRequest> <Credentials> <OdiUser>odi_user</OdiUser> <OdiPassword>odi_password</OdiPassword> <WorkRepository>work_repository</WorkRepository> </Credentials> <Request> <ScenarioName>scenario_name</ScenarioName> <ScenarioVersion>scenario_version</ScenarioVersion> <Context>context</Context> <LogLevel>log_level</LogLevel> <Synchronous>synchronous</Synchronous> <SessionName>session_name</SessionName> <Keywords>session_name</Keywords> <Variables> <Name>variable_name</name> <Value>variable_value</Value> </Variables> </Request> </OdiStartScenRequest>
The scenario execution returns response that depends on the value of the synchronous
element in the request.
In synchronous mode (Synchronous
=1), the response is returned once the session has completed, and reflects the execution result.
In asynchronous mode (Synchronous
=0), the response is returned once the session is started, and only indicates the fact whether the session was correctly started or not.
The getSessionStatus
operation of the agent web service returns the status of one or more sessions in a given repository, identified by their Session Numbers provided in the SessionIds
element. It manages both running and completed sessions.
<OdiGetSessionsStatusRequest> <Credentials> <OdiUser>odi_user</OdiUser> <OdiPassword>odi_password</OdiPassword> <WorkRepository>work_repository</WorkRepository> </Credentials> <SessionIds>session_number</SessionIds> </OdiGetSessionsStatusRequest>
The invokeRestartSess
operation of the agent web service restarts a session identified by its session number (provided in the SessionID
element) in a given work repository. The session is executed by the agent providing the web service.
Note that you can change the value of the variables or use the KeepVariables
boolean element to reuse variables values from the previous session run.
<invokeRestartSessRequest> <Credentials> <OdiUser>odi_user</OdiUser> <OdiPassword>odi_password</OdiPassword> <WorkRepository>work_repository</WorkRepository> </Credentials> <Request> <SessionID>session_number</SessionID> <Synchronous>synchronous</Synchronous> <KeepVariables>0|1</KeepVariables> <Variables> <Name>variable_name</name> <Value>variable_value</Value> </Variables> </Request> </invokeRestartSessRequest>
This operation returns a response similar to InvokeStartScen
, depending on the Synchronous
element's value.
The listContext
operation of the public web service lists of all the contexts present in a master repository.
<listContextRequest> <OdiUser>odi_user</OdiUser> <OdiPassword>odi_password</OdiPassword> <listContextRequest>
The listScenario
operation of the public web service lists of all the contexts present in a given work repository.
<listScenarioRequest> <OdiUser>odi_user</OdiUser> <OdiPassword>odi_password</OdiPassword> <WorkRepository>work_repository</WorkRepository> <listScenarioRequest>
Oracle Data Integrator contains two shell scripts for UNIX platforms that use the web service interface for starting and monitoring scenarios from a command line via the run-time agent web service operations:
startscenremote.sh
starts a scenario on a remote agent on its web service. This scenario can be started synchronously or asynchronously. When started asynchronously, it is possible to have the script polling regularly for the session status until the session completes or a timeout is reached.
getsessionstatusremote.sh
gets the status of a session via the web service interface. This second script is used in the startscenremote.sh
script.
Before accessing a web service from a command line, read carefully the following important notes:
The command line scripts, which are required for performing the tasks described in this section, are only available if you have installed the Oracle Data Integrator Standalone Agent. See the Oracle Fusion Middleware Installation Guide for Oracle Data Integrator for information about how to install the Standalone Agent.
Unlike the startscen.sh
command line, these scripts rely on the lightweight WGET utility installed with the UNIX or Linux platform to perform the web service calls. It does not use any java code and uses a polling mechanism to reduce the number of running processes on the machine. These scripts are suitable when a large number of scenarios and sessions need to be managed simultaneously from a command line.
To start a scenario from a command line via the web service:
Change directory to the /agent/bin
directory of the Oracle Data Integrator installation.
Enter the following command to start a scenario.
On UNIX systems:
./startscenremote.sh <scenario_name> <scenario_version> <context_code> <work_repository> <remote_agent_url> <odi_user> <odi_password> -l <log_level> -s <sync_mode> -t <timeout> -i <interval> -h <http_timeout> -v
Table 19-5 lists the different parameters of this command, both mandatory and optional.
Table 19-5 Startscenremote command Parameters
Parameters | Description |
---|---|
|
Name of the scenario (mandatory). |
|
Version of the scenario (mandatory). If the version specified is -1, the latest version of the scenario is executed. |
|
Code of the execution context (mandatory). |
-l |
Level of logging information to retain. This parameter is in the format Example: |
|
Name of the work repository containing the scenario. |
|
URL of the run-time agent that will run this session. |
|
Name of the user used to run this sessions. |
|
This user's password. |
|
Execution mode:
|
|
Timeout in seconds for waiting for session to complete if sync_mode = 2. |
|
Polling interval for session status if sync_mode = 2. |
|
HTTP timeout for the web services calls. |
|
Verbose mode. |
To monitor the status of a session from a command line via the web service:
Change directory to the /agent/bin
directory of the Oracle Data Integrator installation.
Enter the following command to start a scenario.
On UNIX systems:
./getsessionstatusremote.sh <session_number> <work_repository> <remote_agent_url> <odi_user> <odi_password> -w <wait_mode> -t <timeout> -i <interval> -h <http_timeout> -v
Table 19-5 lists the different parameters of this command, both mandatory and optional.
Table 19-6 Startscenremote command Parameters
Parameters | Description |
---|---|
|
Number of the session to monitor. |
|
Name of the work repository containing the scenario. |
|
URL of the run-time agent that will run this session. |
|
Name of the user used to run this sessions. |
|
This user's password. |
|
Wait mode:
|
|
Timeout in seconds for waiting for session to complete if sync_mode = 2. |
|
Polling interval for session status if sync_mode = 2. |
|
HTTP timeout for the web services calls. |
|
Verbose mode. |