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 =