Oracle BI EE 10.1.3.3/2 – 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 + “\””;
try
{
wshShell.Run(dosCmd, 0, true);
}
catch(e)
{
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.
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.
Thanks.
Shaurya
tapasvi said
Works like a charm ! Thanks !
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?
Thanks
Qinghe
Oracle BI EE 10.1.3.3/2 - Scheduling Cache Purging - Phase 2 - Using Java and Delivers « Business Intelligence - Oracle said
[…] 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 […]
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?
Thanks.
Alexander Donchev said
Hello,
Does anyone know how to create a scheduler for purging cache in Tuxedo 8.1 or Weblogic 8.1?
Thank you!!
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
Thanks,
Devendra
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,
Stefan