Business Intelligence – Oracle

Oracle BI EE – Scheduling Cache Purging

Posted by Venkatakrishnan J on December 18, 2007

One of the common questions that i typically get while on customer calls is how do we about purging the cache on a regular basis. Well this blog entry would give you a step by step approach for doing the same. This is just an example. You can customize this to your needs. Before going into the actual procedure lets look at an easier approach of purging the caches. If you go to the Admin tool you would basically find a cache Manager under Manager. If you open the cache manager you would find all the Logical SQL statements that have been cached. You can purge each of these one by one or you can do a Purge All.




Now lets look at automating the above process. In order to automate this we shall be using a utility called NQCMD. What this utility can do is it can accept ODBC sql calls and can execute them. So, our aim is to call this utility from an ibot via a java script. Lets first look at the syntax of NQCMD. If you go to {OracleBI}\Server\bin via command line and type in nqcmd/?, you would get the syntax as shown below.


So, basically it accepts the following important parameters

   1.   Username
   2.   Password
   3.   BI Server DSN
   4.   Input file containing the Purge SQL Statement
   5.   Output result file

Now, lets look at the list of steps to purge the cache one by one.

1.   Open a text editor and type in the following command. This command will be purging the cache.


{call SAPurgeAllCache()};

Save this file in say D drive as SQL.txt. This will be our input file.


2.   Lets start creating a simple javascript. Open a text editor and enter the following list of statements.

var nqCmd = “D:\\Oracle\\OracleBI\\server\\Bin\\nqcmd.exe”;
var dsn = “AnalyticsWeb”;
var user = “Administrator”;
var pswd = “Administrator”;
var tempInFileName = “D:\\SQL.txt”;
var tempOutFileName = “D:\\Output.txt”;
var wshShell = new ActiveXObject(“WScript.Shell”);
var dosCmd = nqCmd + ” -d \”” + dsn + “\” -u \”” + user+ “\” -p \”” + pswd + “\” -s \”” + tempInFileName + “\”” +” -o \”” + tempOutFileName + “\””;
wshShell.Run(dosCmd, 0, true);
throw e;

Save this file in {OracleBI}\Server\Scripts\Common as “Purge.js”. As you see above what this java script does it calls the nqcmd utility by passing in the relevant arguments.


3.   Now go to BI EE Delivers and start creating a simple ibot. Go to the advanced section and enter the details of the Java Script that we created. In the schedules section choose the frequency with which you would like to run this script. For testing purposes lets test this by running the script immediately


4.   Once this is done save the ibot. Now you can see that all the cache would have been purged and also you can see how much have been purged from the output.tx file.


8 Responses to “Oracle BI EE – Scheduling Cache Purging”

  1. Shaurya said

    Hi Venkat,

    When I try to save the iBot, it throws following error message:

    Oracle BI Scheduler Error: [nQSError: 68019] Authentication Failed.
    Error Details
    Error Codes: GYFPI8RN

    Also, checked the support web for Resolution but it’s not very clear.
    It says, Once cryptotools had been rerun with the Repository username and password and the DSN pointing to their Oracle tnsnames entry, the user was able to save their iBot.

    I believe that SR was raised by you only.
    Anyways, please share how to get around this.


  2. tapasvi said

    Works like a charm ! Thanks !

  3. Qinghe said

    Hi Venkat,

    Could you make this technique ‘purging on demand’ through a web link? In other words, is it possible to just click a link on My Dashboard and the link will call nqcmd.exe with related parameters to purge caches in that remote machine?


  4. […] by Venkatakrishnan J on February 11, 2008 If you had seen my previous blog entry here, i would have shown you a procedure to purge the cache on a periodic basis using Java Script. I was […]

  5. Nick said

    Hi venkat,

    Thanks for the posting. I have tried the samething as you said. But the Js does not work, I am not sure what I am doing wrong. I know the authentication is correct. And I can run a dos batch like:

    C:\OracleBI\server\Bin\nqcmd.exe -d AnalyticsWeb -u Administrator -p Administrator -s c:\Purge.txt -o C:\Output.txt

    It works fine, but when I use the js, even by clicking on it, it does not work. Can you please help?


  6. Alexander Donchev said

    Does anyone know how to create a scheduler for purging cache in Tuxedo 8.1 or Weblogic 8.1?

    Thank you!!

  7. devendra said

    Hi Venkat ,

    nice post..
    I tried but its now working. We have two dedicated server for BI Server and BI Presentation server.

    can we put BI server path in .js file ?

    can you please help me out..Its urgent


  8. Stefan said

    Hi there!

    Sorry, maybe HERE can someone help me:

    We are running Oracle BI under UNIX.
    If I am right there is no AdminstratorTool under unix available?!

    How is it possible to purge the complete cache of the BI server?

    Thank you!
    Best regards,

Leave a Reply to Nick Cancel reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: