Translated Transaction Log / Mobilink

[Baron]

Dear sirs, I am translating the trnsaction log files for tracing some bugs in our program and it is not clear for me to distinguish who has executed each statement on DB. Our program writes in DB and the mobilink writes also simultaneously on this same DB. Does the mobilink leave any trace to show that any specific statement is done by him?

[regdomaratzki]

Do you mean Mobilink or do you mean the MobiLink client (dbmlsync)?

Volker's answer is correct if you are talking about the MobiLink Server, since it's just an ODBC application connecting to your database. If you are discussing translating a transaction log that the dbmlsync process was writing to, there are a few tricks to help you find operations applied by dbmlsync as opposed to your application.

[Baron]

Actually I am looking for some bugs on both sides (Cons. & Rem.). Yes exactly as you mentioned, the options from Volker worked only on Cons., is there any way for Rem. side?

The situation is that there are some rows written in DB with incorrect values, and the first challenge is to determine whether these rows are writting through mobilink or through the application connected to the DB. The related table is synchronising in both directions.

[regdomaratzki]

I've updated Volker's answer below to also include information about the client.

[VolkerBarth · Accepted solution]

Whereas SQL Remote's operations can easily be traced within the translated log via dbtran -is SR, there's no particular flag for ML.

On the server side, when using a particular user for the MobiLink Server connection to the consolidated database (as recommended), you can use options -u or -x to include/exclude particular users.

On the client side, whenever dbmlsync connects to a remote database, it writes an APPLICATION CONNECTION REGISTRATION STAMP. In the sample below, you can see that connection "1017" connects, writes the dbmlsync stamp, inserts a row, updates a system table and then commits.

--CONNECT-1017-0000007542-DBA-2017-09-07 12:59
--APPLICATION CONNECTION REGISTRATION STAMP:-1017-0000007553
--  app_Name = sybase.asa.dbmlsync
--  app_info_str = uid=dba;
--  conn_label = Main
--  status = 0x3
--INSERT-1017-0000007595
INSERT INTO DBA.parent(pid,data)
VALUES (1,'8bdbbd50-11b8-4308-aa64-190ae9bf9e67')
go
--INTERNAL STORED PROCEDURE-1017-0000007649
call sa_sync_sub( 1, 'rem1', 'SET LASTDOWNLOADTIME', '2017-09-07 12:59:39.972000' )
go
--COMMIT-1017-0000007710
COMMIT WORK
go

Hope that helps,

Volker + Reg

[Baron · Accepted solution]

I can figure out from the transaction log file when has the mobilink client connected, but the problem is that our application is simultaneously connected to DB, so that the logs from Mobilink and our App are interlaced.

[regdomaratzki · Accepted solution]

Every operation in the transaction log has the connection ID associated for it. In my example, the connect shows that it was connection ID 1017 ( --CONNECT-1017- ), and you can see that the log operations for the insert ( --INSERT-1017- ) and commit ( --COMMIT-1017 ) were on this same connection. If your application was also connected at the same time, it would have a different connection ID from 1017 and you would be able to differentiate those operations from those made by dbmlsync. If that connection ID was 1065 an insert from that connection might look like :

--INSERT-1065-0000007775
INSERT INTO DBA.parent(pid,data)
VALUES (754, 'inserted from application')
go

If this is the operation that you think is suspect, then search back in the translated log for "--CONNECT-1065-" and look to see if that connect has an APPLICATION CONNECTION REGISTRATION STAMP from dbmlsync.

In general, lines in the translated log file starting with "--" have the format :

--TYPE_OF_OPERATION-CONNECTION_ID-LOG_OFFSET[-EXTRA_DATA]

Reg

[Baron · Accepted solution]

Thanks, your explanation was very helpful. Now I could determine that our application is who has executed this suspected INSERT statement. I have also noticed that this 'connection id' is not permanently allocated for each user/application, so it is important to check which connection id was allocated to dbmlsync in the LAST synchronisation. But it was already clear in your explanation.

[Baron · Accepted solution]

One more question: If some row is inserted over a specific connection-id, and this insertion has fired some internal trigger, then would the execution of this trigger have always the same connection id as the insert statement? in other words, would the fired trigger inherit the connection id? Does this apply for all cases (dbmlsync, mlsrv, DBA...etc)? Over my walkthrough on our transaction logs I have found that the answer is YES. Could you confirm please?

[VolkerBarth · Accepted solution]

Triggers execute as part of the according DML statement, so by design they do run within the same connection and transaction as the triggering DML statement.

---

Of course a trigger could "trigger" an event (created via CREATE EVENT), and events run within their own separate connection and transaction, so those actions would show a different connection id (with values above 1 billion).