Home
 
Welcome to my repository

Dear Guest,

 

We are happy to do IBM i/AS400/iSeries Change Data Capture Proof of Concept at your infrastructure .

The cost of setup and support is 40$ an hour for the first 8 hours. We do not charge for time above 8 hours.

 

Installation is accomplished remotely.

 

POC environment must consists of:

 

1. Informatica Power Center. (Admin right for plugin installation).

2. IBM i with *ALLOBJ access for Change Data Capture process.

3. MSSQL database. (As target database) .

 

Feel free to send email and add your suggestion for your excpectations.

Please send an email to This e-mail address is being protected from spambots. You need JavaScript enabled to view it

 

Regards

Cezary Opacki

 

 

 

The reason for building my "IBM i" Change Data Capture solution for Informatica® PowerCenter is to grab complete stream of "IBM i" database changes and process them with great power of Informatica® PowerCenter. 

Let me present, what can be done with the "IBM i" Change Data Capture for Informatica® PowerCenter solution.

 

  1. IBM i contains your crucial database with great number of large tables. You would like to have copy of those tables in a database build by different vendor let say Microsoft®. But copy of those tables takes several hours. You want them to be always up-to-date. We can setup change data capture solution in a few hours for hundreds of tables.  
  2. You would like to gather data for your AI solution. Unfortunately Slowly Changing Dimension is not so appropriate. You would like to analyze changes not for different days, months, years by for seconds or even to subsequent changes which were applied to your database.  Building known Warehouse solutions one must always loose some information and you decide which before warehouse is setup.   Our solution  let you keep all information as long as you need them. Query them as one do it in source database. Compare any states of source database. After analises, you can decide that some states are not important, than define a query which shows important states and the tool removes not important states. 
  3. You would like to start some processes when an important business event has occurred. Just define logical condition which defines when your event has occurred. As you can keep current and old database states, your event condition can be built using all database states that ever happened.

 

CDC Features

 

As you see, CDC is not enough. We need additional functionalities to mainan source and targets in consistent states.

  1. MONITCDC, to be informed that something is wrong (the file system is full) .  
  2. wf_CDC_DATETIME_CHK, when we would like to compare source and target tables on column level to make sure we have all data  replicated.
  3. wf_CDC_FULL_REFRESH, for new tables we have to do FULL REFRESH.
  4. wf_CDC_Synchr_Diff, in some cases, we need to load just differences between source and target tables.
  5. wf_i5OS_CDC, always running, change data capture. 

All five workflows are oriented on multi table operations. Thus, adding tables, changing structure does not required to do any changes to Workflows or Mappings.

My LinkedIn profile is open. So feel free to send me and email if you have any questions. Cezary Opacki LinkedIn profile

 

Change data Capture workflow is described hier

The most complicated and significant session in change data capture (CDC) workflow (See the workflow) is the one responsible for transforming transaction journal entries in to database rows. In our solution, the role is taken by s_CDC_Replication_from_i5OS replication session session. 

 Mapping for session transforming journal entries into database rows

 Please notice, Entry Specific Data is transformed (red line) into database row represented by ESD field/port in the target transformation named TargetDatabase. In any database a row must belong to a table. In our case, TargetDatabase transformation must know to which table processed row belongs. The data are encapsulated in EntrySpecificData field and passed to ESD field. Therefore, an addition of new table does not require changes to presented mapping.

The same is true, when a table structure is changed. The solution is possible due to new "Generic Target" connector for Informatica® Power Center.

 The presented solution, allows to build real time mapping in minutes, where the task does not depend on number of tables to be replicated. At the moment, the solution does not create new tables in target database but it is intended to do so.

I can help you remotely wherever you are. I am ready to make my solution better and solve your issues with change data capture from IBM i (as400, i5/OS, iSeries) . My email is This e-mail address is being protected from spambots. You need JavaScript enabled to view it .  

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.5 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.