Application Service Guide > Data Integration Service Management > Data Object Caching
  

Data Object Caching

The Data Integration Service uses data object caching to access pre-built logical data objects and virtual tables. Enable data object caching to increase performance for mappings, SQL data service queries, and web service requests that include logical data objects and virtual tables.
By default, the Data Integration Service extracts source data and builds required data objects when it runs a mapping, SQL data service query, or a web service request. When you enable data object caching, the Data Integration Service can use cached logical data objects and virtual tables.
Perform the following steps to configure data object caching for logical data objects and virtual tables in an application:
  1. 1. Configure the data object cache database connection in the cache properties for the Data Integration Service.
  2. 2. Enable caching in the properties of logical data objects or virtual tables in an application.
By default, the Data Object Cache Manager component of the Data Integration Service manages the cache tables for logical data objects and virtual tables in the data object cache database. When the Data Object Cache Manager manages the cache, it inserts all data into the cache tables with each refresh. If you want to incrementally update the cache tables, you can choose to manage the cache tables yourself using a database client or other external tool. After enabling data object caching, you can configure a logical data object or virtual table to use a user-managed cache table.
To use the Timestamp with Time Zone data type and to enable data object caching for IBM DB2 or for Microsoft SQL Server, set the date time format of the deployed mapping to the "YYYY-MM-DD HH24:MI:SS" format. The Data Integration Service writes the data up to seconds.

Cache Tables

The Data Object Cache Manager is the component of the Data Integration Service that creates and manages cache tables in a relational database.
You can use the following database types to store data object cache tables:
After the database administrator sets up the data object cache database, use the Administrator tool to create a connection to the database. Then, you configure the Data Integration Service to use the cache database connection.
When data object caching is enabled, the Data Object Cache Manager creates a cache table when you start the application that contains the logical data object or virtual table. It creates one table in the cache database for each cached logical data object or virtual table in an application. The Data Object Cache Manager uses a prefix of CACHE to name each table.
Objects within an application share cache tables, but objects in different applications do not. If one logical data object or virtual table is used in multiple applications, the Data Object Cache Manager creates a separate cache table for each instance of the object.

Data Object Caching Configuration

To configure data object caching, configure the cache database connection for the Data Integration Service. Then, enable caching for each logical data object or virtual table that end users access frequently.
Perform the following steps to configure data object caching:
  1. 1. Configure the cache database connection in the cache properties for the Data Integration Service.
    2. The Data Object Cache Manager creates the cache tables in this database.
  2. 2. Enable caching in the properties of logical data objects or virtual tables in an application.
    3. When you enable caching, you can also configure the Data Integration Service to generate indexes on the cache tables based on a column. Indexes can increase the performance of queries on the cache database.

Step 1. Configure the Cache Database Connection

The Data Integration Service stores cached logical data objects and virtual tables in the data object cache database. You configure the connection that the Data Integration Service uses to access the database.
Verify that the database administrator has set up the data object cache database and that you have created the connection to the database.
To configure the connection for the Data Integration Service, click the Properties view for the service in the Administrator tool. Click Edit in the Logical Data Object/Virtual Table Cache area, and then select the database connection name for the Cache Connection property. Restart the service for the property to take effect.

Step 2. Enable Data Object Caching for an Object

To enable caching for an object, stop the application that contains the logical data object or virtual table, edit the object properties, and restart the application.
    1. In the Administrator tool, select the Data Integration Service.
    2. Click the Applications view.
    3. Select the application that contains the logical data object or virtual table for which you want to enable caching.
    4. Stop the application.
    5. Expand the application, and select the logical data object or virtual table.
    6. In the Logical Data Object Properties or Virtual Table Properties area, click Edit.
    The Edit Properties dialog box appears.
    The dialog box contains an Enable Caching checkbox, a Cache Refresh Period property, and a Cache Table Name property.
    7. Select Enable Caching.
    8. In the Cache Refresh Period property, enter the amount of time in minutes that the Data Object Cache Manager waits before refreshing the cache.
    For example, if you enter 720, the Data Object Cache Manager refreshes the cache every 12 hours. If you leave the default value of zero, the Data Object Cache Manager does not refresh the cache according to a schedule. You must manually refresh the cache using the infacmd dis RefreshDataObjectCache command.
    9. Leave the Cache Table Name property blank.
    When you enter a table name, the Data Object Cache Manager does not manage the cache for the object. Enter a table name only when you want to use a user-managed cache table. A user-managed cache table is a table in the data object cache database that you create, populate, and manually refresh when needed.
    10. Click OK.
    11. To generate indexes on the cache table based on a column, expand the logical data object or virtual table.
    1. a. Select a column, and then click Edit in the Logical Data Object Column Properties or Virtual Table Column Properties area.
      2. The Edit Column Properties dialog box appears.
      The dialog box contains a Create Index checkbox.
    2. b. Select Create Index and then click OK.
    12. Restart the application.
    The Data Object Cache Manager creates and populates the cache table.

Data Object Cache Management

By default, the Data Object Cache Manager manages the cache tables in the data object cache database. You can use the Administrator tool or infacmd to configure when and how the Data Object Cache Manager refreshes the cache. Or, you can choose to manage the cache tables yourself using a database client or other external tool.
When the Data Object Cache Manager manages the cache, it inserts all data into the cache table with each refresh. You can choose to manage the cache tables yourself so that you can incrementally update the cache.

Cache Tables Managed by the Data Object Cache Manager

By default, the Data Object Cache Manager manages the cache tables in the data object cache database.
When the Data Object Cache Manager manages the cache tables, you can perform the following operations on the data object cache:
Refresh the cache
You can refresh the cache for a logical data object or virtual table according to a schedule or manually. To refresh data according to a schedule, set the cache refresh period for the logical data object or virtual table in the Administrator tool.
To refresh the cache manually, use the infacmd dis RefreshDataObjectCache command. When the Data Object Cache Manager refreshes the cache, it creates a new cache. If an end user runs a mapping or queries an SQL data service during a cache refresh, the Data Integration Service returns information from the existing cache.
Abort a refresh
To abort a cache refresh, use the infacmd dis CancelDataObjectCacheRefresh command. If you abort a cache refresh, the Data Object Cache Manager restores the existing cache.
Purge the cache
To purge the cache, use the infacmd dis PurgeDataObjectCache command. You must disable the application before you purge the cache.

User-Managed Cache Tables

A user-managed cache table is a table in the data object cache database that you create, populate, and manually refresh when needed.
Configure a logical data object or virtual table to use a user-managed cache table when you want to incrementally update the cache. When the Data Object Cache Manager manages the cache, it inserts all data into the cache table with each refresh. If the source contains a large data set, the refresh can take a long time to process. Instead, you can configure the object to use a user-managed cache table and then use an external tool to insert only the changed data into the cache table. For example, you can use a PowerCenter CDC mapping to extract changed data for the objects and incrementally update the cache.
When you configure an object to use a user-managed cache table, you must use a database client or other tool to create, populate, purge, and refresh the cache table. You create the user-managed cache table in the data object cache database that the Data Integration Service accesses with the cache database connection.
You cannot use the Administrator tool or command line tools to manage a user-managed cache table. The Data Integration Service uses the cache stored in the user-managed cache table when it runs a mapping, an SQL data service query, or a web service request that includes the object. However, the Data Object Cache Manager does not manage the cache table. When you use the Monitor tab to monitor an object that uses a user-managed cache table, the object has a cache state of Skipped.
Note: If the user-managed cache table is stored in a Microsoft SQL Server database and the database user name is not the same as the schema name, you must specify a schema name in the database connection object. Otherwise, mappings, SQL data service queries, and web service requests that access the cache fail.

Configure User-Managed Cache Tables

To configure a logical data object or virtual table to use a user-managed cache table, you must create a table in the data object cache database. Populate the table with the initial cache, and then enter the table name in the data object properties.
Note: Before you configure an object to use a user-managed cache table, you must configure the cache database connection for the Data Integration Service. You also must enable data object caching for the object so that the Data Object Cache Manager creates the default cache table.

Step 1. Find the Name of the Default Cache Table

On the Monitor tab of the Administrator tool, find the name of the default cache table that the Data Object Cache Manager created after you enabled data object caching for the object.
    1. In the Administrator tool, click the Monitor tab.
    2. Click the Execution Statistics view.
    3. In the Navigator, expand a Data Integration Service.
    4. In the Navigator, expand an application and select Logical Data Objects or SQL Data Services.
    5. In the contents panel, perform one of the following steps:
    Details about the selected object appear in the details panel.
    6. In the details panel, select the Cache Refresh Runs view.
    The Storage Name column lists the name of the default cache table that the Data Object Cache Manager created.
    For example, the following image displays a cache table named CACHE5841939198782829781:
    The Cache Refresh Runs view displays details about the default cache table created for the logical data object named LDO_EMP.

Step 2. Create the User-Managed Cache Table

Ask the database administrator to create a table in the data object cache database using the same table structure as the default cache table.
Use a database client to find the default cache table in the data object cache database. Use the SQL DDL from the default cache table to create the user-managed cache table with a different name. The name of the user-managed cache table cannot have the prefix CACHE. The prefix CACHE is reserved for names of cache tables that are managed by the Data Object Cache Manager.
After creating the user-managed cache table, populate the table by copying the initial cache data from the default cache table.

Step 3. Configure the Object to Use the User-Managed Cache Table

To configure a logical data object or virtual table to use a user-managed cache table, stop the application that contains the object, edit the object properties, and restart the application.
    1. In the Administrator tool, select the Data Integration Service.
    2. Click the Applications view.
    3. Select the application that contains the logical data object or virtual table for which you want to use a user-managed cache table.
    4. Stop the application.
    5. Expand the application, and select the logical data object or virtual table.
    6. In the Logical Data Object Properties or Virtual Table Properties area, click Edit.
    The Edit Properties dialog box appears.
    7. Enter the name of the user-managed cache table that you created in the data object cache database.
    When you enter a cache table name, the Data Object Cache Manager does not generate the cache for the object and ignores the cache refresh period.
    The following figure shows a logical data object configured to use a user-managed cache table:
    The dialog box displays a user-managed cache table name entered for the Cache Table Name property.
    8. Click OK.
    9. Restart the application.