Skip to content

Monthly Archives: March 2009

DB2 LUW CLP – how to rollback transaction using +c -s -f options


Objective: You have a set of SQL DML (or other, such as DDL) statements that you want to run as a file using the CLP db2 -f filename option. If any of the statements in the file fail, you want processing of the -f file to end, every statement executed successfully (but not yet committed) prior to the fail to be rolled back. How do you do this?

The short answer is to avoid any connection state changes in the -f file (and obviously any explicit COMMIT’s). Anything that closes the connection will commit all changes up to that point, regardless of what auto-commit behavior you have designated for the session. If you remain connected at the end of processing a file using the +c -s -t -f options, you can explicitly ROLLBACK the transactions that succeeded before the one that failed. Ascertain that your CLP -f file has no QUIT, CONNECT RESET or TERMINATE statement.

A NetBackup for DB2 Cheat Sheet


This post is a cheat sheet for most of what I think the DB2 database administrator needs to know about Symantec NetBackup as a DB2 backup, recovery and log archival utility. My notes make version and platform assumptions of Unix DB2 version 9 and above, and NetBackup 6.0.

I wrote this because you don’t need to know that much to get NetBackup working for DB2, and the Symantec docs are very unclear and not well written. With that being said, if you get into a jam you will need to read the Symantec docs, and probably call their support. As I discovered, not everything is documented. But NetBackup DB2 Agent version 6.0 seems to work quite well most of the time.