When using ETL (Extract Transform and Load) tools such as Informatica Power Center, Ascential Data Stage, BO Data Integrator, Microsoft SSIS and others, SQL based CDC can be used to retrieve incremental source table changes and apply them to the target tables.
An SQL-based Change Event Router is used to read changes off a CDC agent and write these events into multiple change files (implemented as DISAM files). The following diagram shows the components related to SQL-based Change Event Router.
The system has three platforms:
  • Database platform: This is where the database and agent run. It can be any platform supported by AIS. The database platform is, in many cases, also a legacy application platform, which means that processing overhead on this platform should be minimized.
  • Staging area platform: This is where the Change Table (Change File) are hosted. This platform also hosts the SQL-based Change Router which enters data into the change tables. It can be any Windows or UNIX platform.
  • ETL platform: This is where the ETL tool runs. It can be any platform supported by AIS, as well as any platform that can run any of AIS thin clients (e.g., any standard Java platform). The ETL platform may be the same as the Staging Area platform, this depends on the ETL tool resource requirements. In general, if the platform is strong enough, then placing both the staging area and the ETL tool on the same platform can reduce network utilization and improve throughput.