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

    SQL PWX not taking any data

    Prabhuram Jegatheesh New Member
    Prabhuram Jegatheesh Feb 28, 2019 9:05 AM

      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?

      • 247 Views
      • Tags:
        • 1. Re: SQL PWX not taking any data
          dan.smith@informatica.com Guru
          dan.smith@informatica.com Feb 28, 2019 9:12 AM (in response to Prabhuram Jegatheesh)

          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           

                 ,a.source_object

          FROM  dbo.MSarticles a

          JOIN  dbo.MSpublisher_databases b on                      

                 (a.publisher_id=b.publisher_id and                  

                  a.publisher_db=b.publisher_db)

          JOIN  master.dbo.sysservers c on

                 (c.srvid=a.publisher_id) 

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

                 DESC

           

          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>

           

          NOTE

          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

          https://kb.informatica.com/howto/6/Pages/17/297011.aspx

          1 of 1 people found this helpful
          • 2. Re: SQL PWX not taking any data
            Prabhuram Jegatheesh New Member
            Prabhuram Jegatheesh Mar 4, 2019 8:45 PM (in response to dan.smith@informatica.com)

            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:

             

            DBTYPE=MSS

            CONDTYPE=NONE

            CRGNAME=glledger

            UID=******

            PWD=*******

            LOCATION=gblwbidbp02.******.corp

            MSSOPTS=(DBSERVER=gblwbidbp02.*******.corp,DBNAME=DWCR,UPDATESTATUS=Y)

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

            REPLACE=N

            REPLACEACTIVE=N

            REUSECRGNAME=N

            RPTCOLS=Y

            STATUS=I

            TABLE=dbo.JDEGAAccountLedger

            TESTRUN=N

             

            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

             

             

            MSSOPTS:

            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
              dan.smith@informatica.com Mar 5, 2019 7:04 AM (in response to Prabhuram Jegatheesh)

              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
                Prabhuram Jegatheesh Mar 14, 2019 11:18 AM (in response to Prabhuram Jegatheesh)

                Hi Dan,

                 

                Thanks for all your help,

                 

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