Field Information From Tracking Table

[Progress_Learner]

Hello Team, hope you guys are doing well !!!

I want to fetch field level information from _cdc-tracking-table that which field is being changed by using _Field-Map field.

I tried writing code for this but getting useful information.

Please help me here if you have some idea regarding this.

Regards

 

[Rob Fitzpatrick]

We might be able to offer more constructive help if you posted a code sample of what you have attempted so far.

 

[TomBascom]

You might find slides 7 & 8 of the CDC talk interesting: https://www.pugchallenge.com.au/#database-track

 

[Progress_Learner]

Thanks for your reply @Rob Fitzpatrick and @TomBascom and sorry for delay in my response.

I want to know the field name from _cdc-change-tracking table when field of any database table changes. _cdc-change-tracking table has one field _change-fieldmap (datatype is RAW) that stores field name in RAW format. I want to know field name (in Char) from this field.

Steps:-

1. I have updated Name from customer table so it has created corresponding record in _cdc-change-tracking table.

FIND FIRST customer EXCLUSIVE-LOCK.
UPDATE NAME.

2. As I mentioned, _change-fieldmap is RAW type so I cannot display that field. I exported whole record of _cdc-change-tracking that was created after I updated customer name.

OUTPUT TO "cdc-change-tracking.txt".
FOR EACH _cdc-change-tracking NO-LOCK:
    EXPORT _cdc-change-tracking.     
END.
OUTPUT CLOSE.

cdc-change-tracking.txt Data:

"tSoGR55SqaRuFARMCPjOvQ" 38277 2020-10-20T23:43:20.710-07:00 3361 4 "020001EA==" 2 0 0 0 ? ? ?

"020001EA==" is _change-fieldmap field and RAW conversion of field "Name" of Customer Table.

3. Now, I wanted to convert RAW type to CHAR by using GET-STRING so that "020001EA==" can be displayed as "NAME".

DEF VAR ch1 AS CHAR NO-UNDO.
DEF VAR rw1 AS RAW  NO-UNDO.

FOR EACH _cdc-change-tracking NO-LOCK:
    rw1 = _cdc-change-tracking._change-fieldmap.
    ch1 = GET-STRING(rw1, 1 , LENGTH(rw1)).
    MESSAGE ch1 
        VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
END.

In above code, ch1 is display as blank.

​

Please suggest how to display this RAW type field (_cdc-change-tracking._change-fieldmap) so that we can understand
which field is updated from _cdc-change-tracking table.

Regards.

 

[TomBascom]

You didn’t read the PPT.

 

[TomBascom]

Slide 7:

• _Change-fieldmap is a raw bitmap.
• System fields (“_” prefix) are not part of the bitmap.
• Bits in _Change-fieldmap correspond to _field-rpos:
  • EXCEPT that they are off by 8!​
  • Conveniently, the System fields start at 2 and there are 7 of them.​
  • So bit #1 maps to _field-rpos 9.​
  • byte# + bit# = _field-rpos ☺​

Slide 8:

/* convert a raw bitmap into a string for logging / debugging purposes
*/

function bitmap2string returns character ( bitMap as raw ):
  define variable stringBM as character no-undo.
  define variable i        as integer   no-undo.
  define variable j        as integer   no-undo.
  do i = 1 to length( bitMap ):                                     // byte by byte
    do j = 1 to 8:                                                  // bit by bit
      stringBM = stringBM + string( get-bits( get-byte( bitMap, i ), j, 1 ), "9" ).   // "0" or "1"
    end.
    stringBM = stringBM + " ".               // separate bytes with a space for easier reading
  end.
  return trim( stringBM ).
end.

You might also like to consider downloading and carefully reading: Introduction to Change Data Capture

 

[Progress_Learner]

Thanks a lot @TomBascom For this valuable information. I will go through complete ppt so that I can get more information on that.