1 Reply Latest reply: May 15, 2014 10:00 AM by Bill Watson RSS

    Datapump 8.5 Duplicating Processes

    Bill Watson

      Hello

       

      Is there a simple method of duplicating an existing Datapump Process?

       

      We are still in the process of transferring some of our Datapump 5 processes into 8.5 and as we have multiple similar processes set up it would be much simpler to set one up and then duplicate and modify it to create the other processes. This functionality would also make it much easier for us to test variations of existing processes without affecting the "live" process.

       

      I have had a look at the SQL Server end of Datapump and it would seem simple to create a new entry in the PumpProcesses table, however I haven't an indepth knowledge on how the whole thing hangs together and don't want to break anything.

       

      Preferably a new command in the Datapump console would be the preferred solution? Is this possible or will we have to wait for DP9?

       

      Edit:

       

      I have used the following SQL to successfully duplicate a process, is there anyway to tie this in to the console?

       

      [font="courier"]set ANSI_NULLS ON

      set QUOTED_IDENTIFIER OFF

      GO

      INSERT INTO PumpProcesses

                            (ProcessName, Description, ProcessDocument, Engine, Priority, AllowParallelJobs)

      SELECT     PumpProcesses_1.ProcessName+' Duplicate' AS ProcessName, Description, ProcessDocument, Engine, Priority, AllowParallelJobs

      FROM         PumpProcesses AS PumpProcesses_1

      WHERE ProcessID = 1  /font[/quote]

       

      [size="1"][ September 05, 2007, 08:16 AM: Message edited by: Bill Watson ][/size]

        • Datapump 8.5 Duplicating Processes
          Bill Watson

          As I haven't received a reply I presume we are the only install of Datapump which has this requirement.

           

          I have created the below SQL which when run using SQL Server Management Studio Express will insert a new stored procedure "SP_DUPLICATEPROCESS" into the DWCHServer database.

           

          [font="courier"]set ANSI_NULLS ON

          set QUOTED_IDENTIFIER OFF

          GO

           

           

          PRINT 'Using DWCH Server Database'

          USE DWCHServer

          GO

           

          PRINT 'Checking for the existence of this procedure'

          IF (SELECT OBJECT_ID('SP_DUPLICATEPROCESS','P')) IS NOT NULL --means, the procedure already exists

               BEGIN

                    PRINT 'Procedure already exists. So, dropping it'

                    DROP PROC SP_DUPLICATEPROCESS

               END

          GO

           

          CREATE PROCEDURE dbo.SP_DUPLICATEPROCESS

          @PROCESSIDENTIFIER INT,

          @PROCESSCOPIES INT

           

          AS

          BEGIN

               DECLARE @counter INT 

           

               -- Loop through @copies number of times appending @counter to the Process Name

               SET @counter = 0

               WHILE @counter < @PROCESSCOPIES  

               BEGIN

                    INSERT INTO .[dbo].[PumpProcesses] (ProcessName, Description, ProcessDocument, Engine, Priority, AllowParallelJobs)

                    SELECT     PumpProcesses_1.ProcessName' Duplicate' CAST(@counter AS varchar(3)) AS ProcessName, Description, ProcessDocument, Engine, Priority, AllowParallelJobs

                    FROM         .[dbo].[PumpProcesses] AS PumpProcesses_1

                    WHERE ProcessID = @PROCESSIDENTIFIER

           

                    SET @counter = @counter + 1

               END

          END

            /font[/quote]You can execute this stored procedure as follows:

           

          Right Click on the PumpProcesses table in the Tables Section and select Open Table

               Make a note of the ProcessID of the Process you wish to duplicate

           

               Open a New Query Window and enter the following command:

           

                    EXEC sp_DUPLICATEPROCESS  ProcessID, NoCopies[/i]

           

               Where  ProcessID[/i]  is the number noted above, and  NoCopies[/i]  is the number of duplicate entries required.

           

          This will create the required number of copies of the selected process adding Duplicate1, Duplicate2 etc to the Process Name. These can then be edited as normal in Datapump Administrator.