Is it possible to convert the values stored in the "Change_Mask" column into bit format?

[khchoy]

I'm trying to convert the values stored in the "Change_Mask" column into bit format or Hex values for transformation into bit format, but I can't do it because I don't know the format of the stored values. This is to distinguish between columns updated with 'Null' values and those not updated in the "Change table" used in store changes. While during "change apply," we can differentiate in the "attrep_changes_xxxxx" table, such as 'attu_Null', the "change table" doesn't allow distinguishing whether a column was updated with Null values or if no update occurred. Therefore, I request a method to change the values of "Change_Mask" into bit format (e.g., a value that allows checking the changed column positions like 1100111).

[Heinvandenheuvel]

>> I'm trying to convert the values stored in the "Change_Mask" column into bit format or Hex values for transformation into bit format, but I can't do it because I don't know the format of the stored values.

[edited] The change_mask is a hex value to represent the bits. It is a bit (sic) tricky to test bits in SQL - trivial in C. In SQL to test say  column 11  (bit 2 in byte 1 both starting at 0) ,  you would have to extract the second byte as hex, convert to integer in order to AND with the value 4. (bit 0=1, 1=2, 2=4, 3=8).  Per documentation:

The change mask is a string of hexadecimal digits, representing a bitmask of data columns in little-endian order.
The bit position in the change mask is based on the ordinal of
the column in the metadata message of that table.
This means that if there are 10 data columns, they occupy bits 0 to 9 in the bitmask.
If UPDATE mask is 0B hexadecimal, which is 1011 binary – it means that the columns at ordinals 1, 2 and 4 were changed.

>> the "change table" doesn't allow distinguishing whether a column was updated with Null values or if no update occurred.

Hmm, i believe that if you see a null-value, then that's what went into the column. It does not mean that it was not changed in normal cases. There are perhaps exceptions when the column values could not be retrieved due to incorrect supplemental logging.

Hein.

[khchoy · Accepted solution]

Hi Hein,

Thanks yor answer. But it is a little bit confused me.

Could you provide a more specific example? For instance, if an update occurs in a table with 10 columns (c01, c02, c03, c04, c05, c06, c07, c08, c09, c10) where c01 is the primary key, and if columns c02, c03, c05 and c10 are changed to specific values, what would be the hexadecimal value and the stored bit value? Also, if the value of co4, c05 and c09 was not originally null but updated to null, what would be the stored hexadecimal value and the stored bit value?

In the manual, it states that Byte 0 is represented as bit7 bit6 bit5 bit4 bit3 bit2 bit1 bit0, and Byte 1 is represented as bit15 bit14 bit13 bit12 bit11 bit10 bit9 bit8. Given the example, could you specify which bit stores the changed values for c02, c03, c05, and c10, and what the stored hexadecimal value would be? Additionally, when c04, c06 and c09 changes from not null to null, which bit stores this change, and what would be the stored hexadecimal value?

I apologize for the detailed question, but the manual's explanation is quite challenging to understand, and even when attempting to convert the stored values from hexadecimal to bits, it seems there might be discrepancies. Could you please provide a concrete example?

Regards,
Kwang Ho

[Heinvandenheuvel · Accepted solution]

[hmmm... why mark a follow up question as 'solved' ?]

>>> c02, c03, c05 and c10  

Those would be bits 1 (first bit is 0) - value 2^1 = 2,  bit 2 - value 2^2 = 4,  bit 4 val 16 (0x10)  and bit 1 in byte 2 val 2

The bit pattern would  little endian     0000 0010 0001 0110  and hex 02 16

C:\>perl -le "$m = 0x216; printf q(mask=%x bits=%b), $m, $m"
mask=216 bits=1000010110

Additionally, when c04, c06 and c09 changes from not null to null, which bit stores this change, and what would be the stored hexadecimal value?

Well, the value changed, whether it is from one value to another value or to null - it still changed so I expect the bits for those colunss also be set.

That would add C04 = bit3 = 8, C06 = bit5 = 32 = 0x20, C09 = bit 0 = 1 in byte 1.

That would be 0x0128

C:\>perl -le "$m = 0x0128; printf q(mask=%x bits=%b), $m, $m"
mask=128 bits=100101000
-- combined --
C:\>perl -le "$m = 0x128+0x216; printf q(mask=%x bits=%b), $m, $m"
mask=33e bits=1100111110

The best thing for you to do is to run the test as you describe in dev and check the resulting mask values.

Use some scientific calculator to convert bits to hex or use perl as I did.

Hein.