Informatica uses cache files for various transformation wherever it needs to store data throughout the life of the mapping. For all these transformations Informatica uses Index Cache and Data Cache. This is done using either memory alone or using memory and physical disks. When the cache settings are calculated correctly and enough room is provided, Informatica PowerCenter stores data in memory. In cases where not enough size is provided for index and a data cache, Informatica stores as much as it can in the memory and then starts creating cache files on the physical disks. For example, if your cache file was supposed to have 500,000 lines but you only provided room for 100,000 lines through your cache settings, Informatica will write rest (400,000) of the lines into cache files on physical disks. I would think the size of these files depends on the kind of platform you are on.
In case of Lookup Transformation, Informatica also sorts the data by condition ports sequence. In cases where lookup cache is needed to be written to the physical disk, this sorting operation itself can become time consuming. But this could be advantageous if you can select the rows to be processed from the source qualifier in the same sorted order as the lookup condition ports sequence. So, if cache data for a particular key is in memory, then most probably the data for the adjacent keys will also be in memory. In this case, the time that lookup transformation would take to lookup data would keep increasing for each row as it would have to keep digging deeper in the file for each new row. Eventhough it sounds bad, it’s not it’s better than having data in random order. With appropriate indexes on the source tables, this operation could become very fast.
Wherever possible, use sorted data for these transformations. It is advisable to count appropriate amount of cache settings for each transformation that uses caching. Also one must consult the DBAs to come up with a good indexing strategy for all source tables.
Transformations that use caching for their process are…