Skip to content

Tag Archives: db2

Leveraging DB2 Snapshot Administrative Views

27-May-09

This article will show the DB2 LUW version 9 administrator how to use Unix shell scripts to build a home-grown database performance history system without resort to third-party tools. Shell scripts are used to automate performance capture and provide 24×7 monitoring support. The author also provides 40 views representing calculated common metrics of the resulting performance history data.

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

30-Mar-09

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.