3 Replies Latest reply on Jan 11, 2022 9:14 AM by dan.smith@informatica.com

    PowerExchange ingored fields in Capture Registration. Still seeing updates in log.

    Burgans Active Member

      There is a field in a Oracle 19c table we do not want to capture changes on so I did not include that field in the capture registration of the table, however when I run an SQL statement that only updates that one field in the table, I am still seeing updates being captured in the Condense file.  The purpose of not including it was to avoid unnecessary replication of a field the business doesn't need.  The table has around 200,000 records and that field is updated at least once for every record during our nightly batch.  Why would an unselecting field in the capture registration still get picked up by the logger?  Is there a way to avoid capturing updates when an update occurs to only that selected field?

        • 1. Re: PowerExchange ingored fields in Capture Registration. Still seeing updates in log.
          dan.smith@informatica.com Guru

          You have mostly correctly described how PWX CDC works.

           

          Please note that there are no such things as "unselected rows".

          Oracle writes REDO for each insert, update, or delete.

          PWX CDC captures any of those row-images for any tables that are registered for capture.

          It won't capture any columns that aren't registered for capture - but it will capture the other columns.

           

          No, there is no way to change that behaviour.

           

          If you are using PowerCenter to consume changes from PWX Listener, and you turn on CI (Change Indicators) for all the columns, you could filter out the rows where none of the CI were set.

          That isn't a "perfect" solution, in that those rows still get captured, condensed, read by PWX Listener, and sent to the PC reader before getting filtered out.

          However, it is the best available solution.

          • 2. Re: PowerExchange ingored fields in Capture Registration. Still seeing updates in log.
            Burgans Active Member

            Okay. Just so I follow:

             

            In TABLE1 I do not have FIELD1 selected in the Navigator capture registration.

             

            If I run UPDATE TABLE1 SET FIELD1 = '0'; COMMIT;

             

            Then the change to the unselected field in the the CR is still captured in the condense file even though that is the only field updated and it is up to PC to ignore the field and not PWX?

             

            I can understand if other fields that are included are in the CR are updated are captured since we'd need to replicate those, but I was under the assumption that if no fields selected in the CR where updated then the update would be ignored and a condense record not created.

             

            Thanks again for your help.

            • 3. Re: PowerExchange ingored fields in Capture Registration. Still seeing updates in log.
              dan.smith@informatica.com Guru

              Not exactly.  The change to that field/column is not captured.

               

              Oracle writes REDO for that UPDATE.

              Because of the Supplemental Log Group, Oracle writes all the registered columns as part of that.

               

              PWX Capture reads that REDO, and captures all the columns that are registered for capture.

               

              Clarification:

              PWX Capture doesn't know whether any individual column was changed - only that there was REDO for an UPDATE on a table that is registered for capture;  it captures that UPDATE, and all the columns that are registered for capture.

              It does not capture any column that isn't registered for capture, because that column isn't registered for capture.

               

              If you turn on CI in the Extraction Map, then the PWX Listener reader process compares the Before and After images from the UPDATE to determine whether or not to turn on CI for each column, before it sends the data downstream (to the PC CDC session, or IICS CDC session).

               

              Yes, if you want to filter such rows out, then you would have to do that in the PowerCenter CDC session (or IICS CDC session, if IICS is the consumer, rather than PC).