Results 1 to 4 of 4

Thread: explain querry

  1. #1
    Join Date
    Dec 2003
    Posts
    39

    Unanswered: explain querry

    Db2 v7.2 ,AIX 4.3 and 5.1
    STEP1
    ********
    I am trying to analyze the access plan of my querry
    The process I am following is as follow

    STEP2
    ******
    db2 set current explain mode explain


    executing my querry ..

    b2 connect to nc3db;

    db2 "SELECT DISTINCT(MAIN.ODBID), MAIN.CREATETMSTMP, MAIN.CREATEUSER,MAIN.CREATETRAN,MAIN.LASTUPDTMSTMP , MAIN.LASTUPDUSER,\
    MAIN.LASTUPDTRAN, MAIN.ODBID_EQP_REF,MAIN.BAR_CODE,MAIN.FORM, MAIN.SERVICE_NAME, MAIN.MONITOR_APPL, MAIN.INSTALL_TARGET, \
    MAIN.INSTALL_COMPLETED,MAIN.TRACKING_NUM, MAIN.ORIGINAL_SYSTEM, MAIN.ORIGINAL_ID,MAIN.TRACKING_SYSTEM,MAIN.COMMON_ NAME, \
    MAIN.SERIAL_NUMBER, MAIN.STATUS, MAIN.DAY_OPS_PHONE,MAIN.NIGHT_OPS_PHONE, MAIN.HOT_OPS_PHONE, MAIN.STOP_DATE, \
    MAIN.DNMS_POLL_NETWORK, MAIN.DEVICE_FUNCTION, MAIN.MANAGING_SYSTEM, MAIN.MONITOR_ACTION,MAIN.ALIAS_NAME, \
    MAIN.IN_SERVICE_DATE,MAIN.OUT_SERVICE_DATE, MAIN.PRIORITY_CODE, MAIN.STATUS_DATE,MAIN.STATUS_DETAILS, \
    MAIN.MAX_ENDPOINT_EST, MAIN.FIRMWARE_VERSION, MAIN.REPAIR_INTERVAL,MAIN.PRIMARY_IP_ADDRESS, EQPREF.IS_SIGNIFICANT, \
    MAIN.ODBID FROM NC3.eqp main,NC3.eqp_ref eqpRef WHERE (((UCASE(main.common_name) = 'EVRO_SCHDMSEVPN1'))) AND \
    (main.odbid_eqp_ref=eqpRef.odbid AND main.stop_date>'9999-12-30' AND eqpRef.stop_date>'9999-12-30') ORDERBY \
    eqpRef.is_significant DESC, MAIN.ODBID "


    db2 connect reset;
    date


    Step3
    *******
    db2exfmt -d db -g TIC -w -1 -n % -s % -# 0 -o storeOrg.txt


    I am getting the following error

    DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp. 1991, 2001
    Licensed Material - Program Property of IBM
    IBM DATABASE 2 Explain Table Format Tool

    Connecting to the Database.
    Connect to Database Successful.
    No Explain instances were found for database db, source pattern: %.%.
    Output is in storeOrg.txt.
    Executing Connect Reset -- Connect Reset was Successful.

    Kindly suggest how to resole the above error with example...

  2. #2
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186
    Not sure why you have all the option on the explain plan. But just use
    db2exfmt -d SID -1 -g TIC -o output.file, this will give you a graphical format in the output file. I didn't look up all the options you were using. If the error keeps happening, just rerun db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL to re-setup your explain tables and then rerun your sql.
    David Quigley

  3. #3
    Join Date
    Dec 2003
    Posts
    39
    i tried the option what you suggested
    gain got the same error

    db2exfmt -d nc3db -1 -g TIC -o output.file
    DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp. 1991, 2001
    Licensed Material - Program Property of IBM
    IBM DATABASE 2 Explain Table Format Tool

    Connecting to the Database.
    Connect to Database Successful.
    No Explain instances were found for database nc3db, source pattern: %.%.
    Output is in output.file.
    Executing Connect Reset -- Connect Reset was Successful.
    [shussain@nc3testdb#]

    am i supposed to use (explain plan for ) option in my querry.

  4. #4
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186
    Try running the
    db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL

    Make sure it finished successfully,

    Then

    db2 terminate
    db2 connect to <db>
    db2 set current explain mode explain
    run your statement
    db2exfmt -d <db> -1 -g TIC -o output.file,


    review the output.file to make sure it has what your looking for, if not add the other options to the command line.
    David Quigley

Posting Permissions

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