Migration to 9.1 and CRC32 function

danddmrs · Post by **danddmrs** » Fri Feb 07, 2014 11:34 am

Currently we are migrating from 7.5.2 to 9.1. In 7.5.2 we use the CRC32 function to detect changed data in a given record. When we run the same data in 9.1 and 7.5 we get different values from the CRC32 function so we are getting a good deal of false updates in our data warehouse. This wouldn't be a big deal except that we keep history records in the DW so we would waste space unnecessarily.

Should there be a difference in how CRC32 values are calculated?

chulett · Post by **chulett** » Fri Feb 07, 2014 12:11 pm

It really seems like there shouldn't be a difference between the two in order to maintain backwards compatability and avoid exactly what you are seeing. However I could see that there could be a difference depending on the coding / seeding of the function.

In your shoes I'd suggest contacting your official support provider and asking them. Perhaps it is a known issue with a fix or there is an option to revert to the old behaviour? Hey, one can dream!

danddmrs · Post by **danddmrs** » Fri Feb 07, 2014 12:37 pm

NLS is disabled in 7.5.2 but not in 9.1. Could this be affecting what is passed to the function?

Setting up some jobs to capture the values passed to the function.

chulett · Post by **chulett** » Fri Feb 07, 2014 2:02 pm

danddmrs wrote:NLS is disabled in 7.5.2 but not in 9.1.

Ah... that could very well be the issue. Not sure exactly why but it wouldn't surprise me if that's the root cause.

ray.wurlod · Post by **ray.wurlod** » Sun Feb 09, 2014 4:30 pm

It would certainly explain the difference if the CRC32 function operates on bytes rather than on characters. The non-NLS and NLS byte streams are very likely to be different even for "extended ASCII".

roy · Post by **roy** » Mon Feb 10, 2014 7:33 am

I've came across several cases of CRC32 and the likes of it, of any form and provider, returning same output for different inputs !!!
There for I never use them as detecting changes or serogate keys.

chulett · Post by **chulett** » Mon Feb 10, 2014 8:23 am

... and that is a completely different discussion.

roy · Post by **roy** » Mon Feb 10, 2014 8:49 am

Just thought its important to note in case the one using it is not aware of this.

danddmrs · Post by **danddmrs** » Mon Feb 10, 2014 10:08 am

NLS was indirectly to blame. There is a data edit routine that was modified because of NLS being enabled. One of the functions of the routine was to reduce fields consisting of only multiple spaces to a single space. This was omitted in the 9.1 version so since ' ' is not the same as ' ' the CDC calculations were not the same. Correcting the data edit routine has resolved the issue.

Thanks all for your input.

chulett · Post by **chulett** » Mon Feb 10, 2014 10:33 am

Good catch! :D

And in case anyone was wondering why the two examples of spaces in a string look the same, that's because the forum software automagically removes any 'extra' spaces, so the quotes with four spaces between them end up looking the same as the quotes with one space between them. Code tags solve that issue, even though this isn't really code:

Code: Select all

This was omitted in the 9.1 version so since ' ' is not the same as '    ' the CDC calculations were not the same.