-
1. Re: SQL PWX not taking any data
dan.smith@informatica.com Feb 28, 2019 9:12 AM (in response to Prabhuram Jegatheesh)1 of 1 people found this helpfulWhen 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
-
2. Re: SQL PWX not taking any data
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 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 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.