Purging the Cache using ODBC Procedures

Purging the Cache using ODBC Procedures:

The Oracle BI Server provides ODBC-extension functions for Purging the cache entries.

Only administrators have the right to purge the cache. Therefore, scripts that call these ODBC-extension functions must run under credentials with administrator privileges.

The following ODBC functions affects the cache entries that associated with the repository specified by the ODBC functions

1. SAPurgeAllCache:Purges all cache entries

Ex: SAPurgeAllCache();

2. SAPurgeCacheByDatabase: Purges all cache entries associated with a specific physical database name. A record is returned when any of the ODBC procedures to purge the cache are called. This function takes one parameter that represents the physical database name, and the parameter cannot be null.

Ex: Call SAPurgeCacheByDatabase( 'DBName' );

3. SAPurgeCacheByTable:Purges all cache entries that are 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 that represent the four components (database, catalog, schema, and table name proper) of a fully qualified physical table name.

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

4. SAPurgeCacheByQuery: Purges cache entries that exactly match a specified query. For example, using the following query, you would have one or more query cache entries that retrieve the names of all employees earning more than $100,000:

Ex: Call SAPurgeCacheByQuery('SELECT lastname, firstname FROM employee WHERE salary > 100000' );

1. Calling Functions using nqCmd:

1. Create a purge script file and save

2. Open a cmd line editor and go the below shown path

3. Execute below command

2. Calling ODBC Functions from analytics editor:

1. Login to analytics editor and select Administration
2. In Maintenance and Troubleshooting section select Issue SQL

3. Write the odbc function and click issue sql