Load Ingest Data
Results 1 to 1 of 1

Thread: interpreting header__change_mask for change-tables-store in Oracle

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

    interpreting header__change_mask for change-tables-store in Oracle

    This topic is a companion to the SQL server examples in :
    http://www.attunity.com/forums/sql-s...tore-2924.html


    This subject is not really Oracle (target) dependent, but the examples here are.
    The example are a first working attempt. Performance was not a consideration for now.
    I'm sure the code can be improved upon (Please put suggestion in a reply or Email!) , but it should prove a fine start.

    With that out of the way, Here goes nothing....

    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.

    The tricky parts in using Oracle to interpret this RAW bit mask are
    1) There is no simple CAST from RAW to INTEGER
    2) Oracle converts RAW quickly to HEX, and really deals in 4-bit NIBBLES, not the more common bytes
    2) The nearest existing funtion UTL_RAW.CAST_TO_BINARY_INTEGER is not labeled 'DETERMINISTIC' although it surely is, and this prevents it from being directly used in (Oracle 11G) COMPUTED BY / VIRTUAL columns.

    For this example I use a TEST table with 20+ columns, F10 ... F39, replicated to ABC in a schema called XOE.

    The BITS that the RAW 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 COLUMN_ID, COLUMN_NAME,  from user_tab_cols WHERE TABLE_NAME = 'ABC__ct' order by COLUMN_ID;ORDINAL_POSITION    COLUMN_NAME
    1    header__change_seq
    :
    7    header__timestamp
    8    F10
    9    F11
    :
    36    F38
    37    F39

    Once we know the ordinal number essentially what needs to happen is
    - divide that by 4 to get the nibble
    - substring the nibble from the raw
    - use MOD to find the bit in the nibble,
    - 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 bit-test for a RAW, which I defined as test_bit_in_raw


    Code:
    create or replace function  test_bit_in_raw ( raw_data RAW, the_bit NUMBER)
    return NUMBER deterministic is
    -- Can not use UTL_RAW.CAST_TO_BINARY_INTEGER directly because it no labeled DETERMINISTIC even though it is.
    -- This is needed for computed by field... even when not used in an index.
      the_nibble NUMBER;  
      the_mask NUMBER;
    begin 
    -- Tried SUBSTRB for a BYTE, but the LENGTH is in nibbles anyway....
      the_nibble := UTL_RAW.CAST_TO_BINARY_INTEGER ( SUBSTR (raw_data, length(raw_data) - floor(the_bit/4), 1));
      the_mask := POWER ( 2, MOD ( the_bit, 4));
      
      if BITAND ( the_nibble, the_mask) > 0
      then return 1;
      else return 0;
      end if;
    end test_bit_in_raw ;
    /
    show error function test_bit_in_raw


    One can NOT hardcode the byte number, and the mask.
    Code:
    alter table "ABC__ct" add f12_hardcoded integer as ( DECODE( BITAND ( UTL_RAW.CAST_TO_BINARY_INTEGER ( SUBSTR (
      "header__change_mask", LENGTH("header__change_mask") - 3, 1)), 2), 2, 'yes', 'no'));
    Results in:
    Code:
    SQL Error: ORA-30553: The function is not deterministic



    Instead of 'hardcoding' the bit number corresponding with a column, we can dynamically translate those the column names through USER_TAB_COLS as per above
    The Function for that could be:

    Code:
    create or replace function  changed_column ( raw_data RAW, the_table_name VARCHAR, the_column_name VARCHAR)
    return NUMBER deterministic is
    -- Can not use UTL_RAW.CAST_TO_BINARY_INTEGER directly because it no labeled DETERMINISTIC even though it is.
    -- This is needed for computed by field... even when not used in an index.
      the_bit  NUMBER;
      the_nibble NUMBER;  
      the_mask NUMBER;
    begin 
    -- Tried SUBSTRB for a BYTE, but the LENGTH is in nibbles anyway....
      SELECT COLUMN_ID INTO the_bit from user_tab_cols WHERE TABLE_NAME = the_table_name AND COLUMN_NAME = the_column_name;
      the_bit := the_bit - 1;
      the_nibble := UTL_RAW.CAST_TO_BINARY_INTEGER ( SUBSTR (raw_data, length(raw_data) - floor(the_bit/4), 1));
      the_mask := POWER ( 2, MOD ( the_bit, 4));
      
      if BITAND ( the_nibble, the_mask) > 0
      then return 1;
      else return 0;
      end if;
    end changed_column ;
    /
    show error function changed_column

    This example only uses table_name because it used USER_TAB_COLS, not ALL_TAB_COLS which would need an OWNER param


    On the Oracle Source 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;

    On the Oracle TARGET side I added a virtual column for demonstruction purposes:

    Code:
    alter table "ABC__ct" add f12_altered integer as (test_bit_in_raw ( "header__change_mask", 9));

    ok... finally time to run the SELECT to see what changed!


    Code:
     SELECT to_char("header__timestamp",'hh24:mi:ss') "Time"
          ,"header__change_oper" "?", f12_altered
          ,test_bit_in_raw ( "header__change_mask", 18) "F21 altered"
          ,changed_column ( "header__change_mask", 'ABC__ct', 'F34' ) "F34 Changed"
          ,"header__change_mask" "Mask", length("header__change_mask") "len"
    --      ,dbo.list_changed_columns('TEST__ct', "header__change_mask") as changed_columns
    --     ,[f12_changed_bit] as 'bit'
          ,f10,f11,f12,f13,f21,f22,f23,f32,f33,f34,f35
      FROM "ABC__ct"
      WHERE "header__timestamp" > '30-NOV-2013' 
      ORDER BY "header__change_seq", "header__change_oper"

    Here is the simple text representation of the result:


    Code:
    Time     ? F12_AL F21 a  F34 C   Mask       len  F10   F11                   F12  F13  F21     
    17:07:58 B 0      0      0                       1003  f13,f33,f34=1-1            1            
    17:07:58 U 0      0      0       0100       4    1003  f13,f33,f34=1-2            1            
    17:07:58 B 0      0      0                       1001  f12,f22,f23=1         1         8       
    17:07:58 U 1      0      1       E0000300   8    1001  f12,f32,f33,34=2      2         8       
    17:07:58 B 0      0      0                       1001  f12,f32,f33,34=2      2         8       
    17:07:58 U 0      1      0       1C0100     6    1001  f12,f21,f22,f23=2     2         2       
    17:07:58 B 0      0      0                       1001  f12,f21,f22,f23=2     2         2       
    17:07:58 U 1      1      1       801C0300   8    1001  f12,f21,f22,f23,34=3  3         3



    ok?


    Enjoy,
    Hein.
    Last edited by Hein; 12-02-2013 at 03:54 AM.

Posting Permissions

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