How to trace sql commands for a specific connection to db2?

Question

I am in the process of setting up a central build server. The server is responsible to produce the official build artifacts that will be deployed to all environment. For one of the applications there is an build step that writes to a database. At deploy time we would need to run this build step to the appropriate environment. Since it is difficult/expensive to reliably reproduce this build step later the idea was born to capture the sql run just the sql script later at deployment time.

I have no control over the source code that generates the sql. It is an java program that uses hibernate. I haven't found out yet, where to put the log4j.properties/log4j.xml to use the hibernate trace functionality. And there might be a problem with prepared statements showing '?' instead of the actual value.

Does anyone have experience with Activity Monitor of DB2 and how to use it? Or is there other (easier) options like replacing the db2 driver in the hibernate configuration? I still want to use the DB2Dialect driver, though.

EDIT: I don't have control over the database structure. The database structure might be updated or the process might write to different tables in the future. This should not impact the solution I am setting up now.

Answer 1

If the table is non-existent / empty before is id filled, you could also export the resulting table including records and import that into you production database at deploy time.

As you already said, it might prove impossible to determine the contents of the tables using a log of actions performed. Logging prepared statements will not contain the bound data and if more than simple inserts are executed you might be forced to implement parts of SQL to reproduce what you recorded.

Answer 2

Have a look at p6spy:

P6Spy is an open source framework to support applications that intercept and optionally modify database statements. The P6Spy distribution includes the following modules:

P6Log. P6Log intercepts and logs the database statements of any application that uses JDBC. This application is particularly useful for developers to monitor the SQL statements produced by EJB servers, enabling the developer to write code that achieves maximum efficiency on the server. P6Spy is designed to be installed in minutes and requires no code changes.

http://www.p6spy.com/