Informatica MYSQL POWEREXCHANGE(PWX) CDC

Abstract:
This blog explains the concept of PWX, MySQL CDC 10.4.1 (latest, at the time of writing this blog) and discusses about the benefits and Challenges of PWX with CDC.
High level Architecture on Informatica BDM with Mysql PWXCDC:

Architecture for Mysql PWXCDC:

**The Explanation for the CDC Publisher is given in separate blog.

Overview on PWX with Change Data Capture:

PWX reads change events, including data changes, for the source tables from the MySQL binary log by using the MySQL binary log reader, mysqlbinlog. PWX captures change records for registered source tables. By capturing the database inserts, updates, and deletes these events as soon as they occur on their application database systems. This captured stream of database activity can be delivered to multiple targets in real time, without intermediate queues or staging tables.

PWX Listener:
The PWX Listener manages to capture registrations and extraction maps for all CDC data sources. The PWX Listener uses the DataDirect ODBC driver that PWX supplies to connect to the MySQL system.
PWX Logger:
PWX Logger can be used to capture the change data and write it to log files by configuring the PWX Logger. The change data is then extracted from the PWX binary log files.

PWX Express CDC for Mysql:
ODBC.ini:
[EEEE]
Driver=
SERVER=xxxx.com
PORT=3306
USER=aaa
Password=abc@123
OPTION=3
SOCKET=
NO_SSPS=1

To check the connection to MySQL through the server:
isql -v EEEE

pwxcatmy command:
Source needs to be initiated before register the table.

• Password is mandatory to register the table.
• CATSCHEMA available in dbmover configuration file.
• Refer the sample register commands.

$PWX_HOME/pwxcatmy OPERATION=REGISTER CATHOSTNAME=dsn:EEEE CATUSERNAME=aaa CATPASSWORD=’abc@123’ CATSCHEMA=HHHH schema=HHHH table=YYYY

Operations performed:

• Register – to register a new table
• Unregister – to unregister an existing table
• List – to list the registered tables
• Drop – to drop a table in case of table corruption
• Verify – to verify the tables that are available in the database
• Create – to create a new table

Sample script to create a PWXCAT table and update tables in any database with the help of DBA Admin team based on the project requirement:
CREATE TABLE HHHH.`PWXCatTables` (
`tid` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
`serveruuid` BINARY(16) NOT NULL,
`schemaname` VARCHAR(64) NOT NULL,
`tablename` VARCHAR(64) NOT NULL,
`initcoord` BIGINT(20) UNSIGNED NOT NULL,
`curcoord` BIGINT(20) UNSIGNED NOT NULL,
`ranges` TEXT NOT NULL,
PRIMARY KEY (`tid`),
UNIQUE INDEX `serveruuid` (`serveruuid`, `schemaname`, `tablename`)
)
COLLATE=’utf8_general_ci’
ENGINE=InnoDB
AUTO_INCREMENT=2
;
*****************************************************************************
CREATE TABLE HHHH.`PWXCatUpdates` (
`tid` INT(9) UNSIGNED NOT NULL,
`type` ENUM(‘snapshot’,’created’,’removed’,’altered’) NOT NULL,
`desc` TEXT NOT NULL,
`coord` BIGINT(20) NOT NULL,
INDEX `tid` (`tid`),
CONSTRAINT `PWXCatUpdates_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `PWXCatTables` (`tid`) ON DELETE CASCADE
)
COLLATE=’utf8_general_ci’
ENGINE=InnoDB
;
*****************************************************************************
Note: The registered tables will be available in these PWXCAT tables.

PWX Express requires some configuration changes to DBMOVER.CFG and PWXCCL.CFG.
dbmover.cfg :
/***************************************************************
/* PWX Configuration File
/***************************************************************
LISTENER=(EEEE,TCPIP,2480,262144,262144,262144,262144)
NODE=(local,TCPIP,127.0.0.1,2480)
NODE=(EEEE,TCPIP,127.0.0.1,2480)
NODE=(default,TCPIP,x,2480)
/* SVC & CMD node for Listener (dtllst)
SVCNODE=(EEEE,8500)
CMDNODE=(EEEE,LISTENER,127.0.0.1,8500)
/* SVC & CMD node for Logger (pwxccl)
SVCNODE=(pwxccl_eeee,8522)
CMDNODE=(pwxccl_eeee,CONDENSE,127.0.0.1,8522)
/*
APPBUFSIZE=1048576
COLON=:
CONSOLE_TRACE=Y
DECPOINT=.
DEFAULTCHAR=*
DEFAULTDATE=19800101
MAXTASKS=30
MSGPREFIX=PWX
NEGSIGN=-
PIPE=|
POLLTIME=1000
PRE861_COMPAT=N
EXT_CP_SUPPT=Y
/*PATH*/
CAPT_PATH=/PWX_HOME/ CAPT_PATH_DIR/
CAPT_XTRA=/PWX_HOME/ CAPT_XTRA_DIR/
LOGPATH=/PWX_HOME/LOGS/
DMX_DIR=/PWX_HOME/ DMX_DIR
TRACE=(MYSQL,9,99)
TRACE=(mystrace,9,99)
/* —————————*/
CAPI_CONNECTION=(NAME=MYSCPX,TYPE=(CAPX,DFLTINST=EEEE))
CAPI_CONN_NAME=CAPIMYS

CAPI_CONNECTION=(NAME=CAPIMYS,DDLTRACE=mystrace,TYPE=(MYSQL,CATSCHEMA=HHHH, SERVER=dsn:EEEE, RECONNTRIES=5, ONDATATRUNC=fail,RSTRADV=15,ONTABLETRUNC=FAIL,ONTABLEDDL=warn-until-eol,MYSQLBINLOG=’/usr/bin/mysqlbinlog”))
CAPI_CONNECTION=(NAME=PUBCX01,DLLTRACE=cpx,TYPE=(CAPX,DFLTINST=SYNC,FILEWAIT=60,RSTRADV=15))
/*——————-*/
TRACING=(PFX=log,APPEND=N,FILENUM=5)
/**************************************************************

PWXCCL.CFG file for PWX Express CDC for MySQL:
/***************************************************************
/* PWX PWXCCL Configuration File
/***************************************************************
CONDENSENAME=pwxccl_eeee
DBID=SYNC
DB_TYPE=MYS
CAPTURE_NODE_UID=UIDEEEE
CAPTURE_NODE_EPWD=Xyz0987
Verbose=N
PROMPT=N
EXT_CAPT_MASK=/PWX_HOME/ CAPTURE /CondenseO
COND_CDCT_RET_P=14
LOGGER_DELETES_EXPIRED_CDCT_RECORDS=Y
/* COLL_END_LOG 0==Continuous capture (normal) 1==stop at end-of-log (batch)
COLL_END_LOG=0
/*Number of minutes to wait between CAPI read cycles in seconds
NO_DATA_WAIT=0
/*Number of seconds to wait at the end-of-log for more change data
NO_DATA_WAIT2=60
/*Number of seconds before flushing,or writing,data to the current log file on disk
/*-1 = No flush, 0 = flush every record, 1 to N flush every N seconds
FILE_FLUSH_VAL=10
FILE_SWITCH_CRIT=M
FILE_SWITCH_VAL=20
CAPT_IMAGE=BA
SEQUENCE_TOKEN=00
RESTART_TOKEN=00
/************************************************************
Listener and Logger Steps:

Step 1.Start the listener by going to the PWX directory PWX_HOME and execute the command in below format:

nohup dtllst EEEE  CONFIG=/PWX_HOME/eeee/dbmover_eeee.cfg > /PWX_HOME/logs/eeee/start_listener.log &
Execute below command will display the listener running status.
ps -ef|grep dtllst|grep eeee
Step 2.Start the logger by going to the PWX directory PWX_HOME and execute the command in below format:
nohup pwxccl coldstart=n cs=/PWX_HOME/eeee/pwxccl_eeee.cfg config=/PWX_HOME/eeee/dbmover_eeee.cfg > / PWX_HOME /logs/eeee/start_logger_eeee.log 2>&1 &  
Note:
Coldstart=n – this will bring the data from the last processed condense file
Coldstart=y – this will bring all data from the beginning (chances to have duplicate data)
Execute the below command to check the logger status.
pwxcmd displayall -sv pwxccl_eeee
Execute below command will display the logger running status.
ps -ef|grep pwxccl|grep eeee
Note:  Bring up the listener in server then make an entry in the dbmover.cfg file where installed in navigator and then execute the below steps.

Initially an entry should be made in dbmover configuration file before proceeding to the registration.

Sample screenshot for ODBC configuration installed in remote:

Registration Steps:
Step 1:  Go to the Informatica PWX 10.4.1 tool. Click the PWX Navigator then page will be opened as in the below screenshot.

Step 2: Open the Data Capture tab à Registration group

Step 3: Right click Registration group then add the Add Registration Group by entering the valid user id and password and click finish.

Step 4: Once authenticated, right click on Add Capture Registration, and give the details of Name, Schema and Table.
Note: The given topic name should be in 8 characters.
Fill the details and click next.

Step 5:  Double click on the schema name.

Step 6: Click on ‘Select All Columns’ and click next.

Step 7: Change the status to Active and click Finish.

Row Testing:

One way to test your extraction mapping is by doing row test. This is done from the Extraction group.

DTL__CAPXUSER: The username should be populated in this column.

Challenges with PWX CDC :
• PWX limits the length of data in columns that have a mediumblob, longblob, mediumtext, longtext, enum, json, set, varbinary, or varchar datatype to 98,304 bytes.
• When there is a mass update in source, there will be huge CPU consumption and takes a longer time to read the condense file.
• If any new/modify/drop column action is carried out in source, then the table must be registered in pwxcat tables and PWX navigator tool, logger must be brought down and then brought up.
• Table registration is not possible when the table doesn’t have the primary key.
• CAPX__ROWID will not be populated.

PWX with CDC – Benefits:
Below are the benefits by using the PWX with CDC
• Access and deliver the data in “right time”.
• For audit and other purposes of keeping track of the deleted record CDC can be great help as the deleted record in source will be captured in dtl__capxaction=D in PWXCDC.
• It has the in-built feature for accessing the replication logs. So, when the tables are registered the replication logs will be executed internally in the tool.

Conclusion:
The PWX Change Data Capture (CDC) Option can satisfy our business requirements for up-to-the-minute data. PWX Change Data Capture (CDC) Option is available for all popular enterprise database systems, this Option is essential whenever you need timelier access to data. When combined with real-time data integration platform, event-driven data can be accessed, transformed, and cleansed continuously and used to drive business results in any enterprise, large or small. Please feel free to reach Cittabase for more information. Visit our blogs for more topics on informatica

References:
https://knowledge.informatica.com