checksums to compare data

gerie

gerie

Member
I'm planning a migration from one server to an other.
Nou the auditor says he wants to see checksums for the financial tables.

Is there a progress function that I can use? Is there a statement I can use? Or does anyone know about suitable software packages that can do the work for me?
 
If the two databases are in the same state then you could dump the tables on both servers and then use the "sum" command (UNIX) to compare them.

I hope that this is a small system. It might take a long time to do this with a large system ;)
 
I don't have linux, it's a windows 2003 server. The databases I need to compare are 64 gb each. For me personally I would say a db analys on both db's and compare the record counts per table. But the auditor expects me to run checksums to prove that I didn't temper with the database. They mentioned things like amount * number or date and then the totals as hash totals. Therefor I need extra checks that i can run. Either with the help of an extra program, or a statement, or a db functionality.

So, any input is welcome. :-(
 
Windoze must have something equivalent to "sum" that you could run on dump files. Other than that I guess you could do like you say and run through the data calculating a value.

But I see no reason why the auditor should trust that any more than trusting a simple assertion that you haven't tampered with the data. How would the auditor know the difference between a program that really does do the comparison and one which simply says "42" without actually doing any work?

(IMHO your auditor is insane.)
 
Funny as your remarcs are to me ;-), it's not really helpfull. I'm now getting the question if I can do a one on one comparisment of every record in the database in order to give the insurance that there is absolutely no dataloss and the data is identical.

Pfffff. I'm hoping anyone has dealt with this before and can give me a solution.
 
I have indeed dealt with auditors asking this kind of question before. After explaining what it would take to satisfy their desire the business inevitably tells the auditor that such a comparison is not necessary.

1) there is no built-in checksum for a table in Progress (or any other db)
2) the dbanalys comparisons will often satisfy them
3) a detailed explanation of your migration process and the safeguards it has is also helpful
4) if your application has financial analysis reports there should be some summary types of things that would demonstrate that the data has not changed

But, ultimately, if the business supports this demand you will end up having to do something like:
Code: 
for each table no-lock:
   cs = cs + calcCS( table ).
end.

Instead of calculating a check sum you could also try finding the same record in both databases and doing a buffer-compare but that has issues too. If the migration is a dump & load you cannot do it by recid and you would also have to detect and handle missing/extra records. All in all it would be a big coding hassle for no real benefit. But, of course, the business has the ultimate say on whether or not they want to do that.

And with a 64gb db that is going to take a while (you will have to do it twice - once for each db).
 
You are very right. I even tried to convince the people involved that I should be able to talk with the auditor, because I couldn't imagine that he really wanted the things the testcoordinator told me. However, I don't get to speak with him directly so my hands are a bit tied there.

I'll take a further look at the suggestions you made. Thank you for your input. :-)
 
Some slightly more detailed code:

Code: 
define variable r as raw no-undo.
define variable i as integer no-undo.
define variable j as integer no-undo.
define variable x as integer no-undo.

for each customer no-lock:
  raw-transfer customer to r.
  j = length( r ).
  do i = 1 to j:
    x = x + get-byte( r, i ).
  end.
end.
display x.

This isn't exactly production-ready ;) It's just a "proof of concept".
 
:D I can make this production ready. Thnx.

In the meanwhile I'm still trying to convince everyone that my checks are sufficient enough. Keeping my fingers crossed to see if my message is getting trough.

I was already thinking, I could just write some code and just tell them that the outcome was indeed 42. :rolleyes:
 
I have not thought through all of the possible "gotchyas" in that code... ;)

I don't know for sure one way or the other but there might, for instance, be legitimate reasons why two records that have the same data in them look different to RAW-TRANSFER. Particularly if the db has been dumped and loaded.
 
You must log in or register to reply here.
Back
Top