Sunil S. Ranka's Weblog

Superior Data Analytics is the antidote to Business Failure

Analytics Cache Purge ODBC Extension Functions

Posted by sranka on November 4, 2011

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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: