Sybase 16: creating a trigger to detect inconsistent table updates

[former_member508873]

Dear Everyone,

We face a strange inconsistency issue in a system, an order item entry in our application is updated with outdated data. An important flag is reset and one timestamp field is set to a past date, this causes an issue in the application. This inconsistency occurs randomly, it is not reproducible, there is no identified pattern. We activated and checked application level/sytem logs, implemented extra checks, double checked the transaction handling of the application but nothing was found at all. We could not identify any suspicious standard/custom ABAP code which could be responsible for this symptom.

Now we try to catch the root cause from DB side which is SYBASE / 16.0.03.07. The issue happens rarely, 1-2 times in 2 days, because of that we want to avoid the dbcc traceon approach in isql, the log would surely explode before catching anything useful. We think that creating an update trigger for error logging could be helpful in this case, we could extract some data about the mysterious process. It could be good to know when this false update happens, who is the responsible user, based on these we could track back the ABAP process.

As an ABAP application developer I haven't got any clue about Sybase until now, at the moment I try to figure out the logic of the new trigger from application point of view. How to create a simple trigger and setting up the logical condition is fine, I found quite good help materials. What it is not clear what we could print in the BEGIN-END section

CREATE TRIGGER false_update
ON /namespace/tablename
FOR UPDATE
AS IF ....some condition check on the fields
BEGIN
PRINT "Invalid table update"
PRINT "Timestamp field is incorrect"
PRINT getdate()
...
END

In the Sybase Infocenter I found some hints, for example I can call the getdate() function and I could print its result. ( http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1572/html/commands/... ) But I rather need a username if possible, (not the database owner), a current timestamp. I'm not sure what else can I use, is current_date(), current_time() valid in this context, can I call these in a trigger? In the Adaptive Server® Enterprise 15.5 Reference Manual: Building Blocks ( http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36271.1550/pdf/blocks.pdf ) I found the user function as well.

I'm afraid that in Sybase level only some technical user can be logged, I'll not be able to figure out here who executed the ABAP process which resulted the inconsistent update. So the real question is: can we use these functions in the BEGIN-END section of the trigger to log the user and date/time of this inconsistency? Do you maybe have further hints what else informative could be extracted?

PRINT current_date()

PRINT current_time()

PRINT user

Thanks a lot in advance,

Akos

[luc_vanderveurst]

Hello Akos,

When you execute a print statement in a trigger, the application who is doing the update should be aware of this and handle the result. Not every application will be able to cope with this.

I would use a print statement in a trigger when it's temporary and when the update is done via sqsh or isql.

In your example, since you don't want to rollback the transaction, I would create a table with the columns you are interested in and additionally columns for the update-time and the user who executes the update.

You figured out how to get the time: select getdate(), the username executing the query can be found with: select suser_name().

Another option is to use dbcc logprint, you can read more about this in :

https://launchpad.support.sap.com/#/notes/0002079594

The user who executes the dbcc logprint command needs to have the sybase_ts_role.

The message are written in the errorlog file of the server.

Easier then creating a table, but getting the info about the 'abnormal' updates is easier when you can select from a table.

HTH,

Luc.

[former_member508873]

Dear Luc,

Thank you very much for your quick reply. Creating a new table for storing the logged data is a good idea, I haven't though about that, I'll check how it can be implemented. I know that is it possible to do a rollback in a trigger (rollback transaction) and initially I haven't really considered this option. Or the fact that using the print statement could cause error in the application level.

But thinking about that theoretically: basically I want to find an ABAP process which results in a database inconsistency. This should be prevented, the process should be corrected. If it is aborted due to some error coming from the DBMS then it would have an obvious sign in the system. Update terminations can be monitored and investigated for more detail. Once it is detected, we can drop this trigger and focus on the aborted process. It is harder than saving some logs and analyzing them later but a considerable approach.

I would like to help me to understand yourstatement: "...When you execute a print statement in a trigger, the application who is doing the update should be aware of this and handle the result. Not every application will be able to cope with this...." In our case we are talking about some ABAP process which performs an update at the end. In same cases update terminations might happen e.g. a new table entry is to be inserted with an already existing key. We can check this update termination, when it happened, who was the user, what was the update function. Can we expect an update error/termination if a PRINT statement is used in the trigger, is that what you meant by this statement?

Thanks a lot,

Akos

[former_member508873]

Dear Mark,

I really appreciate your quick and very detailed response on this. As I'm not a DBMS expert, I'm still digesting some of these recommendations, I have to look after some things 🙂

Auditing would be too complex in this case, I think we could get the root cause with less effort by following your other recommendations.

Creating a temporary table for storing the log details is basically a favorable approach, also seems to be a safe one.

Just like Luc mentioned in his memo, you also pointed out that the result of the PRINT statement is returned to the client. I was thinking about that a bit and its possible impact. We are talking about something which generates inconsistencies, this has to be avoided. If the inconsistency is not saved then there is nothing to be cleaned up with some correction report as a follow-on action. If I understand you correctly this PRINT can cause an application error in the main process (which is most probably an ABAP process) e.g. an update termination occurs. My assumption is (I hope it is correct) then it would lead to a complete rollback, nothing would be changed in the database. I searched for some sources regarding this but I could not find an exact answer. What do you think about that, is this expectation correct?

Kind regards, Akos

[former_member508873]

Hello Mark,

Thank you very much for your feedback. I think I have enough information to go on with this case now, I really appreciate your help on this.

Best regards,

Akos