4 Replies Latest reply on Mar 14, 2019 11:18 AM by Prabhuram Jegatheesh

    SQL PWX not taking any data

    Prabhuram Jegatheesh New Member

      Hi Team,


      We have recently done the partition in the SQL DB and after that We have started the realtime job which fetching the data from that DB. It's failed due to Replication not availble.


      DB team added the replication and the realtime jobs are running without any issues but not fetching any data from that DB.


      Could you please suggest any solution?

        • 1. Re: SQL PWX not taking any data
          dan.smith@informatica.com Guru

          When you create a PWX SQL Server Capture Registration, the thing that links the PWX Capture Registration to the SQL Server Article is the object Id of the Article.

          If anything changes that, then data in DistDB won't be related to the Article object ID that PWX is looking for.

          That breaks CDC.


          If something "turned off" replication, then that probably happened.


          To check whether or not there are rows in DistDB for PWX related Articles,  you can use these queries:


          Run these queries against the Distribution Database in SQL Server.

          DistDB name may be "distribution", but the DistDB name can vary.



          -- Query to get Publication, Article IDs


          SELECT a.article_id, b.id, a.publication_id, c.srvname

                 ,a.publisher_db, a.article,a.source_owner           


          FROM  dbo.MSarticles a

          JOIN  dbo.MSpublisher_databases b on                      

                 (a.publisher_id=b.publisher_id and                  


          JOIN  master.dbo.sysservers c on


          ORDER BY a.article_id desc, b.id desc, a.publication_id



          That query will give you a list of article IDs, from that you'll take the PWX article ID and use it in the following query:



          -- Query to select rows for an Article ID


          SELECT publisher_database_id, xact_seqno, type,

                 article_id, command_id,partial_command,

                 cast(command as nchar(100))

          FROM  dbo.MSrepl_commands

          WHERE article_id = <article id>



          1) The article should match the tag name in the PowerExchange capture registration.

          2) If there are no rows in the DistDB, then there is no data for PWX to retrieve


          You can use DTLUCBRG to rebuild the connection between the article(s) and the capture registration(s):


          HOW TO: Run the PowerExchange DTLUCBRG utility using the MSSOPTS UPDATESTATUS parameter in PowerExchange


          1 of 1 people found this helpful
          • 2. Re: SQL PWX not taking any data
            Prabhuram Jegatheesh New Member

            Hi Dan,


            Thanks for the post. We have tried the steps you suggested and We could see 0 records in the MSrepl_commands so We have tried to create a DTLUCBRG txt file and tried to run from command prompt


            Below is the file we created:


















            But it's throwing an error




            PWX-09082 API function <DESCRIBE> returned non-zero return code <264>, DBCB RCs <264/9088/0>.

            PWX-00264 DBAPI Error. DB_DESCRIBE failed for file V:\SOURCE\DTLCRTSTU\DEBUG\glledger.MSS.DWCR000.1.cpr.

            PWX-01252 DBNTC Initial "Describe" CONVERSE failed to location "gblwbidbp02.*******.corp", rcs 260/9088/0.

            PWX-09088 Cannot add record, record sequence <1>, does not match file <0>.


            Could you please suggest ,how we can move forward?


            Below is the entry taken from log file:


            2019-03-05 04:35:26 DTLUCBRG REGISTRATION REPORT (TESTRUN=N)



            CONDTYPE     = <None>

            CRGNAME      = <glledger>

            DBTYPE       = <MSS>

            INSTANCE     = <>

            LOCATION     = <gblwbidbp02.******.corp>

            LOCATION_CRG = <gblwbidbp02.******.corp>

            LOCATION_DM  = <gblwbidbp02.******.corp>

            LOCATION_XDM = <gblwbidbp02.******.corp>

            OUTPUT       = <G:\Informatica\PWX91\MSSQL\logs\dtlucbrg.log>

            REPLACE      = <N>

            REPLACEACTIVE= <N>

            REUSECRGNAME = <N>

            RPTCOLS      = <Y>

            STATUS       = <I>

            TABLE        = <dbo.JDEGAAccountLedger>

            NOTIFYCHANGES = <N>



            IMSOPTS: Not relevant for this run

            ORAOPTS: Not relevant for this run




            Dbserver = <gblwbidbp02.******.corp>

            Dbname   = <DWCR>



            ADAOPTS: Not relevant for this run




            Summary of registrations created in DWCR000 with status INACTIVE and

            condense type NONE

            No of registrations created = 0

            No of registrations updated = 0

            No of existing registrations not matching update parameters = 0



            2019-03-05 04:35:33 END OF DTLUCBRG REGISTRATION REPORT



            Please let us know if you need any information.



            Advance Thanks.

            • 3. Re: SQL PWX not taking any data
              dan.smith@informatica.com Guru

              If you want to be able to use PWXCCL, then you need CONDTYPE=PART.

              If you want DTLUCBRG to replace existing registrations, then you need REPLACE=Y and REPLACEACTIVE=Y.

              If you want to maintain the same registration name, then you need REUSECRGNAME=Y.


              Please note that for SQL Server source, the login that PWX uses requires dbo to create capture registrations, and sa to create capture registration groups.

              • 4. Re: SQL PWX not taking any data
                Prabhuram Jegatheesh New Member

                Hi Dan,


                Thanks for all your help,


                We are able to resolve the issue by recreating the Capture registration using the old CAPTURE files.