Purging Cach Programtically

Purging Cache Programmatically

---

The Analytics Server provides ODBC-extension functions for purging cache entries programmatically. These functions are particularly useful for embedding in an Extract, Transform, and Load (ETL) task. For example, after a nightly ETL is performed, all Analytics 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 administrators have the right to purge cache. Therefore scripts that call these ODBC-extension functions must run under an 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 programmatically 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 analytics repository, execute the following call in a script:
  Call SAPurgeCacheByTable( 'DBName', 'CatName', 'SchName', 'TabName' );
  NOTE:  Wild cards are not supported by the Analytics 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 is an example of this call:
  Call SAPurgeCacheByDatabase( 'DBName' );

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:

Result Code	Result Message
1	SAPurgeCacheByDatabase returns successfully.
E_Execution_CacheNotEnabled	Operation not performed because caching is not enabled.
E_Execution_NonExistingDatabase	The database specified does not exist.