7 Replies Latest reply: May 15, 2014 10:00 AM by donne4real _ RSS

    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,

        • scheduling a process to run on completion of a seperate process
          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, /b[/quote]

            • scheduling a process to run on completion of a seperate process
              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.

                • scheduling a process to run on completion of a seperate 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.[/QUOTE]

                    • scheduling a process to run on completion of a seperate 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?[/QUOTE]

                        • scheduling a process to run on completion of a seperate process
                          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

                          /code

                           

                          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

                            • scheduling a process to run on completion of a seperate process
                              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

                              /code

                               

                               

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