Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to get "estimated costs" in SQL explain using db2expln command?

    Hi,
    using DB2 10.1 server on Linux. Accessing DB2 database using DB2 10.5 fixpack 5 db2 client on Windows.

    I would like to create SQL explain. From my DB2 Windows client I have executed command:
    db2expln -database mydatabase -u myuserid mypassword -t -g -f myfile.sql

    In myfile.sql I put simple SQL:
    select * from sysibm.sysdummy1

    I got result:
    Code:
    ******************** DYNAMIC ***************************************
    
    ==================== STATEMENT ==========================================
    
            Isolation Level          = Cursor Stability
            Blocking                 = Block Unambiguous Cursors
            Query Optimization Class = 5
    
            Partition Parallel       = No
            Intra-Partition Parallel = No
    
            SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
                                       "DB2INST1"
    
    Statement:
    
      select *
      from sysibm.sysdummy1
    
    
    Section Code Page = 912
    
    Estimated Cost = 0,000034
    Estimated Cardinality = 1,000000
    
    Table Constructor
    |  1-Row(s)
    Return Data to Application
    |  #Columns = 1
    
    End of section
    
    
    Optimizer Plan:
    
     Operator
       (ID)
    
     RETURN
      ( 1)
       |
     TBSCAN
      ( 2)
       |
     TFunc:
     SYSIBM
     GENROW
    If you look at the "Optimizer Plan" graph at the bottom it is clear there are no "estimated costs" in it. Is there any way I can get displayed "estimated costs" inside Optimizar Plan graph?
    Like (estimated costs in bold):
    ==============
    Operator
    (ID)

    RETURN
    ( 1)
    0,000034
    |
    TBSCAN
    ( 2)
    0,000032
    |
    TFunc:
    SYSIBM
    GENROW
    ==============
    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by grofaty View Post
    Is there any way I can get displayed "estimated costs" inside Optimizar Plan graph?
    Yes. Use db2exfmt.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    I tried using eb2exfmt and it works file. I have another problem. I have executed above command on DB2 server (remote ssh session) and it works fine. But on my Windows 7 PC with DB2 client installed this tool is not available. Do I need to install something else?

    ========== output of db2level command ==========
    C:\Programs\IBM\SQLLIB>db2level
    DB21085I This instance or install (instance name, where applicable: "DB2")
    uses "64" bits and DB2 code release "SQL10055" with level identifier
    "0606010E".
    Informational tokens are "DB2 v10.5.500.107", "s141128", "IP23628", and Fix
    Pack "5".
    Product is installed at "C:\Programs\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".

    From Windows Control Panel:
    Click image for larger version. 

Name:	db2_version.png 
Views:	6 
Size:	31.6 KB 
ID:	16261
    Last edited by grofaty; 03-26-15 at 03:38.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    The runtime client is for run-only environments , has a small disk-footprint, and few tools.

    The command line tool db2exfmt is not part of the DB2 clients - it is part of a DB2-server installable as far as I know.
    For some reason IBM does not distribute db2exfmt with the "fat client" on V10.5 (IBM Data Server Client).

    Your options are either to use a GUI tool that shows detailed explains, many are available, including IBM Data Studio 4.1.1 or higher, or instead to have a DB2-server copy on your Windows environment (e.g. a free-to-use one - you don't need to have local databases, you can use the tools against remote databases).

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    You can install db2 express-c to get an access to db2extfmt utility as well.
    Regards,
    Mark.

Posting Permissions

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