Online backup of the System Store (Derby/Cloudscape)

Follow these steps to build an automated System Store backup utility. Note that your System Store must be set up for Network mode.

1. Create a new AL (call it "BackupSystemStore")


2. Add a JDBC Connector (call it "Derby") in AddOnly mode and put it in Passive state.


3. Configure the JDBC Connector to point at the System Store. The easiest way to do this is to click on the label of the JDBC URL parameter. In the resulting dialog, use the Expression drop-down to choose the com.ibm.di.store.database property. Or you could just enter this into the Expression field:
   
   {property.Solution-Properties:com.ibm.di.store.database}
   
   Now the JDBC URL parameter will be dynamically configured using the named property from your Solution-Properties (solution.properties). This is the same property TDI uses when working with its System Store database.


4. Repeat step 3 for the other mandatory parameters:
   
   
   
   • JDBC Driver - com.ibm.di.store.jdbc.driver
   
   
   • Username - com.ibm.di.store.jdbc.user
   
   
   • Password - com.ibm.di.store.jdbc.password
   
   Since the Connector is not in Iterator mode, we don't have to set Table Name (i.e. no selectEntries).


5. Add a Script component to the AL with this code:
   

   
   // Make sure the path uses forward "/" and ends with one
   // as required by SQL syntax.
   //
   function sqlPath( pth ) {
      pth = system.mapString(pth, "\\", "/");
      if (!pth.endsWith("/"))
         return pth + "/"
      else
         return pth;
   }
   
   // Here is the function for backuping up the System Store
   // to the specified directory.
   //
   function backupDB( backupdir ) {
      // Get today's date as a string:
   
      var todaysDate =  new java.text.SimpleDateFormat("yyyy-MM-dd");
      var backupdirectory = sqlPath(backupdir) +
                 todaysDate.format((java.util.Calendar.getInstance()).getTime());
   
      task.logmsg("Backuping Derby DB to " + backupdirectory + "...");
   
      res = Derby.getConnector().execSQL("CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('" +
                                            backupdirectory + "')");
   
   
      if (res == null || res == "")
         task.logmsg("Done!")
      else
         task.logmsg("** Error: " + res);
   }
   
   // Here the function is called to perform the backup
   // to C:/_Backup/ (which is created automagically the
   // first time).
   //
   backupDB("C:\\_Backup\\");
   

Now you can set up a Scheduler AL with the Timer Connector to periodically call your BackupSystemStore AssemblyLine and make a fresh backup.

Thanks to Boli of Ascendant for the link that made my day :) Backing up Derby databases. This is from the admin guide found with the other Online Derby docs.