How to Use USER_DEFINED Activity in OWB Process Flow (part II)

We have discussed how to use USER_DEFINED activity in OWB Process Flow in the past. In this topic, we will talk about another option for this property - “SCHEDULER”. With this setting, we can run the external job as a configured OS user.

Enable execution of User Defined activity

Presume we are using the UNIX platform for this simple demo. First, we need to modify the Runtime.properties as below. SCHEDULER uses a DBMS Scheduler external job submitted by the Control Center repository owner which is executed by the default operating system user configured by the DBA

property.RuntimePlatform.0.NativeExecution.Shell.security_constraint= SCHEDULER

To make this change take effect, we need to restart the Control Center Service. Then we can take advantage of the OS user configure settings in <ORACLE_HOME>/rdbms/admin/externaljob.ora (which is configured to run the jobs as a dedicated batch user).

By default, a low privileged user & group (nobody) is defined in this file, you need to change this to the expected OS user for the execution (In my environment, I have set it to owb user). Note: externaljob.ora is owned by root, so you need to root privilege to make the changes.

clip_image002

In OWB design center, let's still use the simple process flow, which contains a Start activity, a User Defined activity and an End Success activity.

clip_image004

Leave all parameters of activity USER_DEFINED unchanged except that we enter /tmp/test.sh into the Value column of the COMMAND parameter.

clip_image006

Then let's create the shell script test.sh in /tmp directory. Here is the content of /tmp/test.sh (this article is demonstrating a scenario in Linux system, and /tmp/test.sh is a Bash shell script):

#!/bin/sh

echo `id` > /tmp/test.txt

Note: don't forget to grant the execution privilege on /tmp/test.sh to your dedicated OS user. For simplicity, we just use the following command.

chmod a+x /tmp/test.sh

After the change is ready, let’s deploy and execute the Process Flow. We will see that the Process Flow completes successfully.

clip_image008

The execution of /tmp/test.sh successfully generated a file /tmp/test.txt, which contains the owb user information as below.

clip_image010

 

Platform differences

UNIX platform and ORACLE 11.2 are used in above example; there are different settings with different environments though. With UNIX platform, if using release 10.2.0.2 or prior releases (where there is no rdbms/admin/externaljob.ora) we need to change the owner of the extjob executable (who should be nobody by default); With Windows platform, instead of modify externaljob.ora, we need to change the user that the external jobs Windows service runs as ("OracleJobSchedulerSID" service).

 

Diagnostic information

OWB uses DBMS_SCHEDULER (create_job, enable etc) package to execute external processes, so when we have issues with the execution, we can try executing using the package manually to narrow down the issue. An example is as below:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
    job_name             => 'TESTJOB',
    job_type             => 'EXECUTABLE',
    job_action  => '/tmp/test.sh');
    DBMS_SCHEDULER.ENABLE('TESTJOB');
    END;
    /

This block should be run under the context of the Control Center Service Owner, In OWB 11.2 release, the owner should be OWBSYS. The view “all_scheduler_job_run_details” can be queried for more details with the job run information.

 

We can also pass the parameters to the command or leverage the return value with “SCHEDULER”, they are the same as discussed in previous post, and will not be discussed here.

Comments:

Hi,
I want to run a ctl which I run with command sqlldr to run in owb .What should I do?Is it possible to guide?

Regards

Posted by guest on November 26, 2011 at 08:09 PM PST #

Hi Sahar

An alternative is to ue the approach below....
http://blogs.oracle.com/warehousebuilder/entry/owb_invoking_sql_loader_from

Cheers
David

Posted by David on November 28, 2011 at 02:52 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today