1 2 Previous Next 15 Replies Latest reply on Jan 17, 2019 10:29 AM by dan.smith@informatica.com Branched from an earlier discussion. Branched to a new discussion.

    PWXCCL for PWX SQL Server CDC

    vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

      Hello Dan,

       

      we have run into issues with starting up the Logger. Here is our development env set up just in case if you forgot. Multiple publication dbs replicating to single distribution db. All publication dbs and distribution db exist on the same server. One set of Listener, Logger, config files running on Informatica Power Center machine. Please help.

       

      Here is the error:

      ------------------------

      PWX-33260 Loading registrations from location "LAWSONPWXLST"

      PWX-33304 Connection: 127.0.0.1:54210, Type: CPR, Task ID: 0, Operation timeout:

      ( - ) secs, Heartbeat interval: 30 secs, Session:  ( - )

      PWX-33304 Connection: 127.0.0.1:54232, Type: CPR, Task ID: 0, Operation timeout:

      ( - ) secs, Heartbeat interval: 30 secs, Session:  ( - )

      PWX-33263 0 registrations loaded

      PWX-06115 No active Capture Registrations for processing

       

       

      Here is the dbmover.cfg file:

      ---------------------------------------

      LISTENER=(LAWSONPWXLST,TCPIP,2480)
      NODE=(local,TCPIP,127.0.0.1,2480)
      NODE=(LAWSONPWXLST,TCPIP,127.0.0.1,2480)
      NODE=(default,TCPIP,x,2480)
      /* SVC & CMD node for Listener (dtllst)
      SVCNODE=(LAWSONPWXLST,12480)
      CMDNODE=(LAWSONPWXLST,LISTENER,127.0.0.1,12480)
      /* SVC & CMD node for Logger (pwxccl)
      SVCNODE=(LAWSONPWXLOG,22480)
      CMDNODE=(LAWSONPWXLOG,CONDENSE,127.0.0.1,22480)
      APPBUFSIZE=256000
      COLON=:
      COMPRESS=Y
      CONSOLE_TRACE=N
      DECPOINT=.
      DEFAULTCHAR=*
      DEFAULTDATE=19800101
      MAXTASKS=5
      MSGPREFIX=PWX
      NEGSIGN=-
      PIPE=|
      POLLTIME=1000

      CAPT_PATH=G:\Shares\Working\PWX_Lawson\CondenseFiles 
      CAPT_XTRA=G:\Shares\Working\PWX_Lawson\ExtractionMaps

      LOGPATH=G:\Shares\Working\PWX_Lawson\Logs
      TRACING=(PFX=ALT,FLUSH=10,FILENUM=99,VIEW=Y,APPEND=Y,SIZE=100,RECLEN=255)


      CAPI_CONN_NAME=CAPXLAWS
      CAPI_CONNECTION=(NAME=MSQLLAWS,
      TYPE=(MSQL,DISTSRV=server,
      DISTDB=distribution,
      MULTIPUB=Y,
      EOF=N,RSTRADV=15,UIDFMT=DBNAME))
      CAPI_CONNECTION=(NAME=CAPXLAWS,TYPE=(CAPX,DFLTINST=LAWSON,FILEWAIT=60,RSTRADV=15))

       

       

      Here is the pwxccl.cfg file:

      ---------------------------------------

      CONDENSENAME=LAWSONPWXLOG

      DB_TYPE=MSS

      DBID=LAWSON

      DISTDB=distribution

      DISTSRV=server

      CAPTURE_NODE=LAWSONPWXLST

      CAPTURE_NODE_UID=XXXX

      CAPTURE_NODE_EPWD=PPPP

      CONN_OVR=MSQLLAWS

      EXT_CAPT_MASK=G:\Shares\Working\PWX_Lawson\CondenseFiles\CondenseS

      COND_CDCT_RET_P=30

      COLL_END_LOG=0

      NO_DATA_WAIT=0

      NO_DATA_WAIT2=10

      FILE_FLUSH_VAL=60

      FILE_SWITCH_CRIT=M

      FILE_SWITCH_VAL=30

      CAPT_IMAGE=BA

      SEQUENCE_TOKEN=00

      RESTART_TOKEN=00

      LOGGER_DELETES_EXPIRED_CDCT_RECORDS=Y

       

       

      Navigator information:

      -------------------------------

      Registrations are active

      Location is LAWSONPWXLST

      Condense is set to part

      capture type is MSSQL

       

       

      Here is more information about the error.

       

      PWX-21605 Connection selected MSQLLAWS found from covr< MSQLLAWS> tag< > type< M
      SS> int< FALSE> method< CONN_OVR>.

      PWX-25802 Process pwxccl pid 10612 locked file G:\Shares\Working\PWX_Lawson\Cond
      enseFiles\CDCT_instance_LAWSON_lockfile.lck
      PWX-25802 Process pwxccl pid 10612 locked file G:\Shares\Working\PWX_Lawson\Cond
      enseFiles\CondenseS_loggerfiles_lockfile.lck

      PWX-33260 Loading registrations from location "LAWSONPWXLST"
      PWX-33304 Connection: 127.0.0.1:62794, Type: CPR, Task ID: 0, Operation timeout:
      ( - ) secs, Heartbeat interval: 30 secs, Session:  ( - )
      PWX-33304 Connection: 127.0.0.1:62795, Type: CPR, Task ID: 0, Operation timeout:
      ( - ) secs, Heartbeat interval: 30 secs, Session:  ( - )
      PWX-33263 0 registrations loaded
      PWX-06115 No active Capture Registrations for processing
      PWX-25803 Process pwxccl pid 10612 unlocked file G:\Shares\Working\PWX_Lawson\Co
      ndenseFiles\CondenseS_loggerfiles_lockfile.lck
      PWX-25803 Process pwxccl pid 10612 unlocked file G:\Shares\Working\PWX_Lawson\Co
      ndenseFiles\CDCT_instance_LAWSON_lockfile.lck

      PWX-33254 pwxccl ended. return code 6115
      PWX-26502 Tracing subtask ended at Wed Jan 09 15:09:39 2019

      pwxccl ended. rc=6115

        • 1. Re: PWXCCL for PWX SQL Server CDC
          dan.smith@informatica.com Guru

          That's pretty straightforward.

           

          PWX-33263 0 registrations loaded

          PWX-06115 No active Capture Registrations for processing

           

          If there are no capture registrations, then PWXCCL has nothing to do.

          It tells you that, and then stops.

           

          The question would be: Why are there no capture registrations?

          Probable causes:

          1) DBID is set incorrectly, so PWX is looking for the wrong thing

          (DBID must match DFLTINST in DBMOVER.CFG, and DBID & DFLTINST must match instance name in PWX Navigator!)

          2) There are no registrations that are Active

          3) There are no registrations that have CONDTYPE set to PART

          - PWXCCL ignores any capture registrations set to NONE because that means no condense.

          - PWXCCL ignores any capture registrations set to FULL, because it only does Partial Condense.

          4) You have asked PWXCCL to talk to a different PWX Listener than the one where the capture registrations exist

          - You have coded CAPTURE_NODE=LAWSONPWXLST

          - That tells PWXCCL to ask LAWSONPWXLST to perform capture

          - That means the capture registrations would have to exist at PWX Listener "LAWSONPWXLST"

          • 2. Re: PWXCCL for PWX SQL Server CDC
            vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

            Hello Dan,

             

            Thank you for the reply. Please see my responses below.

             

            1) DBID is set incorrectly, so PWX is looking for the wrong thing.

            (DBID must match DFLTINST in DBMOVER.CFG, and DBID & DFLTINST must match instance name in PWX Navigator!)

            DFLTINST=LAWSON

            DBID=LAWSON

            So, DBID and DFLTINST do match. When it comes to instance name, we have 3 publication databases and i created 3 registration groups for each publication db. But i could provide instance=LAWSON for only one of them because Navigator threw an error saying the instance name 'LAWSON' already exists which is for the first registration while creating registration groups for the other two publication datbases. So, i let Navigator pick the instance name for the other two.

             

            2) There are no registrations that are Active.

            All the registrations are active.

             

            3) There are no registrations that have CONDTYPE set to PART

            All of them are set to PART

             

            4) You have asked PWXCCL to talk to a different PWX Listener than the one where the capture registrations exist.

            Navigator, Listener and Logger all are running on the Informatica PowerCenter Machine.

            In the Navigator, i see that all 3 registration groups have Location=LAWSONPWXLST which is the same as CAPTURE_NODE=LAWSONPWXLST defined in the PWXCCL.cfg file

            • 3. Re: PWXCCL for PWX SQL Server CDC
              vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

              Also, DISTSRV and DISTDB are defined in the PWXCCL.CFG file as well in addition to DBMOVER.CFG file because we want the single PowerExchange Logger instance to read data for all of the registered articles that are published to the distribution database, from 3 source publication databases. All the 3 pub dbs and dist db exist on the same SQL instance. Following is the text i read from CDC guide for DBID parameter just in case if it helps.

               

              "If you specify the optional DISTSRV and DISTDB parameters to have the single PowerExchange Logger instance log data for all of the registered articles that are published to the distribution database, enter a name that serves as the collection identifier for all of the registrations. This name must be one to eight characters in length and start with a letter. This name overrides the instance name that is associated with the individual registrations.Note: When you perform a CAPXRT database row test, you must enter this name in the MSS LUW DBId field in the CAPXRT Advanced Parameters dialog box. When you define a PWXPC connection for PowerCenter CDC sessions that extract data from PowerExchange Logger log files, enter this value for the Logger DBID attribute on the PowerCenter PWX MSSQL CDC Real Time connection."

              • 4. Re: PWXCCL for PWX SQL Server CDC
                dan.smith@informatica.com Guru

                That's odd.

                 

                That all looks correct, but clearly PWXCCL isn't finding the capture registrations (or more correctly, PWX Listener isn't).

                 

                I would suggest looking at the PWXCCL.t*.log alternate logging file, finding the timestamp where it connected to PWX Listener, then find that timeframe in DTLLST.*.log alternate logging file from PWX Listener, and seeing that it shows.

                • 5. Re: PWXCCL for PWX SQL Server CDC
                  vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

                  I shutdown listener and restarted it. And then i started logger. below is the output for both. Just when(190110 141758) pwxccl was finding the registrations(listener) listener did something. I see it read some rows as well conincidentally at the same time when pwxccl was finding the registrations(listener). Something wrong with the ip addresses? the  reason i say that because i see two ip addresses in listener output.

                   

                  PWXCC.t**:

                  ==========

                  190110 141758 WIN64 4504 PWX-15799 E:\Informatica\PowerExchange10201\pwxccl.cfg <E:\Informatica\PowerExchange10201\pwxccl.cfg> PARM INPUT FILE: END(COMPLETE).

                  190110 141758 WIN64 4504

                  190110 141758 WIN64 4504 PWX-21605 Connection selected MSQLLAWS found from covr< MSQLLAWS> tag< > type< MSS> int< FALSE> method< CONN_OVR>.

                  190110 141758 WIN64 4504

                  190110 141758 WIN64 4504 PWX-25802 Process pwxccl pid 4504 locked file G:\Shares\Working\PWX_Lawson\CondenseFiles\CDCT_instance_LAWSON_lockfile.lck

                  190110 141758 WIN64 4504 PWX-25802 Process pwxccl pid 4504 locked file G:\Shares\Working\PWX_Lawson\CondenseFiles\CondenseS_loggerfiles_lockfile.lck

                  190110 141758 WIN64 4504

                  190110 141758 WIN64 4504 PWX-33260 Loading registrations from location "LAWSONPWXLST"

                  190110 141758 WIN64 4504 PWX-33304 Connection: 127.0.0.1:53677, Type: CPR, Task ID: 0, Operation timeout: secs, Heartbeat interval: 30 secs, Session:

                  190110 141759 WIN64 4504 PWX-33304 Connection: 127.0.0.1:53806, Type: CPR, Task ID: 0, Operation timeout: secs, Heartbeat interval: 30 secs, Session:

                  190110 141759 WIN64 4504 PWX-33263 0 registrations loaded

                  190110 141759 WIN64 4504 PWX-06115 No active Capture Registrations for processing

                  190110 141759 WIN64 4504 PWX-25803 Process pwxccl pid 4504 unlocked file G:\Shares\Working\PWX_Lawson\CondenseFiles\CondenseS_loggerfiles_lockfile.lck

                  190110 141759 WIN64 4504 PWX-25803 Process pwxccl pid 4504 unlocked file G:\Shares\Working\PWX_Lawson\CondenseFiles\CDCT_instance_LAWSON_lockfile.lck

                  190110 141759 WIN64 4504

                  190110 141759 WIN64 4504 PWX-33254 pwxccl ended. return code 6115

                   

                  DTLLSTNT.p**:

                  ===========

                  190110 135823 WIN64 23440 PWX-00593 DTLLST1: IP addresses: 10.252.65.17

                  190110 135823 WIN64 23440 PWX-00593 DTLLST1: IP addresses: ::1

                  190110 135823 WIN64 23440 PWX-00651 Listener 10.252.65.17 polling on port 2480

                  190110 135823 WIN64 23440 PWX-00651 Listener ::1 polling on port 2480

                  190110 135825 WIN64 23440 PWX-32503 Command Handler initialization complete, listening on port 12480.

                  190110 135826 WIN64 23440 PWX-00607 Listener LAWSONPWXLST VRM 10.2.0 Build V1020_HOTFIX1_7245739 started.

                  190110 135826 WIN64 23440 PWX-00595 Using config E:\Informatica\PowerExchange10201\dbmover.cfg

                  190110 135826 WIN64 23440 PWX-00595 Using license E:\Informatica\PowerExchange10201\license.key

                  190110 135826 WIN64 23440 PWX-00590 Active control codepage is ISO-8859 (1).

                  190110 135826 WIN64 23440 PWX-00590 Active data codepage is ISO-8859 (1).

                  190110 135826 WIN64 23440 PWX-00590 Active sql codepage is ISO-8859 (1).

                  190110 141758 WIN64 23440 PWX-00650 (461) : Listener ::1 -> 127.0.0.1 on port 2480 socket 416

                  190110 141758 WIN64 6788 PWX-33304 Connection: 127.0.0.1:53677, Type: CPR, Task ID: 420, Operation timeout: secs, Heartbeat interval: 30 secs, Session:

                  190110 141758 WIN64 6788 PWX-00408 DBAPI File <Capture Registration>, 27 rows read.

                  190110 141759 WIN64 23440 PWX-00650 (461) : Listener ::1 -> 127.0.0.1 on port 2480 socket 392

                  190110 141759 WIN64 5688 PWX-33304 Connection: 127.0.0.1:53806, Type: CPR, Task ID: 760, Operation timeout: secs, Heartbeat interval: 30 secs, Session:

                  190110 141759 WIN64 5688 PWX-00408 DBAPI File <Capture Registration>, 4 rows read.

                  • 6. Re: PWXCCL for PWX SQL Server CDC
                    dan.smith@informatica.com Guru

                    PWX Listener always checks to see what TCP/IP addresses it should treat as "local".

                    I assume that means "10.252.65.17" is the assigned TCP/IP address for the box where it is running.

                     

                    "127.0.0.1" is TCP/IP's "loopback" address that always points to the local box, and is what you defined in

                    NODE=(LAWSONPWXLST,TCPIP,127.0.0.1,2480)

                     

                    So: PWXCCL asked LAWSONPWXLST for a list of capture registrations.

                    LAWSONPWXLST read 27 rows and 4 rows from the CCT in its CAPT_PATH.

                    PWXCCL says 0 capture registrations were returned.

                    Which makes it look like those 27+4 rows were discarded for some reason.

                    Which normally would be mismatched DBID, or not ACTIVE, or not PART.

                     

                    Do "27" and "4" registrations match with what you would expect for any of the source DBs?

                    • 7. Re: PWXCCL for PWX SQL Server CDC
                      vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

                      There are 27 registrations in total for all 3 registration groups. not sure of the 4 registrations and where they are coming from.

                       

                      All 27 are ACTIVE and PART. As for DBID,like i mentioned before Navigator did not let me provide the same instance(which is DBID) for all 3 publication groups.

                      • 8. Re: PWXCCL for PWX SQL Server CDC
                        dan.smith@informatica.com Guru

                        When you are running MULTIPUB=Y, the rules are different - you quoted the manual on that earlier

                         

                        I'm wondering if using one of the "real" INSTANCE names as the "generic" one is confusing something, but I don't know.

                         

                        I would suggest raising a case with Support.

                        Include the PWXCCL.t*.log and DTLLST.*.log, and a screen capture of each of the 3 capture registration groups in navigator, showing the resource explorer pane for each group.

                        • 9. Re: PWXCCL for PWX SQL Server CDC
                          vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

                          Thanks Dan. I will open the ticket.

                          • 10. Re: PWXCCL for PWX SQL Server CDC
                            vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

                            The issue is now resolved. Articles were created in PWX without fully qualified source DB server name while PWX config files had fully qualified source DB server name. Changing config files to not include fully qualified source db server name but instead have just the server name resolved the issue.The listener and logger are up and running now.  Can you please help answer below as well?

                             

                            1) The PWX software and config files exist system drive which only admins have access to and the log files, condense files exist on a share drive which the developers have access to. I see that when Registration groups are created using Navigator on the machine where PWX services are running, they are created in system drive under the folder <PWX Installation>\examples\capreg which developers do not have access to. If developers were to create registration groups and capture registration, can we have them create on their local machine and then copy them over to system drive?  If yes, what additional files need to be copied over? or is there a better way to do it?

                             

                            2) The services were started using pwxsettask command as PWX and powercenter are  running on the same windows machine. They are running as foreground processes. Can we have them run as background processes?

                             

                            Thanks,

                            Vijay

                            • 11. Re: PWXCCL for PWX SQL Server CDC
                              dan.smith@informatica.com Guru

                              1) No, you can't create them locally.

                                The registrations and extraction maps are stored by PWX Listener.

                                Registrations live in the CCT, which lives in CAPT_PATH

                                Extraction Maps are individual files, which live in CAPT_XTRA.

                                Please note that it doesn't matter whether your developers have access to CAPT_PATH and CAPT_XTRA, as it is PWX Listener creating the files, under its login.

                               

                              2)  Options for background do exist

                              2.a) You can run PWX Listener or PWXCCL as a PC Admin Console Service, if

                              - running on a PC node

                              - using the same dot-level and hotfix-level of code for both PC and PWX

                              2.b) You can run PWX Listener as a Windows service, if you are running it on Windows.

                              2.c) You can use a mix of wscript and .bat/.cmd files to run either or both, and then use Windows Scheduler (or other package of your choice) to start them.

                               

                              HOW TO: Run multiple PowerExchange Listeners in the background on Windows using wscript

                              https://kb.informatica.com/howto/3/Pages/119919.aspx

                               

                              HOW TO: Run PWXCCL in the background on Windows using wscript

                              https://kb.informatica.com/solution/13/Pages/108899.aspx

                               

                              FAQ: How can PWXCMD send commands to multiple PWXCCL or multiple PowerExchange Listeners?

                              https://kb.informatica.com/faq/4/Pages/117599.aspx

                              • 12. Re: PWXCCL for PWX SQL Server CDC
                                vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

                                I added the following statements to the dbmover file on my local machine to see if the navigator shows all the registration groups/capture registrations/extraction maps.

                                 

                                LISTENER=(LAWSONPWXLST,TCPIP,2480)

                                NODE=(LAWSONPWXLST,TCPIP,10.252.xx.yy,2480)

                                 

                                Navigator didn't pull any registration groups/capture registrations/extraction maps.

                                • 13. Re: PWXCCL for PWX SQL Server CDC
                                  dan.smith@informatica.com Guru

                                  The LISTENER statement is only used by the Listener.  It is not used by Navigator.

                                  A NODE statement is used when trying to locate a PWX Listener to talk with.

                                  That doesn't automatically populate any groups.

                                   

                                  For almost all sources, the "groups" are only labels at the Navigator install that tell Navigator to talk to a specific Listener and ask about a specific source.  For these sources, you can create a new group, pointing to the correct Listener and asking about the correct source, and Navigator will show you all the registrations (and/or extraction maps).

                                   

                                  The exception is SQL Server, where the "groups" are tied to the PWX Publication, and actually exist, and the files have meaningful content.

                                  For these, you can't simply create a new group.

                                   

                                  If you want to copy an SQL Server group definition to a different Navigator install, you have to play games with the .crg files.

                                  I would suggest raising a case with Support if you want to do that.

                                  • 14. Re: PWXCCL for PWX SQL Server CDC
                                    vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

                                    When running the CDC workflow, the session is failing with below error. We recycled the integration service to see if PC will pick the PWX binaries but it didn't. Should we reboot the node/machine itself? Note that PWX and PC are running on the same machine.

                                     

                                    [ERROR] Error occurred loading library [dtlscli.dll]. System error encountered

                                    is 126. Error text is The specified module could not be found.

                                     

                                    we ensured that

                                     

                                    • PowerExchange is installed on the integration service machine
                                    • Updated the PATH environment variable to include the PowerExchange directory dir
                                    • Recycled the PowerCenter Integration Service so that the new environment changes can be picked up.
                                    1 2 Previous Next