Translating replication messages (SQL REMOTE)

[Baron]

Dear sirs, is there any tool to translate the messages being transferred between consolidated and remote databases? (In case of replication using SQL REMOTE technology). Thanks in advance

[Former Member]

Using -v -a -p -o & -c switches in combination will not apply the messages (-a), and will not purge them (-p), but will log the contents to the console log (-v -o).

Of course this will need to be ran on the machine which has the messages needing to be translated and connected (-c) to the database that would normally be receiving them. The messages and database files can be (safely) moved to another location together to be ran offline.

Since this will only translate the messages and will perform no other actions for the receive phase you should probably restrict this to a single pass (-b) of just the receive phase (-r) so I would add these 2 switches as well.

[VolkerBarth]

AFAIK, you can not directly "read or translate" those messages (except when using method 3) but there are ways to get to know the message contents:

1. Run DBREMOTE with option -v (verbose): That will list all SQL statements that are read and applied (or fail) by DBREMOTE based on the received messages. (Highly recommended to understand the work of SQL Remote...)

2. Use DBTRAN -sr on the sending database to list those commands that will be sent (or have been sent) to the other databases. (You may need to know the according translog opffest ranges here.)

3. Use DBREMOTE with compression option set to 0. AFAIK that will produce "readable" message files. Not recommended for production use IMHO.

[Baron]

What do you mean with method 3? I read the documentation for the available options for DBREMOTE and could not find any option for compression: Could you please write me an example for DBREMOTE with compression option?

[VolkerBarth]

Here you are:

compression option [SQL Remote]

It's set like any other SQL Remote option. i.e. via

SET OPTION PUBLIC.compression = 0;

I'd highly recommend to test that in a test environment (as any other SQL Remote aspect!).

[Baron]

Changing the option did not help having readable version of produced messages. I need to read the produced messages, since I am receiving a weird error message stating (message does not belong to me). in the error message is also stated that there is discrepency between the message header and log offset.

[VolkerBarth]

a weird error message stating (message does not belong to me)

That's not necessarily a "weird" error. It might just tell that the database receives a message with an unfitting log offset, might happen when the database has been re-extracted in the meantime (and the incoming message was created before the re-extraction so the message is "too old") or has been restored from a backup (so the received message is "too young").

Do you also get the warning "Received operations sent to a previous extract of this database or sent to pre-recovered version of this database."?

That being said, do you use DBREMOTE -v -o to log verbose output?

[regdomaratzki]

Did you try Nick's suggestion to use the "-r -b -a -p -v -o file.txt" options on dbremote on the incoming message to do a single pass of the receive phase, not apply message and do not delete messages, but run with high verbosity (i.e. print the messages) and write the output to a file?

When you are trying to get help with an error message you are seeing, it is better to post the actual error message from the log file instead of describing the error. It will be easier for us to help you.

Reg

PS: Directly answering your initial question --> No such tool exist.