Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

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 '19, 03:57

Baron's gravatar image

accept rate: 48%

edited 09 May '19, 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
  insert into SOMESYSTEMTABLE(Offset) values (db_property ('CurrentRedoPos'));

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


permanent link

answered 09 May '19, 09:38

Reg%20Domaratzki's gravatar image

Reg Domaratzki
accept rate: 36%

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

(09 May '19, 09:42) Baron

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
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 09 May '19, 03:57

question was seen: 1,161 times

last updated: 09 May '19, 09:46