Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2001
    Location
    England
    Posts
    426

    Unanswered: Getting io statistics for query

    I need to get the IO involved in a query - mainly because I think the data in a very large table isn't compressing very well for recent data (any suggestions about how to view that would be welcome too).

    The explain plan gui on the client doesn't work - I am told because of the amount of data - bigints and thousands of millions of rows. Would prefer a text output anyway but anything would be good.

    What I would really like is a statement I can run to get the statistics from a query in a resultset.

    I've tried
    SET CURRENT EXPLAIN MODE EXPLAIN
    but this gives an error - I'm guessing due to missing or inaccessible explain plan tables - but it doesn't look that easy to get the output anyway.
    error is DB2 SQL Warning: SQLCODE=217, SQLSTATE=01604, SQLERRMC=null, DRIVER=3.57.86

  2. #2
    Join Date
    Jan 2010
    Posts
    335
    Hi

    your session is already in Explain mode.

    Code:
    > db2 ? SQL0217N
    
    
    SQL0217W  The statement was not executed as only Explain information
          requests are being processed.
    
    Explanation:
    
    The current value of one of the Explain special registers has been set
    to EXPLAIN. This value allows dynamic SQL statements to be prepared and
    explained but prevents any dynamic statement from being executed.
    
    User response:
    
    Change the value of the appropriate Explain special register to a
    setting other than EXPLAIN by issuing the appropriate SET statement from
    the interface or application that is encountering this condition.
    
     sqlcode: +217
    
     sqlstate: 01604
    Which Version of DB2 are you using? Which OS?
    What is your request? Do you want to create an explain plan from the db2 explain format tool (db2exfmt; text based)?

    Cheers
    nvk

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    I would like to get the disk io from a query.
    Would prefer it in text but gui would do if that's easier.

    OS = Linux
    DB2 version 9.5

    I thought the query would show the io from the execution not estimated from the plan.
    Is it possible to get the actual io?

    I get an error from the explain plan gui in data studio due to a bug which has been raised with IBM.

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    It looks like
    SET CURRENT EXPLAIN MODE YES
    will execute the query - does that mean the io will be from the actual execution?

  5. #5
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    After running
    SET CURRENT EXPLAIN MODE YES
    query
    SET CURRENT EXPLAIN MODE NO

    how do I access the explain plan results?
    Is the only way via something like
    db2exfmt -d decipher -# 0 -w -1 -g TIC -n % -s % -o test_expl.txt
    Which I guess has to be run on my client machine to produce a file.

    I don't have db2exfmt installed on my m/c anyway.
    Last edited by nigelrivett; 03-08-10 at 09:07.

  6. #6
    Join Date
    Jan 2010
    Posts
    335
    Yep,

    you have to run the db2exfmt-tool. I usally run it from the same session where i issued the SQL-Statement. Also i just use "db2exfmt -d <DBNAME>". If you skip the Questions except for the output file, then the Access Plan for last inserted Query is generated.

    The Values for the Access Plan is based on the statistics for the used tables. They aren't very acurate and don't show the real number of I/Os . The same goes for the number of timerons. A smaller number of timerons does not may that the query is faster.

  7. #7
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    I'm running the queries from a script window in data studio. Don't think db2exfmt will work from there.

    Is there any way of getting the actual IO?

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use the snapshot view sysibmadm.snapdyn_sql Find your query in the STMT_TEXT column. Then look at columns POOL_DATA_L_READS (pages already in the bufferpool) and POOL_DATA_P_READS (pages read from the table on disk).

    Andy

  9. #9
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Quote Originally Posted by ARWinner View Post
    Use the snapshot view sysibmadm.snapdyn_sql Find your query in the STMT_TEXT column. Then look at columns POOL_DATA_L_READS (pages already in the bufferpool) and POOL_DATA_P_READS (pages read from the table on disk).

    Andy
    That sounds like exactly what I want.
    Unfortunately

    Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID").. SQLCODE=-30082, SQLSTATE=08001, DRIVER=3.57.86

    I'll see if I can get access.

  10. #10
    Join Date
    Jan 2010
    Posts
    335
    O.O How did you manage to run your queries?

    24 (USERNAME AND/OR PASSWORD INVALID)


    The username specified, password specified, or both, are
    invalid. Some specific causes are:
    1. If you have recently changed permissions on DB2 critical
    files such as db2ckpw or moved to a new Fixpak, the db2iupdt
    command which updates the instance might not have been run.
    2. The username being used might be in an invalid format. For
    example, on UNIX and Linux platforms, usernames must be all
    be lowercase.
    3. An error might have been made in specifying the catalog
    information. For example, the correct authentication type
    might not have been specified or, if applicable, the remote
    server might not have been cataloged on the local system.
    For more information on authentication, search the DB2
    Information Center
    (DB2 Database for Linux, UNIX, and Windows) using
    terms such as "authentication".

    How could you log in before?

    The snap_dyn is also not acurate since it's a snapshot at a given point in time. If you want precise data, you have to set up a event monitor for statements.

  11. #11
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    I get a connection using ibm data studio and run queries from there.
    I'm guessing I don't have permission on sysibmadm.snapdyn_sql.

  12. #12
    Join Date
    Jan 2010
    Posts
    335
    Missing Priviledge is SQL1092N.
    You could'nt login to the database, because you provided a wrong username or password

  13. #13
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Nope - I already have the connection. I don't provide the credentials when running a query.

Posting Permissions

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