Try OpenEdge Now
skip to main content
SQL Development
Change Data Capture : Use cases : Looking for changes to a particular column in a source table
 

Looking for changes to a particular column in a source table

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
*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