Change capture

[seanc217]

I have a table where the key value has increased from 2 to 4. When I run the job it does not update this change and you cannot add it to the values property in the change capture stage, it errors and say it cannot be both a key and a value. How do people here handle this kind of change?

TIA.

Sean

[ray.wurlod]

2 to 4 as a value? That shouldn't be a problem, irrespective of the data type of the key column, unless there's a CHECK constraint on the table.

2 to 4 digits/characters? Then, yes, your metadata have changed out there in the database. Your DataStage jobs' metadata will need to be changed to correspond.

Best practice is to re-import the table definition, re-load that column from the imported table definition. In this way the linkage between the table definition in the Repository and the jobs that use that table definition is preserved, which means that you can accurately perform Usage Analysis on those table definitions (not to mention Lineage and other analyses in MetaStage).

[seanc217]

Yes,

my column's metadata changed from varchar2(2) to varchar2(4).

What about if I am trying to maintain a type 2 SCD?
I would want to expire the current records and insert the new ones.

With the change capture stage this would not happen because the column that changed is part of the change key.

The only way I could figure to handle this is to expire all the records by issuing an update and then re-loading.

Any insight is appreciated.

[ray.wurlod]

Have you effected the metadata change in all stages that refer to it?

[seanc217]

Yes. I changed the metadata in the columns page to reflect the change. However when I run the job, it does not recognize the change since the change that was make is actually a key in the change capture stage.

[samsuf2002]

set the key value and also set the change mode option in change capture as Explicit keys,All values and try hope this will help u