When taking a backup of a DB2 LUW database, there are a lot of options to choose from. I like to specify as many as possible to make it clear exactly what I want, even if I am specifying the defaults. Though I very rarely specify the number or size of backup buffers, as DB2 does a decent job for me there.

The Problem

Sometimes the options I specify are incorrect. Most commonly, this happens when I specify online options for an offline backup. SQL2032 usually means you specified an option for the backup that is not valid for that backup type. Usually, I get it when I specify this syntax:

> db2 force applications all; db2 deactivate db sample; db2 backup db sample to /db2fs/backups compress include logs without prompting
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.

DB20000I  The DEACTIVATE DATABASE command completed successfully.
SQL2032N  The "iOptions" parameter is not valid.

The problem here is that I have specified the “include logs” keywords on an offline backup. Include logs is the default after DB2 8.2, and is intended for online backups to include the log files that were written to during the duration of the backup so that a rollforward to a minimum point in time is possible. Prior to DB2 8.2, if you did not either include these logs or manage their retention somewhere else, you could have a backup image that you were unable to restore because you did not have the log files for the minimum point in time recovery. Because of this, I am programmed to include these keywords. However, for an offline backup, there is no database activity while the backup is occuring, so there are no log files to include. Thus DB2 does not allow you to specify them in the backup command.

The Solution

The solution here is easy – just specify valid backup syntax. In this case, I simply change my backup syntax to this:

> db2 force applications all; db2 deactivate db sample; db2 backup db sample to /db2fs/backups compress without prompting

And the backup is able to run and complete successfully.