Data Replication 2
Results 1 to 3 of 3

Thread: interpreting header__change_mask column from Change Tables in store changes task

  1. #1
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    151
    Rep Power
    12

    interpreting header__change_mask column from Change Tables in store changes task

    - Updated Sat 9/30 9:00 EST -

    This subject is not really SQLserver (target) dependent, but the examples are.

    The [header__]change_Mask column in the (_ct) Change Tables is relatively well documented in the Attunity Replicate Userguide, but some clarification by example may be useful.

    The semantics for this column follow the SQLserver T-SQL UPDATED_COLUMNS definitions.
    So be sure to read up on: COLUMNS_UPDATED (Transact-SQL)
    Only actual changes, that is differences between the before image and after image for a column, get the corresponding bit for that column set.

    At first it seems one can use sys.fn_IsBitSetInBitmask, and you can, but not in the way one expects.
    check out:
    Abhijit More's Blog - Have you heard about sys.fn_IsBitSetInBitmask? - Abhijit More. For the source of that function, while in SQLserver execute: exec sp_helptext fn_IsBitSetInBitmask

    We need a Little Endian style bit-test, which I defined as LE_IsBitSetInBitmask

    For this example I use a TEST table with 20+ columns, F10 ... F39, in a schema called XOE in a DB called 'Seg00'.

    The BITS that the VARBINARY refer to the change table (<table_name>_ct) as per (3.0.2) doc indicating "column ordinal in the change table".


    The column ordinal number can be retrieved with :
    Code:
    select ORDINAL_POSITION, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ...
    ORDINAL_POSITION	COLUMN_NAME
    1	header__change_seq
    :
    7	header__timestamp
    8	F10
    9	F11
    :
    36	F38
    37	F39
    Knowing the ordinal number, essentially what needs to happen is
    - divide that by 8 to get the byte,
    - substring the byte from the varbinary,
    - use MOD to find the bit in the byte,
    - raise 2 to the power of that bit (or do a table lookup), to get a mask,
    - perform a bitwise AND.
    - see the result is non-zero. SMOP! :-)

    We need a Little Endian style bit-test, which I defined as LE_IsBitSetInBitmask

    Code:
    alter function LE_IsBitSetInBitmask  
    (@input_binary varbinary(500), @bit int)  
        returns int  
    as  
    begin  
        declare @byte_in_var    int 
        declare @masked_byte    int  
        if @bit < 0 return 0    
        SELECT @byte_in_var = LEN(@input_binary) - FLOOR(@bit/8)     
        IF ( POWER(2, @bit % 8 ) & SUBSTRING( @input_binary, @byte_in_var, 1) ) > 0 RETURN 1
        return 0 
     end
    Alternatively one can hardcode the byte number, and the mask.
    But the byte number changes with the column with the highest ordinal changed, so it must be calculated.
    For example:
    Code:
    alter table XOE.TEST__ct add  f12_changed_bit as 
             CAST(CAST( SUBSTRING(header__change_mask, len(header__change_mask) - 1, 1) AS INT) & 2 AS BIT)
    Now lets play with, and report on, the __ct table.
    Note: Bits start counting at 0, columns at 1 !!

    We can translate those the column names through INFORMATION_SCHEMA.COLUMNS as per above
    (this example only uses table_name, might need schema name as param)

    To get a LIST of names we need some trickery...

    Code:
    ALTER FUNCTION [dbo].[list_changed_columns] ( @My_table_name VARCHAR(64),  @ChangeMask VARBINARY(128))  
          RETURNS NVARCHAR(255)  
          AS   
          BEGIN  
             return (select Column_Name +',' as 'data()'   
             from INFORMATION_SCHEMA.COLUMNS  
             WHERE  (TABLE_NAME = @My_table_name and   
             (dbo.LE_IsBitSetInBitmask(@ChangeMask, ORDINAL_POSITION -1 )<>0)) for xml path (''))  
          END

    On the Oracle side we make some changes and let Replicate move it over.
    (More changes were made. Just a sample below)

    Code:
    update test set f11='f12,f22,f23=6', f12=6, f22=6, f23=6 where f10=1001;
    update test set f11='f12,f32,f33=6', f12=6, f32=6, f33=6 where f10=1002;
    update test set f11='f13,f33,f34=6', f13=6, f33=6, f34=6 where f10=1003;
    update test set f11='f12,f32,f33,34=7', f12=7, f32=7, f33=7,f34=7 where f10=1001;
    update test set f11='f12,f21,f22,f23=7', f12=7, f22=7, f23=7, f21=7 where f10=1001;
    update test set f11='f12,f21,f22,f23,34=8', f12=8, f22=8, f23=8, f21=8,f34=8 where f10=1001;
    commit;
    ok... finally time to run the SELECT to see what changed!

    Code:
     SELECT TOP 1000
           [header__change_oper] as '?'
          ,[header__change_mask] as 'Mask', len([header__change_mask]) as 'len'
          ,dbo.list_changed_columns('TEST__ct', header__change_mask) as changed_columns
          ,[f12_changed_bit] as 'bit', F11
      FROM [Seg00].[XOE].[test__ct] 
      WHERE [header__timestamp] > '2013-11-30 13:35:50.000000' 
      ORDER BY [header__change_seq], [header__change_oper]
    Here is the simple text representation of the result:

    Code:
    ?	Mask	len	changed_columns	bit	F11
    B	0x	0	NULL	0	f12,f21,f22,f23,34=3
    U	0x180300	3	F11, F12, F22, F23,	1	f12,f22,f23=6
    B	0x	0	NULL	0	f12,f32,f33=1
    U	0x60000300	4	F11, F12, F32, F33,	1	f12,f32,f33=6
    B	0x	0	NULL	0	f13,f33,f34=1-2
    U	0xC0000500	4	F11, F13, F33, F34,	0	f13,f33,f34=6
    B	0x	0	NULL	0	f12,f22,f23=6
    U	0xE0000300	4	F11, F12, F32, F33, F34,	1	f12,f32,f33,34=7
    B	0x	0	NULL	0	f12,f32,f33,34=7
    U	0x1C0100	3	F11, F21, F22, F23,	0	f12,f21,f22,f23=7
    B	0x	0	NULL	0	f12,f21,f22,f23=7
    U	0x801C0300	4	F11, F12, F21, F22, F23, F34,	1	f12,f21,f22,f23,34=8

    And with data value
    Code:
      SELECT TOP 1000 
           [header__change_oper] as '?'
          ,[header__change_mask] as 'Mask'
          ,[f10],[f11],[f12],[f13],[f21],[f22],[f23],[f32],[f33],[f34]
      FROM [Seg00].[XOE].[test__ct] 
      WHERE [header__timestamp] > '2013-11-30 13:35:50.000000' 
      ORDER BY [header__change_seq], [header__change_oper]
    Result:
    Code:
    ?	Mask	f10	f11	f12	f13	f21	f22	f23	f32	f33	f34
    B	0x	1001	f12,f21,f22,f23,34=3	3	NULL	3	3	3	2	2	3
    U	0x180300	1001	f12,f22,f23=6	6	NULL	3	6	6	2	2	3
    B	0x	1002	f12,f32,f33=1	1	NULL	NULL	NULL	NULL	1	1	NULL
    U	0x60000300	1002	f12,f32,f33=6	6	NULL	NULL	NULL	NULL	6	6	NULL
    B	0x	1003	f13,f33,f34=1-2	NULL	1	NULL	NULL	NULL	NULL	1	1
    U	0xC0000500	1003	f13,f33,f34=6	NULL	6	NULL	NULL	NULL	NULL	6	6
    B	0x	1001	f12,f22,f23=6	6	NULL	3	6	6	2	2	3
    U	0xE0000300	1001	f12,f32,f33,34=7	7	NULL	3	6	6	7	7	7
    B	0x	1001	f12,f32,f33,34=7	7	NULL	3	6	6	7	7	7
    U	0x1C0100	1001	f12,f21,f22,f23=7	7	NULL	7	7	7	7	7	7
    B	0x	1001	f12,f21,f22,f23=7	7	NULL	7	7	7	7	7	7
    U	0x801C0300	1001	f12,f21,f22,f23,34=8	8	NULL	8	8	8	7	7	8
    ok?

    suggest suplementary article(s) to read: Turn varbinary bit ON/OFF - SQLServerCentral
    Google is your friend! (bing not so much).


    Enjoy,
    Hein.
    Last edited by Hein; 11-30-2013 at 10:48 AM.

  2. #2
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    151
    Rep Power
    12

    correction pending


    Changes made for main topic!


    The original code in this topic used Oracle's OE.CUSTOMER as example and seemed fine.
    It used sys.fn_IsBitSetInBitmask but that only worked for up to 8 columns.
    There appeared to be endian-ness type of problem in sys.fn_IsBitSetInBitmask when using the Replicate Change Mask.

    The complicate matters more, I had try testing the Ordinal numbers with a re-defined change table listing columns out of the original order behind the back of Replicate.
    It sort of worked, but when I changed columns 1,2 I would get 0x03 and for 1 & 3 got 0x05
    I left the header columns to be defined first, but somehow something altered and the header columns were no longer counted if you like.
    Odd, but I'll just stop doing that!


    fwiw, if one wanted to to get those byte offsets, bit numbers, masks statically one can query REPCTL:

    Code:
    # repctl gettabledef database_name=Seg00_Target table_owner=XOE table_name=TEST__ct | perl -ne "$n=$1 if
    /name.:\s.(\w+)/; if(/null/){printf qq(%d\t\%d\t%d\t%d\t%s\n),$i,int($i/8),$i%8,2**($i%8),$n; $i++}"
    Ordinal Offset  Bit     Mask    Column_name
    ------- ------  ---     ----    ------------------
    0       0       0       1       header__change_seq
    1       0       1       2       header__change_oper
    2       0       2       4       header__change_mask
    3       0       3       8       header__stream_position
    4       0       4       16      header__operation
    5       0       5       32      header__transaction_id
    6       0       6       64      header__timestamp
    7       0       7       128     F10
    8       1       0       1       F11
    9       1       1       2       F12
    10      1       2       4       F13
    11      1       3       8       F14
    12      1       4       16      F15
    13      1       5       32      F16
    14      1       6       64      F17
    :
    Hein.
    Last edited by Hein; 11-30-2013 at 12:47 PM.

  3. #3
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    151
    Rep Power
    12

    indicate where any column in a list of columns names has changed

    Talking to myself....


    The next usage question of the change_mask is perhaps : "Just tell me if any of the columns in this list has changed"
    For example because certain columns require an (audit) action when changed.


    Here is a function which will do just that.
    The core is the AND of a byte from the change mask and a mask,
    Now it is tried for every column in a list.

    For a single column that's straightforward as per prior examples.
    The trickiest part of the this function now was to construct a SELECT ... WHERE ... IN ... @variable
    Google found this example : How to use multiple values for IN clause using same parameter (SQL Server) | SQL Server Portal
    Here is what I constructed around that:

    Code:
    ALTER FUNCTION [dbo].[changed_columns](
    		@My_table_name		VARCHAR(64), 
    		@column_list		VARCHAR(256), 
    		@input_binary		VARBINARY(128))  
        RETURNS BIT
          AS   
          BEGIN
            DECLARE @ordinal_position	INT
            DECLARE @byte_in_var		INT
    		DECLARE @masked_byte		INT
    		DECLARE	@bit				INT
    		DECLARE @changed			BIT
    		DECLARE @xml AS XML
    		SET @changed = 0
            SET @xml = CAST( ('<A>'+REPLACE(@column_list, ',', '</A><A>')+'</A>') AS XML)
            DECLARE columns_cursor CURSOR FOR
    			SELECT ORDINAL_POSITION 
    			FROM INFORMATION_SCHEMA.COLUMNS  
    			WHERE  TABLE_NAME = @My_table_name AND COLUMN_NAME IN
    			 ( SELECT X.value('.','VARCHAR(256)') as [Column] FROM @xml.nodes('A') AS FN(X) )			 
    		OPEN columns_cursor 
    
    
            FETCH NEXT FROM columns_cursor INTO @bit
    		WHILE @@FETCH_STATUS = 0 AND @changed = 0
    		BEGIN
    			SET @bit = @bit - 1   
    			SET @byte_in_var = LEN(@input_binary) - FLOOR(@bit/8)     
    			SET @changed = CAST( (POWER(2, @bit % 8 ) & SUBSTRING( @input_binary, @byte_in_var, 1)) AS BIT)
    			FETCH NEXT FROM columns_cursor INTO @bit
    		END 
    		CLOSE columns_cursor
    		DEALLOCATE columns_cursor
    		RETURN @changed
    	END
    
    
    -- tester
    select [dbo].[changed_columns]( 'TEST__ct', 'F11,F12,F32', 0x080030)

    and in actual usage based on example in prior replies, looking for columns F23 or F34 being changed...

    Code:
    select [header__change_mask] as 'Mask'
      ,[dbo].[changed_columns]( 'TEST__ct', 'F23,F34', [header__change_mask]) as 'Changed'
      , F11 FROM [Seg00].[XOE].[TEST__ct] 
      where [header__timestamp] > '2013-11-30 13:35:50.000000' 
      order by [header__change_seq], [header__change_oper]
    results in:

    Code:
    Mask	Changed	F11
    0x	0	f12,f21,f22,f23,34=3
    0x180300	1	f12,f22,f23=6
    0x	0	f12,f32,f33=1
    0x60000300	0	f12,f32,f33=6
    0x	0	f13,f33,f34=1-2
    0xC0000500	1	f13,f33,f34=6
    0x	0	f12,f22,f23=6
    0xE0000300	1	f12,f32,f33,34=7
    0x	0	f12,f32,f33,34=7
    0x1C0100	1	f12,f21,f22,f23=7
    0x	0	f12,f21,f22,f23=7
    0x801C0300	1	f12,f21,f22,f23,34=8

    Hein.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •