|
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. |
|
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 Thank you very much for the amazing answer. I did not know the db_property ('CurrentRedoPos')
(09 May '19, 09:42)
Baron
3
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...
(09 May '19, 09:43)
Volker Barth
Replies hidden
Yes, thank you too. The main idea was about db_property('CurrentRedoPos')
(09 May '19, 09:46)
Baron
|
