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.

Buried treasure

A favored pasttime of my youth was treasure hunting. Whether it was scouring a hillside for Easter eggs, rummaging in the attic of my grandparents' house or just cleaning out the pockets of my jeans. I have this same, if somewhat unusual relationship to troubleshooting error messages from TDI. The secret is knowing where to dig.

The first thing you need to do is scan your log output for the very first error message and stack dump, which will be the root cause of the failure. Subsequent error message are likely to be related to this initial fault.

Once you've found the first error then you can start digging. This is done by first splitting the text of the message into two parts: what TDI is reporting, and what the underlying library or system is complaining about. For example, TDI could be giving you a standard message like:

CTGDIJ001E
No default JDBC driver. The 'jdbcDriver' parameter must be set to use the JDBC Connector.

Here the entire prose of the error message comes from TDI. In this case there is no data source involved (yet) since the Connector itself can't be initialized without the JDBC Driver parameter being set correctly. These are also the types of errors that Google may be less than helpful for, and your best bet may be the discussion forum, the community website or the online docs.

To illustrate the type of message that a net search can help you decipher, let's look at a message I helped debug last week (formatted in bold and italics to illustrate my point):


10:12:03 [DB2_Update] CTGDIS810E handleException - cannot handle exception , initialize

Unable to obtain schema: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -443, SQLSTATE: 38553, SQLERRMC: SYSIBM.SQLCOLUMNS; COLUMNS;SYSIBM:CLI:-805

In a case like this where the exception originated in a call to some driver or API, the first part of the message (italicized above) will help you find the point in your AssemblyLine where the failure occurred.

At the start of the error message is the name of an AssemblyLine component in brackets: [DB2_Update]. This tells you where the error occured in your AL. Immediately following the component name we can furthermore see TDI telling us that this unhandled exception occurred during initialization of the component. So far so good: we know where and when now. The next step is to discover why we are getting this exception.

That is where the text that I've formatted as bold comes in: this is the error that TDI received from the underlying RDBMS. Since it is a DB2 error (not specific to TDI) there is a much greater chance that someone else has fixed and documented this already.

So when I did a Google search with tidbits gleaned from this part of the message (e.g. "sqlcode -443 sqlstate 38553 cli -805") I uncovered plenty of relevant links, eventually leading me to a newgroup post that described a bind problem easily fixed with "bind db2schema.bnd". The search was over, the problem solved and I am ready for new adventures.