Oracle^® Business Intelligence Scheduler Guide > Using Oracle BI Scheduler Job Scripts >

Job Manager Script for Cache Clearance

This topic provides an example of setting up a script for Oracle BI Scheduler. Oracle BI Scheduler can be used for general purpose scripts that extend the functionality of Oracle Oracle Business Intelligence.

The script purgeSASCache.js is used to periodically purge all of the cache from the Oracle BI Server:

/////////////////////////////////////////////////////////
// purgeSASCache.js
//
// Purges the cache on SAS.
// Parameter(0) - The user name to pass in to NQCMD.
// Parameter(1) - The password for the aforementioned user.
/////////////////////////////////////////////////////////

// The full path to nqcmd.exe
var nqCmd = "[$INSTALLDIR]\\server\\Bin\\nqcmd.exe";

// The data source name
var dsn = "BI Web";

// The user to execute the queries
var user = Parameter(0);

// The password of the aforementioned user
var pswd = Parameter(1);

// The ODBC procedure call for purging the cache
var sqlStatement = "{call SAPurgeAllCache()};";

//////////////////////////////////////////////////////////
// Returns a string from the file name
//////////////////////////////////////////////////////////

function GetOutput(fso, fileName)
{
    var outStream = fso.OpenTextFile(fileName, 1);
    var output = outStream.ReadAll();
    outStream.Close();
    return output;
}

//////////////////////////////////////////////////////////
// Get WshShell object and run nqCmd. Capture the output
// so that we can handle erroneous conditions.

var wshShell = new ActiveXObject("WScript.Shell");

// Create a temp file to input the SQL statement.

var fso = new ActiveXObject("Scripting.FileSystemObject");

var tempFolder = fso.GetSpecialFolder(2);
var tempInFileName = fso.GetTempName();
var tempOutFileName = fso.GetTempName();
tempInFileName = tempFolder + "\\" + tempInFileName;
tempOutFileName = tempFolder + "\\" + tempOutFileName;
var tempInFile = fso.CreateTextFile(tempInFileName, true);
tempInFile.WriteLine(sqlStatement);
tempInFile.Close();

try
{

    // execute
    var dosCmd = nqCmd + " -d \"" + dsn + "\" -u \"" + user
        + "\" -p \"" + pswd + "\" -s \"" + tempInFileName + "\"" +
        " -o \"" + tempOutFileName + "\"";

    wshShell.Run(dosCmd, 0, true);

    var output = GetOutput(fso, tempOutFileName);

    // Remove the temp files
    fso.DeleteFile(tempInFileName);
    if (fso.FileExists(tempOutFileName)) {
        fso.DeleteFile(tempOutFileName);

}

    // Check the output for any errors
    if (output.indexOf("Processed: 1 queries") == -1) {
        ExitCode = -1;
        throw Error(-1, output);
    }

    else if (output.indexOf("Encountered") != -1) {
        ExitCode = -2;
        throw Error(-2, output);
    }
        ExitCode = 0;
} catch (e) {
    if (fso.FileExists(tempInFileName)) {
        fso.DeleteFile(tempInFileName);
    }

    if (fso.FileExists(tempOutFileName)) {
        fso.DeleteFile(tempOutFileName);
    }
    throw e;

}