Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2011
    Posts
    38

    Unanswered: Explain equivallent in DB2

    I'm trying to analyze a query to see how it is using the indexes and how it can be improved. I'm new to DB2 but I have a vast experience in using MySQL, where I'd use the EXPLAIN command to analyze a select query.

    What is the equivallent command in DB2? If there is none, what is the common practice in analyzing queries?

    I'm using DB2/LINUXX8664 9.7.0 as the server and Ubuntu 10.04 as the client, on which I have IBM Data Studio installed.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    its called explain here as well. You can, also, use visual explain or any number of other tools.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    EXPLAIN has been in DB2 all the way back to when Bill Gates only bathed once a week (if his co-workers were lucky).
    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
    Apr 2011
    Posts
    38
    Thanks for your prompt response. If that's the case, then can you please advise me on the correct format of explain queries (an easy to understand link would be fine). I tried "explain select field from table" but it returned an error message.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by merik View Post
    advise me on the correct format of explain queries (an easy to understand link would be fine). I tried "explain select field from table" but it returned an error message.
    That's what manuals are for.

    EXPLAIN - IBM DB2 9.7 for Linux, UNIX, and Windows

  6. #6
    Join Date
    Apr 2011
    Posts
    38
    I got this error message:

    The required Explain table "ABC123.EXPLAIN_INSTANCE" does not exist.

    Where ABC123 is my user account on the server. How can I fix it?

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you should really get used to reading manuals

    Code:
    >db2 ? SQL0219N
    
    
    SQL0219N  The required Explain table "<name>" does not exist.
    
    Explanation:
    
    The Explain facility has been invoked but was unable to find the
    required Explain table "<name>". The Explain tables must be created
    prior to invoking Explain.
    
    User response:
    
    Create the required Explain tables. The SQL Data Definition Language
    statements needed to create the Explain tables are available in the file
    called EXPLAIN.DDL in the misc directory under sqllib.
    
     sqlcode: -219
    
     sqlstate: 42704
    While you are at it, you may want to also read about the db2exfmt utility - you will need it to retrieve plan information.

    Check out db2expln too.

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

Tags for this Thread

Posting Permissions

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