This use case is for an application looking for changes past a particular threshold value or the application could be propagating only a single column to some other data target. This Use Case also shows how the sql CDC function, CDC_is_column_changed(), can be used to understand and process change data.

In this case, the changed values are searched for, and possibly found, in 2 steps. Step 1 is required to get the source file table number so that the Change Tracking table can be searched efficiently. Step 2, then gets any column value changes of interest.

For example, the column name is “my_searched_col” and that the fields being identified are ID_f1, ID_f2, ID_f3.

Step 1 is as follows:

select f."_File-Number"
from   pub.”_File” f
inner join 
       pub."_CDC-Table-Policy" tp
	  on  f.rowid = tp.”_Source-File-Recid”
	  and tp."_Policy-Instance"  = 0      -- Policy is current.
where f."_file-name" = ‘customer’

The query for step 2 uses the sql CDC function CDC_is_column_changed() to get a list of the changes made to column “my_searched_col”. The _Operation value from the Change table is important, also, to interpret the state of the column after the operation. INSERT, UPDATE, and DELETE leave different values in existence or non-existence in the source table. The query for step 2 is as follows:

select my_searched_col, _Operation
       ID_f1, ID_f2, ID_f3
from   pub."_Cdc-Change-Tracking" ct
inner join
       pub.CDC_Customer c               -- well-known Change table name.
          on ct."_Change-Sequence" = c."_Change-Sequence"
where  ct."_ Source-Table-Number"    = ?          -- from 1st query.
and    c."_Operation"    != 3  -- optional - exclude Before Update images.
and    ((c."_Operation" = 4 and
        is_column_changed(pub.CDC_Customer,my_searched_col,_Change-FieldMap) = 1)
        or
        c."_Operation" in (1, 2) )

order by ct."_ Source-Table-Number", ct."_Change-Sequence";
-- Order By keys map to index _Sequence-Id
Note:
  • Delete shows the value of the change tracked field in the row being deleted.
  • An INSERT operation always has Change data for every change-tracked field, and for Identifying Fields.
  • A DELETE operation has Change data for tracked fields, and has Identifying Fields data values.

With the data that is selected, the ETL application can do the intended Change processing, such as analyzing the distribution of values over a particular threshold value. The application logic may look like the following example:

do
      query #2            -- this is query above, in Step #2.
      exitloop if no data
      Let RS = result set from query #1
      Let stmt = 
      switch _Operation 
          case Create – Build Insert stmt for fields 1..12, 
                            plus Identifying fields, 
                            with values from parameters
                        Set parameter values for Insert from RS data
          case Delete – Build Delete stmt with 
                            Identifying fields = parameters
                        Set parameter values from RS ID_f* fields
          case Update – Build partial Update for DW table
                 for each field f in gcc:          -- changed columns
                     Build set f = parameter, remember parameter
                 Build Where clause for ID_F* fields = parameters
                 Set parameter values for “set” from RS data
                 Set parameter values from RS ID_f* fields
      end switch


      exec sql Prepare of generate stmt
      -- set all parameters for generated stmt
      switch _Operation 
          case Create – Set parameter values for Insert from RS data
          case Delete – Set parameter values from RS ID_f* fields
          case Update – Set parameter values for “set” from RS data
                        Set parameter values from RS ID_f* fields
      end switch


      -- Now execute prepared stmt to send changes to DW
      execute generated stmt

enddo