ODI Knowledge Modules often use temporary objects to:
- stage temporary data for jobs optimization.
- execute some rules
These temporary objects are always stored in a particular schema or directory called the Work Schema (or staging area).
These work schemas should be kept separate from the schema containing the application data (Data Schema).
- when the source is a flat file, and as such is not associated to an engine that supports operations (as concatenation, uppercase, …)
- all components (except the triggers) of the journalizing infrastructure (like all Data Integrator temporary objects, such as integration, error and loading tables)
Objects and work tables prefix
On the target data servers, you should almost certainly create a dedicated work schema. This will store:
- Temporary tables and views created during the loading phase if the source data is not in the same server as the target (C$ tables)
- Temporary tables created during the integration phase when using certain Integration Knowledge Modules (I$ tables)
- Permanent Error tables created during Data Quality Control phase when using Check Knowledge Modules (E$ tables)
On “read-only” source data servers the following objects may be created depending on the way you want to address these servers:
- Views during Load phases, when using certain Loading Knowledge Modules
- Error tables when performing a “static” Data Quality Control (e.g. when controlling the data of a particular source schema independently of any load job)
- Journal tables, views and triggers when using Change Data Capture on the sources.