Enabling Cache Data Storage to Multiple Data Sources
For each eligible data source, you can specify whether you want the cache status and tracking data stored on that data source or on another eligible data source. Typically this type of configuration allows an administrator to manage all the tracking and status tables. When using multiple data sources for caching, the cache tracking and cache storage tables stay together on the same data source.
Requirements
• The cache data source and the data source that holds the cache status and tracking tables must be owned by the same user.
• The cache policy requires the use of the same cached data source for status and tracking tables.
• System data sources, such as the file cache or default cache) are not eligible to hold the cache status and tracking tables.
• The data source being used as the cache data source is not eligible to hold the status and tracking tables when attempting to enable this solution.
To enable caching to multiple data sources
1. In Studio, open the data source that you want to use to store the status and tracking tables for your cache. For example, Oracle11.
2. Under Data Source Cache Configuration, select Current data source.
3. For the Status Table and Tracking Table fields, select Browse and browse to existing tables that you want to use or browse and create new tables to hold the data.
4. Save the data source configuration.
5. Open the data source that you want to use to store your cache data. For example, ds_orders.
6. Under Data Source Cache Configuration, select Select a different data source.
7. Browse to locate and specify the data source where you want to store cache data. For example, Oracle11.
8. Verify that the tables indicated in the Status Table and Tracking Table fields are the ones you want to use.
9. Optionally, open the data source and select other status and tracking tables.
Click Open Data Source. In the Caching section of the Configuration tab, create (execute the DDL), rename or browse to two tables, one for storing cache status data (Status Table) and the other for storing cache tracking data (Tracking Table).
10. Save the data source configuration.
11. Open the table or view resource for which you want to cache data.
12. Select the caching tab.
13. Select Create Cache.
14. Under Storage, select Single Table or Multi-table.
15. For the Data Source field, Browse to the data source which you want to use to hold the cache data. For example, ds_orders.
16. Enable the cache.
17. Save the data source configuration.
18. Refresh the which you want to use to hold the cache data. For example, ds_orders.
19. Enable the cache.
20. Save the data source configuration.
21. To test your configuration, select Refresh Now to populate the cache tables. Open the cache data, cache status and cache tracking tables, validate that each of them contains data and have timestamps that are consistent with the time that you ran the latest cache refresh.