Monday, February 22, 2010

OBIEE: Call SAPurgeAllCache()

Just a note to myself on how to clear the entire BI Server cache.

Mr. Minkjan has a great article here and Gerard Nico has one here.

Caching has been enabled (via <BI_SERVER_HOME>\server\Config\NQSConfig.ini)

For a list of ODBC Function extensions, read here.

First up, I run a simple report and then verify that there is an entry in the cache



I then create a small SQL script and put it in my c:\ folder:



(OK, just ignore what it says in the Title bar, I moved it)

Open up a cmd prompt and issue the following:
nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s purgeallcache.sql
Hit enter and you should see the following:
-------------------------------------------------------------------------------
Oracle BI Server
Copyright (c) 1997-2009 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------

CALL SAPurgeAllCache()

CALL SAPurgeAllCache()

------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------
RESULT_CODE RESULT_MESSAGE

------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------
1 [59118] Operation SAPurgeAllCache succeeded!

------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------
Row count: 1
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------

Processed: 1 queries
Check the Cache Manager and voila!



For future reference, here are the options available for the nqcmd utility:
Command: nqcmd - a command line client which can issue SQL statements
against either Oracle BI server or a variety
of ODBC compliant backend databases.
SYNOPSIS
nqcmd [OPTION]...
DESCRIPTION
-d
-u
-p
-s
-o
-D
-C<# number of fetched rows by column-wise binding>
-R<# number of fetched rows by row-wise binding>
-a (a flag to enable async processing)
-f (a flag to enable to flush output file for each write)
-H (a flag to enable to open/close a request handle for each query)
-z (a flag to enable UTF8 instead of ACP)
-utf16 (a flag to enable UTF16 instead of ACP)
-q (a flag to turn off row output)
-NoFetch (a flag to disable data fetch with query execution)
-NotForwardCursor (a flag to disable forwardonly cursor)
-SessionVar =

No comments: