Results 1 to 7 of 7

Thread: Tablespace Scan

  1. #1
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    16

    Unanswered: Tablespace Scan

    We are on DB2- OS/390. Our application constitutes of queries with following pattern in the where clause

    where
    CAS_PART_ID = :h_part_id
    OR
    :h_part_id = 0

    DB2 is not using indexes for the above mentioned patterns. And hence leading to tablespace scans. Any ideas to improve the access paths....

    Thanks & Regards,
    Nagesh
    Nagesh

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The query doesn't make a lot of sense to me... It basically means "select record(s) with the specified part id or, if part id is not specified (is 0) select all records from the table". Since, at the time of the query compilation it is not known whether the host variable is 0 or not the optimizer decides that it might be reading the entire table and for this reason chooses table scan. I'm not sure if you can affect this behaviour without re-writing the query itself...

  3. #3
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    16
    Query functionally signifies..
    if h_part_id is specified by the user, use the clause CAS_PART_ID = :h_part_id
    else
    show all the rows in the table...

    We cannot rewrite the query as this is a functional requirement. I don't know if we can achieve this functional requirement in any other way.

    Can we explore the options of "DB2 deciding the access path at run time". What should we do for this....

    Thanks & Regards,
    Nagesh
    Nagesh

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't see why you can't write 2 SQL statements and execute the appropriate one based on the value of the host-variable.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    16
    We have NOT less than 10columns in primary keys and the query with part_id where clause is a template....

    In real scenario, query looks ....

    where
    (col1 = :h1
    or
    :h1 = 0)
    and
    (col2 = :h2
    or
    :h2 = 0)
    ....
    ....
    ....
    (col10 = :h10
    or
    :h10 = 0)

    and in real time scenario, any of above mentioned may/may not be passed...

    Hence writing separate queries for each case isn't practical

    Thanks & Regards,
    Nagesh
    Nagesh

  6. #6
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    This is a hard one...

    But fact is that with "... OR :HOST = 0" you are going to have massive performance problems.

    Better way (not a perfect sollution either) is to have many key-ranges like this:

    where col1 between :h1_low and :h1_high
    and col2 between :h2_low and :h2_high
    and col3 between :h3_low and :h3_high
    ...

    Now, when you need to have certain column, issue
    the host value to both :hx_low and :hx_high variables.

    Otherwise :hx_low and :hx_high have LOWVALUE and HIGHVALUE.

    This way this query may use some indexes, but still it's a pain in the...

    Cheers, Bill

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by zip2nagesh
    where
    (col1 = :h1
    or
    :h1 = 0)
    and
    (col2 = :h2
    or
    :h2 = 0)
    ....
    ....
    ....
    (col10 = :h10
    or
    :h10 = 0)
    ...which translates into:

    If all of the paramters are missing return the entire table; otherwise use whatever criteria are available

    I guess you'll have to resort to dynamic sql and build the WHERE clause 'on the fly' from whatever host variables are not 0.

Posting Permissions

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