4 Replies Latest reply on Oct 12, 2018 2:21 PM by dan.smith@informatica.com

    CDC for MSSQL in only few columns of my table

    Informatica GNS Active Member

      Hello,

       

      I have an MSSQL table with over 200 records. I will like cdc to only give me the changes on 13 columns out of the 200. is this possible?

       

      Regards,

       

      Vanessa

        • 1. Re: CDC for MSSQL in only few columns of my table
          Nico Heinze Guru

          Please move this thread to the PowerExchange Adapters forum, this is the right place for such a question.

           

          Regards,

          Nico

          • 2. Re: CDC for MSSQL in only few columns of my table
            dan.smith@informatica.com Guru

            Moved.

             

            > I have an MSSQL table with over 200 records.

             

            Do you mean with over 200 columns?

             

            When you register a table for capture, you can select which columns you want to capture.

            • 3. Re: CDC for MSSQL in only few columns of my table
              Informatica GNS Active Member

              Yes, with over 200 columns.

              We have selected all the columns where we want cdc capture changes. But in our workflow, we will like to have the columns (where we want cdc capture changes) plus the ones where we dont have cdc data capture selected to be returned.

              Who can we achieve this?

              • 4. Re: CDC for MSSQL in only few columns of my table
                dan.smith@informatica.com Guru

                If you want to see all the columns in the change stream from PWX CDC, then you need to include them all in the capture registration and in the extraction map.

                 

                If you only want to see rows where specific columns changed, then add change indicators for those columns, and in the SQ, use a filter to only include rows where at least one of those change indicators is set.

                 

                To add change indicators, open the extraction map in Navigator, and right click in the white space.

                That opens a floating menu, and one of he options allows you to add Change Indicators.

                Add one for each column for which you want to see changes.

                That will result in an additional field for each such column, named like DTL__CI_columnname.

                Then in PC Designer, edit the mapping, and add a filter on the SQ.

                In that filter, include only rows where at least one of those DTL__CI_columnname indicators is set.

                 

                For more information on Change Indicators, refer to the PWX Navigator Users Guide.

                 

                Note that CI are not enabled by default, so this means that you can't just do CREATEXMAPS for a DTLURDMO migration to a different environment / PWX Listener.

                Instead you will need to do both a normal REG_COPY (without CREATEXMAPS), and then an XM_COPY (XM_COPY migrates the extraction map, where REG_COPY + CREATEXMAPS just creates a default one with no CI/BI type stuff).

                Yes, that means two DTLURDMO runs.

                You should be able to find example control cards in KB to do XM_COPY, or raise a case with support.