We are using CDC to replicate some larger data mart tables between DB2 UDB (Source) and Oracle 11g (target) database. The tables have more than 200 million rows of data. We are using a derived column on one of the field on the source table. The expression for the derived column has %GETCOL, %CONCAT and %IF statement. We are doing the initial refresh of these tables to the target environment. With the derived column CDC the performance of the CDC refresh degraded a lot. Without the derived column the table was refresh at a rate of 9 million rows per hour. Once we had the derived column the table was refreshing at a rate of 2.5 million rows per hour.
The derived expression is given below. We use a lookup table (LOOKUP_TBL) to get the column GL_DEPT_NO using GL_DEPT as the key field if the GL_DEPT field value is not blank in the replication table. If the GL_DEPT value is non blank and if it is not on the lookup table then the GL_DEPT value is prefixed with "0" to get the derived value.
%IF(GL_DEPT=' ',' ', %GETCOL(GL_DEPT_NO,"PSYSADM.LOOKUP_TBL",%CONCAT("0",GL_DEPT),GL_DEPT,GL_DEPT))
Is there anything we can do to improve the performance of the initial load?. Any help is greatly appreciated.