Home Administrator

Administrator

Email: This e-mail address is being protected from spambots. You need JavaScript enabled to view it

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 .  

Problem description.

The following error was found in the session log.

 

Lookup query is about to be executed.

Database driver event...

FnName: Execute -- [Informatica][ODBC SQL Server Driver]20152

FnName: Execute -- 523 158]

Error in lookup sql statement execute.

 

There were 5 fields read by lookup transformation from MSSQL database. When removed any 3, no errors noticed. When removed only 2, above errors where generated.

Solution.

The failing transformation was removed and created again. No errors noticed.

Licence of Informatica PowerExchange for i5/OS (as/400) is defined using i5/OS machine IP address. 

After domain name change, PowerExchange did not start. In the PWX log was pointed out that licence key is not correct.

 

Problem was solved by adding full host name (with domain name) to the host table.