SQL Remote Monitoring (Relationship Offset/Date)

[Baron]

I am trying to build some monitoring mechanism on a cons. communicating with several remotes using DBREMOTE. The goal of this mechanism is to make sure that all remotes have already received all the trnasactions from cons. till the end of yesterday.

In the table SYSREMOTEUSERS I can read the confirm_sent, but there is no any relationship between the offset in transaction log file and the date of this offset!

The time_received in SYSREMOTEUSERS does not help too.

Of course I can do this manually by translating the transaction log files on cons. and then I can tell on which date/time has a specific offset took place.

My question is, can I find this information anywhere in the Database?

Select OffsetDate from SOMESYSTEMTABLE where Offset = xxxx

I know this is a stupid query, but just to convey my idea.

[regdomaratzki · Accepted solution]

There is no system table that maps transaction log offsets to a timestamp. It would be pretty trivial to implement yourself. Untested sample code below.

create table SOMESYSTEMTABLE (
  Offset numeric(20,0) not null primary key,
  OffsetDate timestamp default current timestamp
);

create or replace event addOffsets
schedule start time '1900-01-01 00:00:00.000' every 5 minutes
enable 
handler 
begin
  insert into SOMESYSTEMTABLE(Offset) values (db_property ('CurrentRedoPos'));
end;

create or replace event removeOffsets
schedule start time '1900-01-01 00:00:00.000' every 5 minutes
enable 
handler 
begin
  delete from SOMESYSTEMTABLE where Offset < ( select min(confirm_sent) from SYSREMOTEUSER );
end;

Reg

[Baron · Accepted solution]

Thank you very much for the amazing answer. I did not know the db_property ('CurrentRedoPos')

[VolkerBarth · Accepted solution]

Or you could do so with a SQL Remote hook procedure, say via sp_hook_dbremote_send_end() to add offsets and via sp_hook_dbremote_end() to remove those...

That might be more focussed on running SQL Remote compared to a purely scheduled solution...

[Baron · Accepted solution]

Yes, thank you too.

The main idea was about db_property('CurrentRedoPos')