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.

asked 09 May, 03:57

Sarkis's gravatar image

Sarkis
494223350
accept rate: 0%

edited 09 May, 03:58


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

permanent link

answered 09 May, 09:38

Reg%20Domaratzki's gravatar image

Reg Domaratzki
6.1k33793
accept rate: 37%

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

(09 May, 09:42) Sarkis
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, 09:43) Volker Barth
Replies hidden

Yes, thank you too.

The main idea was about db_property('CurrentRedoPos')

(09 May, 09:46) Sarkis
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×89
×58
×50

question asked: 09 May, 03:57

question was seen: 88 times

last updated: 09 May, 09:46