Calculate the cache size

If you calculate the cache size requirements correctly and sufficiently, all cached data will remain in memory and not create files on the server.

Files will be created on the server when

1. The cache space requirements exceed the memory allocated for any of the transformations that create caches: Agg, Rnk, Lkp, Jnr, Srt.
2. Incremental aggragation is used with an aggragator transformation to store the finished aggragated values until the next run
3. Persistant lookup cache is selected to store the lookup caches until they are reused by the next lookup transformation.

Aggregate Cache Calculation
Cache	Calculation	Columns in Cache
Index	# groups [( column size) + 17]	Group-by columns.
Data	# groups[( column size) + 7]	Non group-by input ports used in non-aggregate output expression. Non group-by input/output ports. Local variable ports. Column containing aggregate function (multiply by three).*
* Each aggregate function has different cache space requirements. As a general rule, you can multiply the column containing the aggregate function by three.

Rank Cache Calculation
Cache	Calculation	Columns in Cache
Index	# groups [( column size) + 17]	Group-by columns.
Data	# groups [(# ranks *( column size + 10)) + 20]	Non group-by input ports used in non-aggregate output expression. Non group-by input/output ports. Local variable ports. Rank ports.

Joiner Cache Calculation
Cache	Calculation	Columns in Cache
Index	# master rows [( column size) + 16]	Master column in join conditions.
Data	# master rows [( column size) + 8]	Master column not in join condition and used for output.

Table 19-6. Lookup Cache Calculation
Cache	Calculation	Columns in Cache
Index	# rows in lookup table [( column size) + 16]	Columns in lookup condition.
Data	# rows in lookup table [( column size) + 8]	Connected output ports not in the lookup condition. Return port (for unconnected Lookup transformations).

For more information about each cache, see the separate sections in this chapter.

Cache Column Sizes

When you calculate the column size for each cache, include the size of the data and additional processing requirements.

Table gives the columns sizes for index and data cache calculations:

Column Sizes for Cache Calculations
Datatype	Aggregator, Rank	Joiner, Lookup
Binary	precision + 2	precision + 8 Round to nearest multiple of 8
Date/Time	18	24
Decimal, high precision off (all precision)	10	16
Decimal, high precision on (precision <=18)	18	24
Decimal, high precision on (precision >18, <=28)	22	32
Decimal, high precision on (precision >28)	10	16
Decimal, high precision on (negative scale)	10	16
Double	10	16
Real	10	16
Integer	6	16
Small integer	6	16
NString, NText, String, Text	Unicode mode: 2*(precision + 2) ASCII mode: precision + 3	Unicode mode: 2*(precision + 5) ASCII mode: precision + 9

Cache Calculator

Use the cache calculator for aggregator, joiner, rank, and sorter transformations to improve performance.

Select to calculate the total requirements for a transformation based on inputs. The cache calculator requires different inputs for each transformation. You must select the applicable cache type to apply the calculated cache size. For example, to apply the calculated cache size for the data cache and not the index cache, select only the Data Cache Size option.

The cache calculator estimates the cache size required for optimal session performance based on your input. After you configure the cache size and run the session, you can review the transformation statistics in the session log to tune the configured cache size.

Note: You cannot use the cache calculator to estimate the cache size for an XML target.

lookup SQL override for avoid text filed cache

The override sql needs to be in the session because of the performance issue.  If the sql only in the mapping, when create the lookup cache, powercenter will include the CHARGE_VAL_TXT in the order by (sorting). The follow sql in the session will avoid the issue.  This override is there to avoid cache.

pmrep ObjectImport conflict problem

When I do pmrep ObjectImport -i , try to import a folder to another informatica repository. I got a object conflicts error for a User-Defined Function. Originally I got control file from PowerCenter help. Then I did research find out you need add

<TYPEOBJECT OBJECTTYPENAME="User Defined Function" RESOLUTION="REPLACE"/>

or replace with all

 <TYPEOBJECT OBJECTTYPENAME="ALL" RESOLUTION="REPLACE"/>

 Hope this help