Results 1 to 8 of 8

Thread: Db2 SQL Cache

  1. #1
    Join Date
    Aug 2003
    Location
    Ohio
    Posts
    38

    Unanswered: Db2 SQL Cache

    This may be a silly question, but I don't seem to know where to find the answer.

    We saw some SQL errors in our log today (SQL0404N A string in the UPDATE or INSERT statement is too long for column ""), but we didn't know what SQL was being executed to cause them. Is there a way to see what SQL has been issued recently against a db2 database (ver 8.2 - unix) ? In oracle, we can query v$sql to see what SQL is still in the statement cache... is there anything similar in DB2?

    Thanks!
    Jodie

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There is a way to see it, but only if it is a valid statement and added to package cache. You can see the statements in package cache with a "get snapshot for dynamic sql" or db2pd command. See the Command Reference manual for details on both of these commands.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2003
    Location
    Ohio
    Posts
    38
    Quote Originally Posted by Marcus_A
    There is a way to see it, but only if it is a valid statement and added to package cache. You can see the statements in package cache with a "get snapshot for dynamic sql" or db2pd command. See the Command Reference manual for details on both of these commands.
    If it's a valid statement (which I believe it was), would it automatically be added to the package cache?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by jodie
    If it's a valid statement (which I believe it was), would it automatically be added to the package cache?
    Yes, although in certain circumstances it could be bumped out by other subsequent statements.

    If the error was a runtime error, then the statement probably would be in package cache, but if the error was detected during SQL statement compilation, then it would not be in package cache.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2003
    Location
    Ohio
    Posts
    38
    Quote Originally Posted by Marcus_A
    Yes, although in certain circumstances it could be bumped out by other subsequent statements.

    If the error was a runtime error, then the statement probably would be in package cache, but if the error was detected during SQL statement compilation, then it would not be in package cache.
    Thanks...
    I tried the "db2 get snapshot for dynamic sql", and saw all of the SQL. Unfortunately, all of the variables were "?", indicating bind variables (which is a good thing), but is there any way to see what the variables have been for recent executions?

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Not with a snaphsot. You may be able to see the actual statements with DB2 Query Patroller, which I believe is priced as a separate license.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Aug 2003
    Location
    Ohio
    Posts
    38
    Figures... Thanks for the information.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    As far as I know, Query Patroller doesn't capture bind variable values anyway.

Posting Permissions

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