Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    3

    Unanswered: Question Marks in Logs?

    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??

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Dracofrank
    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??
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2008
    Posts
    3

    Prepared Statements

    Quote Originally Posted by Marcus_A
    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.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Dracofrank
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2008
    Posts
    3
    Quote Originally Posted by Marcus_A
    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.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •