My simplistic testing suggests that a statement like

UNLOAD FROM TABLE MyVeryBigTable TO 'nul';

results in all the rows being read - and therefore ending up in the cache, but no disk activity. Is this correct or is there some big catch I'm not spotting?

Background: There have been a number of questions over the years to do with cache warming like this one, but basically everyone one is searching for the "holy grail" of the database that runs as fast after you've restarted it as it did before you stopped it. The old advice was - "Well, don't stop it then!" but this is becoming more of a problem with the ever more frequent Windows updates.

SAP have added some functionality that can help, such as the default cache warming process that reloads the pages loaded when the database was last started, and the new v17 option that allows you to manually chose a point in time to record what is in the cache and have that reloaded. Neither of these quite hits the spot - the new feature being dependent on users choosing a good time to do it and the data not changing much.

A lot of of customers try to address this problem by running big reports etc after a restart - but have to do it manually or set up something to schedule it. Memory is now pretty cheap and for most customers we tend to have pretty well as much RAM as database - so a more lavish approach seems possible.

The idea would be to have an event triggered by database start up that just executes the above unload for all the important tables in the database, omitting audit trails, blob data etc that don't impact normal query performance. The first plan was to force an unload to file, or possibly a variable, that was then discarded, but there are dangers of running out of disk space or exceeding the 2GB limit on variables. The nul device approach would seem to be safer and also more efficient.

UPDATE

We ended up using the UNLOAD SELECT method. We also put in a check on ISSERVER so that the process isn't invoked when the database is being started manually or by batch file for things like applying logs etc.

CREATE EVENT "adminuser"."CacheWarm" TYPE "DatabaseStart"
HANDLER
BEGIN 
IF PROPERTY  ( 'IsService' ) = 'Yes' THEN 
    MESSAGE string('Cache Warming Started at ',current timestamp) TO CONSOLE;
    UNLOAD select * from AnImportantTable TO 'nul';
    .... lots more tables
    MESSAGE string('Cache Warming Completed at ',current timestamp) TO CONSOLE;
END IF;
END