Results 1 to 6 of 6

Thread: Query tuning

  1. #1
    Join Date
    Jun 2006
    Posts
    7

    Unanswered: Query tuning

    Hi,
    I am new to DB2 adminstration..As far as i know these are the steps to take to tune a bad SQL::
    1)Use Visual explain and analyse path
    2)Use db2advisor for index suggestion
    3)Update statistics

    Could you please elaborately tell me as to how to tweak any bad SQL..Thanks in advance

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you want a generic answer, you may be better off reading a book ...

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2004
    Posts
    330
    Reading a book? I guess that would be a good way to pass the time while waiting for the SQL to run.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    There are a few rules-of-thumb, of course, like avoiding stage-2 predicates in "sensitive" places, or avoiding multiple scans through large tables. Sometimes, it's just a matter of rewriting the SQL query (e.g., join instead of subquery or vice versa) and trying out which one takes longest to terminate ;-)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Jun 2006
    Posts
    7
    Thanks for the help.

  6. #6
    Join Date
    Mar 2004
    Posts
    46
    Other tips -
    1) Understand your database schema and data (as much as possible)
    2) Understand the DB2 optimizer and various switches (configuration parameters)
    3) Understand what the query (or SQL statement) is trying to do
    4) Make use of the tools available
    5) Use DB2's advisor (its free !)

    Here's what I do for my DB2 v8.2.4 environments (actual script) -
    ==============================
    #!/bin/ksh

    PROGNAME="`basename $0`"
    if [[ ! $# -eq 2 ]]
    then
    echo ; echo ; echo ;
    echo "$PROGNAME:2 Parameters required"
    echo "Usage: $PROGNAME <dbname> <query-file>"
    echo ; echo ; echo ;
    exit 1
    fi

    DBNAME=$1
    QUERY_FILE=$2

    db2 connect to $DBNAME
    db2 set current explain mode explain
    db2 -tvf $QUERY_FILE
    db2 terminate
    db2exfmt -1 -d $DBNAME -o ${QUERY_FILE}.explain
    db2 terminate
    db2advis -d $DBNAME -i ${QUERY_FILE} -m MICP -b SYSTOOLSPACE -c SYSTOOLSPACE -f -o $QUERY_FILE.db2advis.sql | tee $QUERY_FILE.db2advis
    db2 terminate
    ==============================

Posting Permissions

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