scheduling a process to run on completion of a seperate process

[akaras]

Is there a feature in datapump that allows you to schedule a process to run once a seperate process has completed.
For example, I have an excel file that recieves data from 2 seperate processes, and then makes calculations based on this data. Process 2 then distributes the excel file. It cannot run until Process 1 has completed or the distributed data will be wrong.

Thanks,

[Gareth Horton]

Akaras -

You have 2 potential options.

Restructure your process, so that you split your processing into multiple projects, then assign those projects to a single process in the correct order.

You should then use the "multiple projects per job" setting in the Process Properties. This will ensure that the whole procedure will take place in a single job, so that no timing inconsistencies occur.

Alternatively, if the is not possible for some reason, you could use a post-export script to call PumpCMD.exe with the relevant command line to start the named process once the exporting from the first process has completed.

If you go to the Program FilesMonarch Data Pump folder, drop to a command line and enter "Pumpcmd /?", the command line arguments will be shown.

I recommend the first approach, if possible.

Gareth

Originally posted by akaras:
Is there a feature in datapump that allows you to schedule a process to run once a seperate process has completed.
For example, I have an excel file that recieves data from 2 seperate processes, and then makes calculations based on this data. Process 2 then distributes the excel file. It cannot run until Process 1 has completed or the distributed data will be wrong.

Thanks,

[Bill Watson]

Gareth

Does this reply still stand or is there a way in Datapump 8.5 to have one process call others on it's completion perhaps by referencing the PumpApi.dll assembly directly in the post process script?

The reason I ask is that we have a procedure which for flexibility and clarity has been built as 9 seperate Datapump processes (5 data Loads/4 Data Extractions) interacting with an Oracle DB. It has been built as seperate processes so that if any individual step caused a problem it's import/export can be rerun without the others having to be run also (it takes about 2 hours to run the whole thing). It would however be nice to have a process we can fire and forget that could be set off to run the whole thing at once (ie overnight) without having to mess about with individual process schedules or have to maintain the seperate processes and a single combined process.

[Bill Watson]

Can anyone give me a clue if I am onto a loser with this idea or give me a starter for ten in how to set it up?

Gareth

Does this reply still stand or is there a way in Datapump 8.5 to have one process call others on it's completion perhaps by referencing the PumpApi.dll assembly directly in the post process script?

The reason I ask is that we have a procedure which for flexibility and clarity has been built as 9 seperate Datapump processes (5 data Loads/4 Data Extractions) interacting with an Oracle DB. It has been built as seperate processes so that if any individual step caused a problem it's import/export can be rerun without the others having to be run also (it takes about 2 hours to run the whole thing). It would however be nice to have a process we can fire and forget that could be set off to run the whole thing at once (ie overnight) without having to mess about with individual process schedules or have to maintain the seperate processes and a single combined process.

[Gareth Horton]

Bill,

You can try my option 2 method, but if you have 8.5, you could use a Post-Process script, instead of a post-export script, which would be cleaner.

Gareth

Can anyone give me a clue if I am onto a loser with this idea or give me a starter for ten in how to set it up?

[Bill Watson]

Gareth

A couple of questions:

1 - PumpCmd Method

Is there any way of checking the status of a process initiated with PumpCmd? If I use this method to initial the dataload to oracle, how can I confirm it has completed before i run the seperate data extract process?

2 - PumpAPI method

Alternatively I went ahead and did some experiments using the PumpAPI.DLL with some success as follows:

Assembly Details:
Name: DwchServer.PumpAPI Assembly: DwchServer.PumpAPI.DLL

Imports:
DwchServer.PumpAPI

PostProcess Script:

Dim PumpAPI As DwchServer.PumpAPI
Dim strTrackingID As String
Dim strProcess(1) as string
Dim strCurrent as string
PumpAPI = New DwchServer.PumpAPI

strProcess(0) = "Dummy1"
strProcess(1) = "Dummy2"

for each strCurrent in strProcess
Try
strTrackingID = PumpAPI.StartProcess(strCurrent)
Catch ex As Exception
Log.AddEvent("Process: " + strCurrent + " Status: Failed to start - " + ex.Message)
end try
next strcurrent


This successfully initiates the two processes by looping, however again they seem to run concurrently. Is there anyway to delay the execution of the loop until the first process has been completed. I have attempted to use GetStatus() to check for a return value other than 1 in a Do.. Loop Until but can't seem to get it to work.

Any pointers would be appreciated

Bill

[Bill Watson]

Firstly many thanks Gareth for pointing me in the right direction regarding the pumpapi start process asynchronous nature. I was able to work around it. Not perfect yet but seems to be doing the job so far.

For anyone who is interested:

Assembly Details:
Name: DwchServer.PumpAPI Assembly: DwchServer.PumpAPI.DLL

Imports:
DwchServer.PumpAPI

PostProcess Script:

Dim PumpAPI As DwchServer.PumpAPI
Dim strTrackingID As String
Dim strProcess(1) as string 'change number to highest entry value in process names list below
Dim intRep as integer
Dim strCurrent as string
dim blStatus as boolean
PumpAPI = New DwchServer.PumpAPI

'define process names here for array
strProcess(0) = "Dummy1"
strProcess(1) = "Dummy2"

blStatus = true 'default process success to true
'loop through process name array and run tasks in order
for each strCurrent in strProcess
if blstatus = true then 'only run next process if first process or all previous processes succesfull
strtrackingid = "" 'reset tracking id
intRep = 0 'reset status code
Try
strTrackingID = PumpAPI.StartProcess(strCurrent)
Log.AddEvent("Process: " + strCurrent + " Status: Execution Begun")
Log.AddEvent("Process: " + strCurrent + " ID: " + strTrackingID)

Catch ex As Exception
Log.AddEvent("Process: " + strCurrent + " Status: Failed " + ex.Message)
blstatus = false

end try
if blstatus = true then
do
System.Windows.Forms.Application.DoEvents()
intRep = pumpapi.getstatus(strTrackingID)
loop until intRep > 0
if intrep > 0 then
do
System.Windows.Forms.Application.DoEvents()
intRep = pumpapi.getstatus(strTrackingID)
loop until intRep <> 1

select case intRep
case 2
Log.AddEvent("Process: " + strCurrent + " Status: Completed OK")
case 3
Log.AddEvent("Process: " + strCurrent + " Status: Completed With Errors")
blstatus = false
case else
Log.AddEvent("Process: " + strCurrent + " Status: Unknown Value ("+intrep.tostring()+")")
blstatus = false
end select
end if
end if
'if false stop running further processes
if blstatus = false then
return false
end if
end if
next strcurrent



Hope this is useful to other members here even as a further insight into the flexibility of Datapump.