Difference between DEFINE_CHAIN_EVENT_STEP and DEFINE_CHAIN_STEP procedure

data-warehouseetljobsoracle

As per oracle help:*

DEFINE_CHAIN_EVENT_STEP Procedure:
This procedure adds or replaces a chain step and associates it with an event schedule or an inline event. Once started in a running chain, this step will not complete until the specified event has occurred. Every step in a chain must be defined before the chain can be enabled and used. Defining a step gives it a name and specifies what happens during the step. If a step already exists with this name, the new step will replace the old one.

DEFINE_CHAIN_STEP Procedure:
This procedure adds or replaces a chain step and associates it with a program or a nested chain. When the chain step is started, the specified program or chain is run. If a step already exists with the name supplied in the chain_name argument, the new step replaces the old one.
The chain owner must have EXECUTE privileges on the program or chain associated with the step. Only one program or chain can run during a step.

Though the help documentation is elaborate, unfortunately I'm failing to understand fully. what is the difference between these two procedures?

Best Answer

First of all a chain is a name referred to a series of programs which are linked together for a specific objective.

So the DBMS_SCHEDULER.DEFINE_CHAIN_STEP procedure define an step to point to a program or a nested chain.

BUT, if you wish to define a step which waits for a event to occur to be executed then you need to use DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP procedure.

The following blog post from Database Journal contains a very good example of using Job Chains.

Figure: Flowchart is taken from database journal blog post by Jim Czuprynski.

For example, according to the above flowchart, to perform employee paycheck file validation steps it file has to be arrived. To define such step you need to user DEFINE_CHAIN_EVENT_STEP procedure.

Go to the referenced link for details.

Reference: Job Chains

Related Solutions

ETL Best Practices – Data Transfer Between OLTP Systems

Use the tools that you are most comfortable with. Your situation seems simple and lasts only for a short time. If you have to keep this up and running for a long time, you might end up by using more advanced tooling, where you define rules for conversions and synchronizations to be performed for you. Doing so is easier to maintain and to document. The learning curve might be a little steep. On the other hand, you could use this case to train yourself. There is no urgent deadline and when it gets more urgent, you probably build the home cooked version within a day.

Sql-server – How to changes or update local server connection in Maintenance plan job

Maintenance plans make use of SSIS packages that are stored in MSDB. These packages use connection strings, which are not changed after a server rename.

Use the script (reproduced below) posted by NancySon in the comments of How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server to get some inspiration on how to change these connection strings. Or recreate the maintenance plans.

Maintenance plans don't get their connections changed to the new server name and so they may break. After a rename you may find that you cannot delete or rename the existing maintenance plans, so either delete them before renaming the server and recreate them afterwards or run the following script to fix them:

   use msdb

   DECLARE @oldservername as varchar(max)
    SET @oldservername='<server name>\<instance name>'

   -- set the new server name to the current server name

   declare @newservername as varchar(max)
    set @newservername=@@servername

   declare @xml as varchar(max)
    declare @packagedata as varbinary(max)
    -- get all the plans that have the old server name in their connection string
    DECLARE PlansToFix Cursor
    FOR
    SELECT    id
    FROM         sysssispackages
    WHERE     (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%')

   OPEN PlansToFix


   declare @planid uniqueidentifier
    fetch next from PlansToFix into @planid

   while (@@fetch_status<>-1)  -- for each plan

   begin
    if (@@fetch_status<>-2)
    begin
    select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) from sysssispackages where id= @planid  -- get the plan's xml converted to an xml string

   declare @planname varchar(max)
    select @planname=[name] from  sysssispackages where id= @planid  -- get the plan name
    print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername  -- print out what change is happening

   set @xml=replace(@xml,'server=''' + @oldservername + '''','server=''' + @newservername +'''')  -- replace the old server name with the new server name in the connection string
    select @packagedata=cast(@xml as varbinary(max))  -- convert the xml back to binary
    UPDATE    sysssispackages SET packagedata = @packagedata WHERE (id= @planid)  -- update the plan

   end
    fetch next from PlansToFix into @planid  -- get the next plan

   end

   close PlansToFix
    deallocate PlansToFix

  ----- This will also handle the packages that have a tag such as 
    ----- <DTS:Property DTS:Name="ConnectionString">Data Source=servername;Integrated Security=SSPI;Connect Timeout=30;</DTS:Property>



   DECLARE @oldservername as varchar(max)
    SET @oldservername='<server name>\<instance name>'-- set the new server name to the current server name
    declare @newservername as varchar(max)
    set @newservername = @@servername
    declare @xml as varchar(max)
    declare @packagedata as varbinary(max)-- get all the plans that have the old server name in their connection string
    DECLARE PlansToFix Cursor FOR 
    SELECT id
    FROM sysssispackages
    WHERE (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%Data Source=' + @oldservername + '%')

   OPEN PlansToFix
    declare @planid uniqueidentifier
    fetch next from PlansToFix into @planid 
    while (@@fetch_status<>-1) -- for each plan 
    begin
    if (@@fetch_status<>-2)
    begin
    select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) 
    from sysssispackages where id= @planid -- get the plan's xml converted to an xml string
    declare @planname varchar(max)select @planname=[name] from sysssispackages where id= @planid -- get the plan name
    print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername -- print out what change is happening
    set @xml=replace(@xml,'Data Source=' + @oldservername,'Data Source=' + @newservername) -- replace the old server name with the new server name in the connection string
    select @packagedata=cast(@xml as varbinary(max)) -- convert the xml back to binary
    UPDATE sysssispackages SET packagedata = @packagedata WHERE (id= @planid) -- update the plan
    end
    fetch next from PlansToFix into @planid -- get the next plan
    end
    close PlansToFix
    deallocate PlansToFix

Best Answer

Related Solutions

ETL Best Practices – Data Transfer Between OLTP Systems

Sql-server – How to changes or update local server connection in Maintenance plan job

Related Question