Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    73

    Unanswered: query optimization

    Pepople:

    Im new in databases. I was working with SQL Server. Do you know if db2 has any tool to optimize queries or calculate it cost??
    The problem is that I have a nested script and when I run it tha database crashes.

    Regards,

  2. #2
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71
    i think you're looking for EXPLAIN.

    HTH

  3. #3
    Join Date
    May 2002
    Posts
    73

    query optimization

    could you explain the usage of EXPLAIN??

    Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    db2's EXPLAIN is like sql/server's SHOWPLAN

    see DB2 Explains Itself: A Roadmap to Faster Query Runtime

    rudy
    http://rudy.ca/

  5. #5
    Join Date
    May 2002
    Posts
    73

    query optimization

    The query that I run is:. /home/rebolled/db2profile
    # The following three lines have been added by UDB DB2.
    if [ -f sqllib/db2profile ]; then
    . sqllib/db2profile
    fi


    PATH=$PATH:/usr/prepaid/currentVersion/db/scripts/unefon:/usr/prepaid/currentVersion/batch/reports/unefon/:/usr/prepaid/currentVersion/db/scripts/unefon/batch_jobs

    if [ `/usr/prepaid/4.0/db/scripts/isActiveNode.sh` = 'T' ];
    then

    #
    db2 change isolation to ur
    db2 connect to epddact user compuser using compuser
    #
    wpp=`hostname |cut -c 1-4 |tr 'a-z' 'A-Z'`
    currDate=`date +%Y%m%d`
    fname="$wpp.ACCTREY.$currDate.csv"
    #rpt_dir="/usr/prepaid/currentVersion/db/util/reports/unefon"
    #rpt_dir="/home/rebolled/scripts/reportes_wpp"
    rpt_dir="/tmp"
    #
    db2 "export to $rpt_dir/tempo_0 of del with rate(cuenta, offer) as(select fk_account_number, max(fk_cust_offerg_no) from t19_rate_pln_offrg group by fk_account_number) select t14.fk_cell_dev_min, t12.account_number, t113.external_acct_no, t19.fk_rate_plan_id, t19.fk_service_id, t12.acct_curr_balance from t12_account t12, t14_account_devc t14, t113_cust_act_xref t113, t19_rate_pln_offrg t19, rate where t12.account_number = t14.fk_account_number and t12.account_number = t113.fk_account_number and t12.account_number = cuenta and cuenta = t19.fk_account_number and offer = t19.fk_cust_offerg_no and t12.account_end_dt is null and t14.account_devc_en_dt is null and t12.fk_account_status <> 'D' FOR READ ONLY"
    #
    sed "s/\"//g" $rpt_dir/tempo_0 > $rpt_dir/tempo_1
    sed "s/+//g" $rpt_dir/tempo_1 > $rpt_dir/tempo_2
    sed "s/\.,/,/g" $rpt_dir/tempo_2 > $rpt_dir/tempo_3
    mv $rpt_dir/tempo_3 $rpt_dir/$fname
    rm $rpt_dir/tempo_*
    #
    db2 connect reset
    #
    fi
    # end of program.


    Is there any way to optomize it ???

    Regards

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    make sure the proper indexes are defined?

    your database administrator should be able to advise you further

    rudy

Posting Permissions

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