Home
Welcome to my repository

The wf_i5OS_CDC workflow demonstrates how to use “i5/OS Data queue & CDC connector” to build replication solution which reads changes from a transaction journal (change data capture). The workflow reads changes from tables on as/400 (iSeries) machine. The changes collected from transaction journal are applied to tables in relational database. Target database can be any database supported by Power Center.

 

AS/400 DATA QUEUE & CDC connector for Informatica PowerCenter has been improved.

  • It can survive TCP connection loss. But what is most exciting, you can sleep well while tables structure is being changed.
  • You can even define and start replication of a table which is to be created in the future. When the table is created replication starts as defined.
  • As/400 can have FIFO or LIFO tables with no unique keys. So most of replication solution are useless. The connector allows you to solve the problem.
  • The CDC can be used to replicate data with RRN as a replication key.
  • AS/400 component of the connector has been optimised.
  • Included sample replication solution based on as/400 journal. The sample replicates data from as/400 to any relational database which is supported by Informatica PowerCenter. It is recommended, to run the solution on PowerCenter 9.0.1 or above as "Time Base Commit" is required. 

On the site is available i5/OS CDC connector for Informatica PowerCenter which  is able to read transaction journal entries from i-System (old As/400) in real time.  This entries can be used to replicate changes to other database, to audit database changes made by users or to compare two processes.

Use Case Description.

There are two identical databases on two different i - Systems (As/400 Systems) and two application sets, which calculates actuarial reserve. One set of applications is the old one. The second applications set must give the same results but is optimised in order to run faster. The challenge is, how to compare what the two sets do the same and what they do differently.

Version 1.2.3 of the Data Queue connector for Power Center support all data queue types and enables reading i5/OS database changes in real time. New name of the product is "Data Queue & CDC Connector for i5/OS". Data available in the i5/OS journal are propagated to Informatica Power Center. Power Center real time edition is the only licence requirement.   

The Data Queue & CDC Connector for i5/OS is a real time connector. Below I present Change Data Capture solution for i5/OS(AS/400) which does not stop in case of table structure alteration as table structures are discovered in real time.  

  • v1m2r2 version of the connector is featured with:
    • change data capture for i5/OS. It means Power Center can read online changes from i5/OS (as/400) database.
    • data queue connector for keyed, FIFO and LIFO data queue.
    • the connector is used to develop replication worflows.

It is common to use the shell command redirection in PowerCenter. The feature allows one to use the ldapmodify command to load data to LDAP. To achieve this it is necessary to create an LDIF flat file. Some data in the file must be in UTF-8 code page and Base64 encoded.

This article is written based on Power Center 9.0.0. But it seems to apply to other versions as well. Let's assume that one needs to write a BASE64 encoded string to a flat file.

The solution shows how to:

·       use shell command to generate input flat file list.

·       each flat file with name <name>.<extension> from the list above is processed and written to output file under the name <name>_O.<extension>.

·       to achieve that transaction control transformation is required. For every transaction it is generated flat file with new name.   

·       expression transformation shows how to check whether new input file is processed. If new file is processed port NewFile is set to 1 (0 in other case). This is used in transaction control transformation to generate new transaction. One of more interesting issues is using local variables in the expression transformation.

PowerCenter flat file target can be configured to redirect output to shell command standard input instead of a flat file.

This technique can be used to:

 

PowerExchange for Websphere MQ returns RFH2 message in binary format. Convert the RFH2 message in binary format to something readable by mapping transformations.

 

 

These tools enable one to develop mapping, which reads requests through the “PowerExchange for Websphere MQ” connector and sends replies, if required to temporary, dynamic queues. Messages are exchanged in RFH2 format, which is compliant with JMS. Source queues and queue managers are defined by PowerExchange for MQ connector. Target queues and queue managers are dynamically calculated in the mapping.