Oracle^® Business Intelligence Server Administration Guide > Query Caching in the Oracle BI Server >

Purging and Maintaining Cache Using ODBC Procedures

The Oracle BI Server provides ODBC-extension functions for the Oracle BI Administrator to use for purging cache entries.

Some of these functions are particularly useful for embedding in an Extract, Transform, and Load (ETL) task. For example, after a nightly ETL is performed, all Oracle BI Server cache can be purged. If only the fact table was modified, only cache related to that table can be purged. In some cases, you might need to purge the cache entries associated with a specific database.

NOTE:  Only Oracle BI Administrators have the right to purge cache. Therefore scripts that call these ODBC-extension functions must run under an Oracle BI Administrator logon ID.

The following ODBC functions affect cache entries associated with the repository specified by the ODBC connection:

• SAPurgeCacheByQuery. Purges a cache entry that exactly matches a specified query. For example, using the following query, you would have a query cache entry that retrieves the names of all employees earning more than $100,000:

  select lastname, firstname from employee where salary > 100000;

  The following call purges the cache entry associated with this query:

  Call SAPurgeCacheByQuery('select lastname, firstname from employee where salary > 100000' );

• SAPurgeCacheByTable. Purges all cache entries associated with a specified physical table name (fully qualified) for the repository to which the client has connected.

  This function takes up to four parameters representing the four components (database, catalog, schema and table name proper) of a fully qualified physical table name. For example, you might have a table with the fully qualified name of DBName.CatName.SchName.TabName. To purge the cache entries associated with this table in the physical layer of the Oracle BI repository, execute the following call in a script:

  Call SAPurgeCacheByTable( 'DBName', 'CatName', 'SchName', 'TabName' );

  NOTE:  Wild cards are not supported by the Oracle BI Server for this function. Additionally, DBName and TabName cannot be null. If either one is null, you will receive an error message.

• SAPurgeAllCache. Purges all cache entries. The following is an example of this call:

  Call SAPurgeAllCache();

• SAPurgeCacheByDatabase. Purges all cache entries associated with a specific physical database name. A record is returned as a result of calling any of the ODBC procedures to purge the cache. This function takes one parameter that represents the physical database name and the parameter cannot be null. The following shows the syntax of this call:

  Call SAPurgeCacheByDatabase( 'DBName' );

About Sharing Presentation Server Cache

When users access the Intelligence Dashboard to run queries, Oracle BI Presentation Services caches the results of the queries. Oracle BI Presentation Services uses the request key and the logical SQL string to determine if subsequent queries can use cached results. If the cache can be shared, subsequent queries are not stored.

• SAGetSharedRequestKey. An ODBC procedure that takes a logical SQL statement from the Oracle BI Presentation Services and returns a request key value.

  The following shows the syntax of this procedure:

  SAGetSharedRequestKey('sql-string-literal)

About Result Records

The result record contains two columns. The first column is a result code and the second column is a short message describing result of the purge operation. The following list contains examples of result records: