Data Replication 2
Results 1 to 3 of 3

Thread: Worked out HeartBeat / HealthCheck / Ticker example. Oracle Source, SQLserver target

  1. #1
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    163
    Rep Power
    12

    Worked out HeartBeat / HealthCheck / Ticker example. Oracle Source, SQLserver target

    A HeartBeat Table is useful in any CDC / Replicate applications to have an application level indication of the Replication activity and end-to-end latency.

    This write-up give one example of creating and activating a heartbeat table with the source being an oracle database, Attunity Replicate the CDC tool, and SQLserver the target. For every step in this process (source, replicate, target) there is a timestamp column available which is filled automatically using a system date/time as timestamp.


    There are many NAMING choices made : TableNames, ColumnNames, JobNames, UserNames
    The example used in this article are expected to have to be adjusted to the standards expected in the implementation environment.
    Full Privileges are assumed throughout.

    “Tim Toady” - There's more than one way to do it (TMTOWTDI)
    But here is one way, to get you going!


    The biggest technical choices are on the source side
    • Use a repeating UPDATE to a single target row, or use INSERT of new rows.
    • Use a scheduled JOB, or a loop in a procedure with delay [exec dbms_lock.sleep(60)]


    For frequent checks (rapid heartbea) like once-a-second, an update to a fixed row in a looping procedure is probably best.

    The example below implements slow but stead check, with a once-a-minute insert through a scheduled job (1440 rows per-day per-task).


    The data columns envisioned in the Target table are:
    [Id] [bigint] - Sequence number from Source (Oracle Sequence, SQLServer Identity)
    [dtSource] - Change Time on Source
    [dtReplicate] - Timestamp when Replicate processed the change
    [dtTarget] - Timestamp when Replicated was inserted in target.
    [text_Source] - Optional text to help pass source information
    [text_Replicate] - Optional text added by Replicate server, typically a task name.
    [text_Target] - Optional text field to highlight a specific record on the target


    The Target table is expected to gather heartbeats from multiple sources (tasks).
    As such the ID field alone will not be Unique.
    Add text_Attunity, or text_Create as key



    Here we go... Let's set up the Oracle Side.

    -- Table --

    Code:
    BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE AttunityPOC.HeartBeat';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    /
    CREATE TABLE AttunityPOC.HeartBeat (Id NUMBER, 
          text_Source VARCHAR(30), dtSource TIMESTAMP DEFAULT SYSDATE);
    CREATE UNIQUE INDEX pk_HeartBeat on AttunityPOC.HeartBeat (id);
    -- Oracle workaround for SQLserver IDENTITY column --

    Code:
    CREATE SEQUENCE AttunityPOC.HeartBeat_Sequence;
     
    CREATE OR REPLACE TRIGGER AttunityPOC.HeartBeat_Trigger
    BEFORE INSERT ON AttunityPOC.HeartBeat
    FOR EACH ROW
    BEGIN
      SELECT HeartBeat_Sequence.NEXTVAL INTO :NEW.Id FROM DUAL;
    END;
    /
    -- Let's stick the actual change into a PROCEDURE even through trivial

    Code:
    CREATE OR REPLACE PROCEDURE AttunityPOC.HeartBeat_Sequence_proc ( p_txt in varchar, p_num in number)
    IS
    BEGIN
          INSERT INTO AttunityPOC.HeartBeat (text_Source) values ( p_txt || p_num );
          COMMIT;
    END;
    /
    -- Kick of a Job ... to run the procedure ... repeatedly ... to make changes ---
    -- Start 'now' (sysdate), Next time is next minute: sysdate+1/24/60

    Code:
    SET SERVEROUTPUT ON
    DECLARE
         job_number NUMBER;
    BEGIN
    DBMS_JOB.SUBMIT(job_number, 'AttunityPOC.HeartBeat_Sequence_proc( ''Source Job='',JOB) ;', 
        sysdate,'sysdate+1/24/60'); COMMIT; 
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Heartbeat job number : ' || job_number);
    END;
    /
    -- Handy SQLplus commands --

    Code:
    SELECT job,TO_CHAR(next_date,'yyyy-mm-dd hh24:mi:ss') "Next", 
     SUBSTR(interval,1,20) "Interval",  SUBSTR(what,1,40) "What" 
     FROM dba_jobs ORDER BY job;
     
    EXEC DBMS_JOB.REMOVE(xx);
     
    ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF2';
     
    COLUMN dtSource FORMAT A20;
     
    SELECT * FROM (SELECT * FROM AttunityPOC.HeartBeat ORDER BY dtSource DESC) WHERE rownum < 4 ;
     
    select job, sid from dba_jobs_running order by job;
    -- Got some Changes?

    COLUMN dtSource FORMAT A20;
    SELECT * FROM (SELECT * FROM AttunityPOC.HeartBeat ORDER BY dtSource DESC) WHERE rownum < 4 ;

    ID TEXT_SOURCE DTSOURCE
    ---------- ------------------------------ --------------------
    5 Source Job=24 11/19/13 13:10:51.00
    4 Source Job=24 11/19/13 13:09:50.00
    3 Source Job=24 11/19/13 13:08:50.00



    Next, set up the SQLserver target...
    Btw.. be ready for that to be whiped away if one fat-fingers
    the Replicate tasks design and allows it to Drop-and-recreate.


    Code:
    DROP TABLE [dbo].[HeartBeat]
    GO
    CREATE TABLE [dbo].[HeartBeat](
          [Id] [bigint] NOT NULL,
          [dtSource] [datetime] NULL,
          [dtReplicate] [datetime] NOT NULL,
          [dtTarget] [datetime] NULL,
          [text_Source] [varchar](30) NULL,
          [text_Replicate] [varchar](30) NOT NULL,
          [text_Target] [varchar](30) NULL
         
    PRIMARY KEY CLUSTERED 
    (
          [Id] ASC,[text_Replicate]
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    ALTER TABLE [dbo].[HeartBeat] ADD  CONSTRAINT [AttunityPOC_def_date_1]  DEFAULT (getdate()) FOR [dtTarget]
    GO

    Now we are ready to use the Attunity Console to add this table to the tasks to be monitored.

    - select and open task
    - [table Selections]
    ... Schema
    ... Heart [search]
    ... [ Add > ]
    ... [ OK ]
    - select table and activate [Table Settings]
    ... [General]
    ... Map to Target table .. Table schema (as desired)
    ... [Transform]
    ... [Add Column] dtReplicate, DATETIME, [...] Functions -> Date and Time -> datetime()
    ( no arguments, [evaluate], [test], [ok] )
    ... [Add Column] text_Replicate, STRING[30], [...], 'My Replicate Task XXX was here'
    ( quored string, [evaluate], [test], [ok] )
    ... [OK]
    [OK]
    [CLOSE] warning of changed table.
    [Task Settings] -> Full Load -> Full Load Settings -> It target table already exists: [do nothing]

    Note... under default usage, a full-load from any tasks with this table would blow away all data.
    That may be ok, or maybe not. The last option selected is a crude workaround for now.


    The effects of these Replicate Task Design changes can be seen in the JSON for the task.
    It is possible to take an existing task, export it, merge in the sections below,
    and import it, as alternative to the console GUI.

    # repctl exportrepository task=XXX
    # notepad ..\data\imports\XXX.json


    Code:
    :
        "explicit_included_tables": [{
               "id":  0,
               "owner":      "ATTUNITYPOC",
               "name":       "HEARTBEAT",
               "estimated_size":    0,
               "orig_db_id": 88653
        }]                                                          }
       },
       "targets":    [{
    :
       "manipulations":     [{
             "name":       "ATTUNITYPOC.HEARTBEAT",
             "table_manipulation":      {
                    "owner":      "ATTUNITYPOC",
                    "name":       "HEARTBEAT",
                    "keep_columns_without_manipulation":     true,
                    "new_owner_name":    "dbo",
                    "transform_columns": [{
                              "column_name":       "ID",
                              "action":     "KEEP",
                              "new_data_type":     "kAR_DATA_TYPE_NUMERIC",
                              "precision":  38,
                              "scale":      10,
                              "is_pk":      true
                       }, {
                              "column_name":       "",
                             "new_column_name":   "dtReplicate",
                              "action":     "ADD",
                              "new_data_type":       "kAR_DATA_TYPE_TIMESTAMP",
                              "scale":      9,
                              "computation_expression":  "datetime()"
                       }, {
                              "column_name":       "",
                              "new_column_name":   "text_Replicate",
                              "action":     "ADD",
                              "new_data_type":     "kAR_DATA_TYPE_STR",
                              "length":     30,
                              "computation_expression":  "'My Replicate Task XXX was here'"
                       }]
             }
       }],
    And in the end, the tab-seperated results look like:

    Id dtSource dtReplicate dtTarget text_Source text_Replicate text_Target
    48 2013-11-19 13:53:55.000 2013-11-19 18:53:57.000 2013-11-19 13:53:59.477 Source Job=24 My Replicate Task XXX was here NULL
    49 2013-11-19 13:54:55.000 2013-11-19 18:54:58.000 2013-11-19 13:55:00.213 Source Job=24 My Replicate Task XXX was here NULL
    50 2013-11-19 13:55:55.000 2013-11-19 18:55:58.000 2013-11-19 13:56:00.930 Source Job=24 My Replicate Task XXX was here NULL
    51 2013-11-19 13:56:55.000 2013-11-19 18:56:59.000 2013-11-19 13:57:01.663 Source Job=24 My Replicate Task XXX was here NULL
    52 2013-11-19 13:57:56.000 2013-11-19 18:58:00.000 2013-11-19 13:58:02.277 Source Job=24 My Replicate Task XXX was here NULL

    Hmmm... my have to tighten up my time-zone handling...
    The dtReplicate is inserted as UTC time.
    We'll leave that as an exercise for the next person!

    Good luck,
    Hein
    Last edited by Hein; 03-20-2014 at 10:53 AM.

  2. #2
    mikedba is offline Junior Member
    Join Date
    Nov 2014
    Posts
    8
    Rep Power
    0
    Great minds must think alike as I create a table in the source oracle database with a database job that updates a row every minute which comes in handy for so many different reasons as below:
    Code:
    sqlplus system
    ALTER USER CDC_ATTUNITY QUOTA UNLIMITED ON USERS;
    grant create table to cdc_attunity;
    exit
    Code:
    sqlplus cdc_attunity
    create table CDC_ATTUNITY.TICK_TOCK (tick_time DATE not null);
    alter table CDC_ATTUNITY.TICK_TOCK add constraint TICK_TOCK_PK primary key (TICK_TIME);
    insert into cdc_attunity.TICK_TOCK(tick_time) values(sysdate);
    COMMIT;
    VARIABLE jobno NUMBER;
    BEGIN
      DBMS_JOB.SUBMIT(:jobno,
        'update cdc_attunity.TICK_TOCK set tick_time = sysdate;',
        SYSDATE, 'SYSDATE+1/(60*24)');
      COMMIT;
    END;
    / 
    select to_char(tick_time,'DD-MON-YYYY HH24:MI:SS') cdc_attunity.tick_time from TICK_TOCK;
    exit

  3. #3
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    163
    Rep Power
    12

    Microsoft SQL Server source for Heartbeat.

    To follow up on the Oracle Source example, here is how you would do a similar thing with SQLserver.

    First create a source table

    Code:
    CREATE TABLE [dbo].[HeartBeat](      [Id] [int] IDENTITY(1,1)  NOT NULL,
          [dtSource] [datetime] NULL,
          [text_Source] [varchar](30) NULL,
         
    PRIMARY KEY CLUSTERED ( [Id] ASC )
    )
    ALTER TABLE [dbo].[HeartBeat] ADD  CONSTRAINT [HeartBeat_def_date_1]  DEFAULT (getdate()) FOR [dtSource]
    GO
    
    -- to test --
    insert into HeartBeat ( text_Source ) values ( 'Hello World' )
    
    -- to verify
    select * from HeartBeat
    Next you'll need JOB.
    Use SQLserver --> SQL Server Agent --> Jobs --> [new job] to get activity. Add a schedule for 'every day', once a minute.


    Detailed example below..
    Roughly...


    Code:
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' 
    DECLARE @jobId BINARY(16) 
    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'HeartBeat', ... , @job_id = @jobId OUTPUT 
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Tick', .. 
    @command=N'insert into HeartBeat ( text_Source ) values ( ''Hello World'' )', 
    @database_name=N'replicate_source', 
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'HeartBeat Schedule', 
    @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=4, @freq_subday_interval=1, 
    @active_start_time=0, @active_end_time=235959, 
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

    Full code below...

    Enjoy,
    Hein.


    Code:
    USE [msdb]
    GO
    
    
    /****** Object:  Job [HeartBeat]    Script Date: 2018-02-14 16:41:43 ******/
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    /****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 2018-02-14 16:41:43 ******/
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    
    END
    
    
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'HeartBeat', 
            @enabled=0, 
            @notify_level_eventlog=0, 
            @notify_level_email=0, 
            @notify_level_netsend=0, 
            @notify_level_page=0, 
            @delete_level=0, 
            @description=N'Tick Tock', 
            @category_name=N'[Uncategorized (Local)]', 
            @owner_login_name=N'att_user', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** Object:  Step [Tick]    Script Date: 2018-02-14 16:41:43 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Tick', 
            @step_id=1, 
            @cmdexec_success_code=0, 
            @on_success_action=1, 
            @on_success_step_id=0, 
            @on_fail_action=2, 
            @on_fail_step_id=0, 
            @retry_attempts=0, 
            @retry_interval=0, 
            @os_run_priority=0, @subsystem=N'TSQL', 
            @command=N'insert into HeartBeat ( text_Source ) values ( ''Hello World'' )', 
            @database_name=N'replicate_source', 
            @flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'HeartBeat Schedule', 
            @enabled=1, 
            @freq_type=4, 
            @freq_interval=1, 
            @freq_subday_type=4, 
            @freq_subday_interval=1, 
            @freq_relative_interval=0, 
            @freq_recurrence_factor=0, 
            @active_start_date=20180214, 
            @active_end_date=99991231, 
            @active_start_time=0, 
            @active_end_time=235959, 
            @schedule_uid=N'0c02ecdf-72da-4d9d-a8c0-5aea6b07974b'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    
    
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •