What is Database Consistency verification (UTIBASE) and what can it do for you?

How many times have you looked at Sage Enterprise Management and noticed a new Menu item or function? Today I like to talk about Database Consistency verification (UTIBASE). 
You can run this function from Development, Utilities, Verifications, Data.
Before I talk about one interesting usage of this tool, if you look at function help for this function you will see below.



As the online help mentions, running this tool for the entire data base may take a while, and you may also need to analyze the result of this tool , in a case by case fashion, because there might be instances that the tool shows some problem, which might be ok.

So the way that this tool works, is that for each field in a table, that has a linked table , it checks to make sure the corresponding record on linked table exists or not.
For example, in SORDER table there is a field called HLDUSR that has a link to table AUTILIS.

So when you run this tool, (for example for SORDER table) among other fields, it will check to make sure for each record in SORDER, there is a corresponding record in AUTILIS, based on HLDUSR field and if the user does not exists, in AUTILIS table, you will see the error in the log produced like below.

So, let’s talk about an interesting usage of this tool. Have you ever had a situation, that a record with blank key existed on one of your tables, and you saw errors or issues, that the root cause was ended up because of the existence of a blank key?

If you answered yes to that question, you may recall how much troubleshooting efforts you might have done to get to the root cause, and this tool might be able to help with that.
For example, in my system, here I have a Sales order with blank SOHNUM, and when I run UTIBASE tool, I would see below in the log.



Conclusion: You can use UTIBASE function to check the links between tables.

Anonymous