Siebel Analytics version 7.7 provided three ODBC extension functions for purging query cache entries: one to purge by query, one to purge by physical table, and one to purge all cache entries in the entire query cache. The subsections below describe each of the three functions.
Even though its not document as part of 11g, but we have implemented at one of the client and it worked as is (Please use at your own risk)
Purge by query
The table below formally defines the ODBC extension function for purging a cache entry associated with a specified query:
Usage: Call SAPurgeCacheByQuery (?)
Input Arguments:
| Argument Number |
Column Name |
Data Type |
Data Size |
Nullable |
| 1 |
SQL Query |
SQL_C_CHAR |
64 KB |
No |
Output result set: None
SAPurgeCacheByQuery will purge a cache entry that exactly matches the logical query plan derived from a specified query. This function takes one, and only one, parameter representing query text.
For example, suppose you have a query cache entry generated from the following query that retrieves the names of all employees earning more than $100,000:
select lastname, firstname from employee where salary > 100000;
The call below programmatically purges the cache entry associated with this query:
Call SAPurgeCacheByQuery('select lastname, firstname from employee where salary > 100000' );
Purge by table
The table below formally defines the ODBC extension function for purging a cache entry associated with a physical table:
Usage: Call SAPurgeCacheByTable(?,?,?,?)
Input Arguments:
| Argument Number |
Column Name |
Data Type |
Data Size |
Nullable |
| 1 |
Database Name |
SQL_C_CHAR |
128 KB |
Yes |
| 2 |
Catalog Name |
SQL_C_CHAR |
128 KB |
Yes |
| 3 |
Schema Name |
SQL_C_CHAR |
128 KB |
Yes |
| 4 |
Table Name |
SQL_C_CHAR |
128 KB |
Yes |
Output result set: None
SAPurgeCacheByTable will purge 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 of a fully qualified physical table name: database, catalog, schema and table name proper.
For example, suppose that you wish to purge the cache entries associated with a table having the following fully qualified name in the physical layer of the Siebel Analytics repository:
DBName.CatName.SchName.TabName
The call below programmatically purges the cache entries associated with this physical table:
Call SAPurgeCacheByTable( 'DBName', 'CatName', 'SchName', 'TabName' );
Purge All
The table below formally defines the ODBC extension function for purging all entries in the entire query cache:
Usage: Call SAPurgeAllCache()
Input Arguments: None
Output result set: None
The call below illustrates the function’s usage:
Call SAPurgeAllCache();
Invoking ODBC Extension Functions
You can call these functions using the nqcmd.exe command-line executable.
The syntax of the call will be as follows:
nqcmd -d "Analytics Web" -uadministrator -psadmin -spurge.txt
where purge.txt contains the call (for example, call SAPurgeAllCache()).
Below is sample file I wrote, which will take all the txt files and run against the weblogic server. Doing this you can seed multiple file using one batch file.
@echo off
setLocal EnableDelayedExpansion
cd %1
FOR /F "TOKENS=1* DELIMS= " %%A IN ('DATE/T') DO SET CDATE=%%B
FOR /F "TOKENS=1,2 eol=/ DELIMS=/ " %%A IN ('DATE/T') DO SET mm=%%B
FOR /F "TOKENS=1,2 DELIMS=/ eol=/" %%A IN ('echo %CDATE%') DO SET dd=%%B
FOR /F "TOKENS=2,3 DELIMS=/ " %%A IN ('echo %CDATE%') DO SET yyyy=%%B
SET date1=%mm%%dd%%yyyy%_%time:~0,2%%time:~3,2%%time:~6,2%%
SET date2=%mm%%dd%%yyyy%_%time:~1,2%%time:~3,2%%time:~6,2%%
Set TDate=%time:~0,2%%time:~3,2%%time:~6,2%%
SET date3=%mm%%dd%%yyyy%
for /f "tokens=* delims= " %%a in ('dir/b *.txt') do (
set str=%%a
"D:\OBIEE11g\Oracle_BI1\bifoundation\server\bin\nqcmd.exe" -d coreapplication_OHXXX -u <username> -p <passwd> -s !str! -o !str!.out.!date3!
)
Hope This helps.
Sunil S Ranka
“Superior BI is the antidote to Business Failure”
(Part of the text has been taken from Oracle Tech Note)
Like this:
Like Loading...
