How to do a task one time for a multi-instance job?

mctny · Post by **mctny** » Wed Mar 05, 2008 8:55 pm

I need to call a few Oracle commands one time for a multi-instance job, I created a simple job which consist of an oracle stage and dummy file, and I put my commands in the oracle stage, now I need to call this job after a job which is multi-instance. there is no sequencer for this job and I should not create one either, I don't have the prior knowledge of the instances/invocation IDs.

I assume if I try to call it in the after job subroutine, it will still call it multiple times.

any suggestions?

kumar_s · Post by **kumar_s** » Wed Mar 05, 2008 9:07 pm

Hi Cetin,

Multiple instance job is very simlar to the other jobs. Just that, you need to pass the Invocation Id during the call. So that the job will have a separate instance running with the extension of invocatin id. ie., JobName.Invocation.ID

So the process of calling the multiple instance job goes as normal as the other jobs with addition of passign Inovcation id as parameter.

kumar_s · Post by **kumar_s** » Wed Mar 05, 2008 9:09 pm

Hi Cetin,

Multiple instance job is very simlar to the other jobs. Just that, you need to pass the Invocation Id during the call. So that the job will have a separate instance running with the extension of invocatin id. ie., JobName.Invocation.ID

So the process of calling the multiple instance job goes as normal as the other jobs with addition of passign Inovcation id as parameter.

mctny · Post by **mctny** » Wed Mar 05, 2008 9:19 pm

Hi Kumar,

thank you very much for the quick reply, I think I wasn't very clear about the case, the thing is that I don't need to call the multi-instance job, I need to call my small job for one time only. i.e., lets say multi-instance job run for 5 different invocation IDs everyday but I still want my small job to be called one time only on everyday. This number, 5, is not a constant number.

thanks again

kumar_s · Post by **kumar_s** » Wed Mar 05, 2008 9:22 pm

If I understand properly, you need to call your new job after the Multiple instance job been executed n time.
How does the Multiinstance job been executed / Called?

kumar_s · Post by **kumar_s** » Wed Mar 05, 2008 9:24 pm

If I understand properly, you need to call your new job after the Multiple instance job been executed n time.
How does the Multiinstance job been executed / Called?

kumar_s · Post by **kumar_s** » Wed Mar 05, 2008 9:24 pm

Sorry, I think there is some problem in my browser

mctny · Post by **mctny** » Wed Mar 05, 2008 9:33 pm

yes correct, it should be before, instead of after , though,

kumar_s wrote: ............ after the Multiple instance job been executed n time.
How does the Multiinstance job been executed / Called?

the multiinstance job is called in a job control datastage basic program, it is a daemon that runs and waits/sleeps almost all day, stops only a few hours, waiting for trigger files etc....

kumar_s · Post by **kumar_s** » Wed Mar 05, 2008 9:54 pm

OK, if the deamon runs periodically or get triggered based up on a file, how will you define Before?
You can schedule this job at 12AM every day. So that the first thing of the day would be this job, and the Multi instance will be executed once Trigger file is been dropped.

chulett · Post by **chulett** » Wed Mar 05, 2008 10:18 pm

mctny wrote:I need to call a few Oracle commands one time for a multi-instance job, I created a simple job which consist of an oracle stage and dummy file, and I put my commands in the oracle stage, now I need to call this job after a job which is multi-instance.

How about you let us know what kind of 'few Oracle commands' you need to run this one time? That knowledge could help craft a more appropriate solution.

mctny · Post by **mctny** » Wed Mar 05, 2008 10:28 pm

sure, they are just refreshing of materialized views in Oracle.

CALL DBMS_SNAPSHOT.REFRESH ('DM_APSDM.MAT_View_1','C');;
CALL DBMS_SNAPSHOT.REFRESH ('DM_APSDM.MAT_View_2','C');;
CALL DBMS_SNAPSHOT.REFRESH ('DM_APSDM.MAT_View_3','C');;
CALL DBMS_SNAPSHOT.REFRESH ('DM_APSDM.MAT_View_4','C');;
COMMIT;;

and some more of the similar fashion

I inserted these into an oracle stage in the after SQL tab.

sorry Kumar, but scheduling my new job seperately is not an option as it is a dependent job. I was thinking of doing some trick inside the multi-instance job so that it will run one time instead of n times everyday, but without using a specific invocation id , it is not very possible.

kumar_s · Post by **kumar_s** » Wed Mar 05, 2008 10:31 pm

So you are refreshing the Materialized view. What stops you from scheduling the Individual jobs?
Schedule this stand alone job at the specific time when the Views need to be refreshed.

mctny · Post by **mctny** » Fri Mar 07, 2008 9:52 am

kumar_s wrote:So you are refreshing the Materialized view. What stops you from scheduling the Individual jobs?
Schedule this stand alone job at the specific time when the Views need to be refreshed.

It would be my supervisor,

and I agree ; Because there is a dependency, and we don't want to run this job if it is not necessary. anyway thanks to you all, I think we will integrate it to another job/sequence which is not a multi-instance.

DSXchange

How to do a task one time for a multi-instance job?

How to do a task one time for a multi-instance job?

Re: How to do a task one time for a multi-instance job?