Hello and thanks in advance,

I am trying to log queries of my app by using an event monitor. The app works fine without errors and producing correct results. Unfortunately, my event monitor only gives queries with question marks, like "select * from address where addr_id = ?" instead of replacing with placeholder. Using a dynamic sql snapshop also gives the same result. Why??

The question mark is a parameter marker/place holder. I'd say that it is often not a good idea to put the specific values in there because (a) this may be dynamic SQL and those values may not be know at statement compilation time, and (b) the values may contain sensitive information.

These are prepared statements. That is the way your applicaiton submits them. There is a big advantage to this in that the statement is compiled only once and the access plan (package) is stored in package cache for reuse over and over again without recompiling.

If your application did not use prepared statements, then the SQL would have to be compiled each time it is submitted (assuming the predicate values are different for each query), probably resulting in longer execution times.

You can do an explain using the parmater markers, so there is usually no serious limitation in their use in analyzing the system performance. Sometimes a query may run faster if DB2 knows the exact value of a predicate at compile time (when the package is created) but a good DBA can figure out ways to solve these problems.

Yes, the queries that I am looking at are prepared statements, but when they are executed their placeholders have actual values. Do you know why the actual values aren't replaced in the TEXT when the event monitor or get snapshot is used? Thanks.

The SQL snapshot gets the data from the package cache, which only has the prepared statement.

So is there any way to get the executed statement? Thanks.

What gets executed is the access plan (also called "section"). A section is something like a function in any other programming language, and the DB2 runtime takes the section and interprets it like /usr/bin/perl is doing it for perl scripts. That being said, a function can take parameters that influence the function result. Exactly the same applies to SQL statements with parameter markers: the SQL statement (section) is "programmed" once you compile the SQL statement and bind it to the database (or execute it for the first time in case of dynamic SQL so that it is kept in the DB2 statement cache). Thus, the SQL statement that's being executed is what can be found in the snapshots.