A MaxL quickie to unload those databases that are eating your precious memory

Due to various business requirements, some organizations end up archiving many of their cubes each year.  For example, if you have a huge Measures dimension that is constantly changing (even in subtle ways), but you need to be able to go back at some point in the future and see what some numbers looked like at some particular point in time, you might find yourself spinning off a copy of the cube that sits on the server.  Most of the time it just sits there, dormant, not being used.  But every now and then someone comes along and spins it up so they can refresh some obscure report.  It’s got a decent sized outline to it, so the overhead just on having this cube running is probably in excess of 50 megs of memory, just to sit there!  If you aren’t rebooting your servers that frequently, that app and database are just going to sit there until someone comes along and stops it.  This might not be a problem for your current situation, but for this particular server, there are over two-hundred apps available at any given time — and RAM is a finite resource.

Now, we could just setup a job to unload everything — and indeed, that’s part of the solution.  But I like to keep the core apps hot so they don’t have to spin up when people (or myself) login.  So what to do?  Create a whitelist of core apps and databases and write a little MaxL to unload everything, then start just the things I want.

For the purposes of brevity, I will just assume that connect.msh has a valid login statement in it.  Then the code to unload the apps (unloadall.msh) is pretty straightforward (spool to some output file as needed…):

msh “connect.msh”;
alter system unload application all;
logout;
exit;

Then we have a script file that starts up a specific app/db passed on the command line (startappdb.msh):

msh “connect.msh”;
alter application $1 load database $2;
logout;
exit;

So, then we have a simple text file with the list of each app/database combination to fire up (whitelist.txt):

App1 Db1
App2 Db2

Then, in a simple Windows batch file we could do the following (which I imagine you could port pretty easily to whatever platform/scripting combination you have):

essmsh unloadall.msh
FOR /f “eol=; tokens=1,2 delims= ” %%i in (whitelist.txt) do essmsh startappdb.msh %%i %%j

That’s it!  Dead simple, but effective.  The FOR in batch is basically broken out as follows: eol is the end-of-line or comment character (which we aren’t using in the data file in this instance), the first and second fields are broken down into %i and %j, and the delimiter between them is a space.  Then we call the script that will start it up (named startappdb.msh, passing along the App and Db).  There are many ways you could do this (passing commands on the command line itself) but this method to me is clean and simple.