Informatica Power Exchange Change Data Capture Implementation For Oracle

  • Power Exchange Listener : The PowerExchange Listener manages data maps for non relational files and capture registrations and extraction maps for all data sources. It also handles extraction requests for bulk data and change data.  
             1) CCT file for capture registrations
             2) CAMAPS directory for extraction maps
             3) DATAMAPS directory for DB2 data maps

  • Power Exchange Logger: PowerExchange Logger receives captured change data from the ECCRs that are connected to it and stores the change data in log data sets. 

The PowerExchange Logger for Linux, UNIX, and Windows captures change data from DB2 recovery logs, Oracle redo logs, or a SQL Server distribution database and writes that data to PowerExchange Logger log files.

Use of the PowerExchange Logger is optional. To use the PowerExchange Logger, run one PowerExchange Logger process for each database type and instance. The PowerExchange Logger writes all successful UOWs in chronological order based on end time to its log files. This practice maintains transactional integrity. You can extract the change data from the PowerExchange Logger log files in either batch or continuous mode. 

Source database overhead is reduced because PowerExchange makes fewer accesses to the source log files or database to read change data. For Oracle, this overhead reduction can be significant

The PowerExchange Logger can use only one Oracle Log Miner session to read change data for all extractions that process an Oracle instance.

You do not need to retain the source RDBMS log files longer than normal for CDC.

PowerExchange does not need to reposition its point in the DB2 or Oracle logs from which to resume reading data. This feature can significantly reduce restart times.
  •  Power Exchange Condenser : Condense creates condense files that contain a condensed version of the changes that were captured by an ECCR and stored by the Power Exchange Logger.

  • The Power Exchange Navigator : Is the graphical user interface that you use to define and manage data maps, capture registrations, and extraction maps for the data sources from which you want to extract bulk data or capture change data.

You must define a capture registration for each source table. The corresponding extraction map is automatically generated. For DB2 sources, you can also define data maps if you need to perform column-level processing, such as adding user-defined columns and building expressions to populate them.


You can import the extraction maps into PowerCenter so that they can be used for
            moving change data to the target
         
Features of  Change Data Capture :

PowerExchange Change Data Capture (CDC) works in conjunction with PowerCenter to capture changes to data in source tables and replicate those changes to target tables and files.

Relational Sources Which It Supports :

1) DB2 for Linux, UNIX, and Windows
2) Microsoft SQL Server on Windows
3) Oracle on Linux, UNIX, or Windows

After materializing target tables or files with PowerExchange bulk data movement, you can use PowerExchange CDC to synchronize the targets with their corresponding source tables. Synchronization is faster when you replicate only the change data rather than all of the data.

Steps Involved in Change Data Replication :  

Change Data Capture: PowerExchange captures change data for the source tables. PowerExchange can read change data directly from the RDBMS log files or database. Optionally, you can use the PowerExchange Logger for Linux, UNIX, and Windows to capture change data to its log files.

Change Data Extraction: PowerExchange, in conjunction with PowerCenter, extracts captured change data for movement to the target.

Change data apply: PowerExchange, in conjunction with PowerCenter, transforms and applies the extracted change data to target tables or files.

Integrate Power Exchange With Power center :

  • PowerCenter works in conjunction with the PowerExchange Client for PowerCenter (PWXPC) to extract the change data that PowerExchange captured and apply it to one or more targets.

  • The primary function of PWXPC is to integrate PowerExchange with PowerCenter so that PowerCenter can access PowerExchange-controlled data and write it to various targets. With PWXPC, CDC sessions can extract change data directly from the change stream and from PowerExchange Logger for Linux, UNIX, and Windows log files.  

  • PowerCenter provides transformation and data cleansing capabilities, which you can use in your CDC sessions. 
   Power Exchange Change Data Capture Architecture :



  Change Data Capture Implementation :

  1. Configure parameters in the dbmover.cfg file for the PowerExchange Listener.
  2. Start the PowerExchange Listener on the machine with the source database.
  3. Perform RDBMS-specific configuration tasks for CDC.
  4. Configure the PowerExchange Logger.
  5. Start the PowerExchange Logger.
   Define Data Sources For CDC :
  • From the PowerExchange Navigator, define and activate capture registrations and extraction maps for the data sources.
  • For DB2 sources that have user-defined or multi-field columns that you want to manipulate, create DB2 data maps.

Materialize targets and start capturing changes:
  1. Materialize the target from the source.
  2. Establish a start point for the extraction

Extract and apply change data :

  From PowerCenter, configure mappings, workflows, connections, and sessions. Then run the  workflow.

 

17 comments:

  1. Hi,
    I came across this blog very recently and its really helpful.
    I'm new to PowerExchange Change Data Capture Real Time.

    Is it possible for you to help me in this? I have some error messages and couldn't get any information on solving these issues. Any help would be much appreciated. Thanks..!!

    PWX-07038 Group Fetch read connection failed: rc=271 rc1=271 rc2=1266.
    PWX-00271 DBAPI Error. DB_READ failed for file .
    PWX-01266 DBNTC Receive READ header for file failed, rcs 260/2011/9973.
    PWX-02011 SQL fetch error. SQLCODE = 9973.
    PWX-04566 Capture Extract RC=9973 from CREAD_Open
    PWX-09973 CAPI i/f: RC=12 from CAPI_Open. Connection DTLJPAS4. Sources 1.
    PWX-06117 Failure loading registration for Instance QAASP.
    PWX-06119 Controller: missing registration tag AS4DTLJPAS4f48011

    ReplyDelete
  2. There could be 2 reason for the issue:

    1. The Session was Warm started and the PWX Express Log reader could not access the log file "/Oracle80/dbdata/arch/log_800674682_1_414.log" with respect to the restart tokens provided, which caused the failure.

    In this scenario , you have to check check with DBA if the specified Log files are available for the CDC to continue processing the data.

    2. Supplement log is not enabled for Database.

    ERP DBA will have control on arch logs..hence they are the responsible for these sort of issues

    ReplyDelete
  3. Thanks for the reply..!!
    But, in any of the cases that you've mentioned, it should be the same for all the sessions.
    In my case, for one session it is okay, rest of the sessions are failing with this error. I couldn't figure out the issue cause. Also, I did a cold start. Any help..??

    ReplyDelete
    Replies
    1. Check for below:
      A. access on the file
      B. Reload the extraction maps

      Delete
  4. Command to ping the pwx listener

    ReplyDelete
  5. Hi Shivakumar,
    Thanks for the informative article. Our requirement is to capture the CDC data and store them on the Data warehouse with history information.
    For an example when a customer has changed his name we want to insert a new row with all the details including changed name and make that row as the active row. And update the old row as de-active. Can this be done via GUI or do I need to write scripts ?
    Thili

    ReplyDelete
  6. Hi Shivakumar,

    What parameter entry can I add or modify in the pwx dbmover.cfg file to prevent this error from occurring when things maybe get delayed briefly on mainframe and db2 v10 processing?

    From DB2PPEP1.DTLLOG01

    141115 065235 MVS 11 PWX-33302 *windows machine* : connectivity lost - Heartbeat
    send failed. RC1=-1, RC2=14, RC3=121.

    From DB2PPEPL.LOGGER
    6:52:47.98 PWXEDM172592I EDM Logger data transfer for STC0055714F178D0 ended.
    6:52:47.98 PWXEDM172669I Log reader agent STC0055714F178D0 disconnected from L
    code=00000000
    6:52:48.03 PWXEDM172597I EDM Log Reader ended for STC0055714F178D0 at 2014.319

    Thank you for any support.
    Brett

    ReplyDelete
    Replies
    1. Just revisiting this - I was able to correct the issue back in Jan 2015 by using this parm SERVICE_TIMEOUT=(ISYNC,60) in PWX config on Mainframe and then up the service class for Production DB2PPE* started tasks to give them more priority when things do get busy.

      Delete
  7. Below is the error in informatica. We are using this soap connection in UAT and PROD.  Account [ipc.salesforce@wellcare.com] logged on salesforce.com at [https://www.salesforce.com/services/Soap/u/14.0].

    [ERROR] Bulk API cURL error received. Error message [unknown version: 14.0].

    ReplyDelete
    Replies
    1. My question is where they are using the sales force URL ? on the internet explorer if that is the case they'll have to use the right version of IE

      Delete
  8. Hi Shivakumar,

    Thank you for the detailed explanation.I am working on power exchange for mainframes and i stuck with an issue. Could you please help me to resolve this?

    Issue:-
    I have created a multi record datamap in power exchange navigator and Imported it as multi record datamap to power center. when i am trying to load the data from this multi record source to a flat file.The session is getting failed with the below error message .

    "Can not get metadata extension Use sequence fields".

    I have checked the use sequence fields option while importing the source but i am unable to read the data from source. Please help me in resolving this issue. Please let me know if you need more information.

    Thanks,
    Hanumantha Reddy Peram.

    ReplyDelete
  9. HI Shivakumar,

    Is there any performace degrdation in the target system due to data replication? Any idea about which is more efficient in terms of CPU and memory utilization CDC or oracle ADG. The question oracle database is to be replicated.

    Thanks
    Mukesh

    ReplyDelete
  10. Hi Mukesh,
    can you please share any experiences in implementing the CDC for powerexchange for salesforce.

    ReplyDelete
  11. i have learnt now what is informatica is all about, thanks for sharing this here. I have been following your blog for a long time. This would be useful for my Best Informatica Training In Chennai

    ReplyDelete
  12. Hi Shiva,

    I have added a capture registration and after that automatic the map is added into Extraction map. Whenever I trying for Row Data Test I am getting below errors. Kindly help
    PWX-01266: dbntc receive READ HEADER FOR FILE
    PWX-02141:record mapping failure
    PWX-02142: field extends past end of record


    ReplyDelete
  13. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info. odzyskiwanie skasowanych smsów

    ReplyDelete
  14. Is there any way you can provide some detailed information on Informatica.Thanks

    ReplyDelete

Popular Posts

Featured Post

Basic unix commands used in Administration

ls –ltr                Shows all the files and sub directories in the  current directory. ls –la                  Shows all the hidden...