Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Unanswered: Federated link query

    I am using a select stmt to "select" one of the field (say x) of a federated link table based on match of another single field ( say y )

    Its taking 15 sec for execution which is too much.
    optimization possible?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Standard SQL and DB2 doesn't know anything about "fields" and "federated link tables". Do you mean "columns" and "nicknames"? If so, it would be helpful to know the exact statement you are using, some information on the schema (indexes and the like), and also the usual information about which DB2 system you are using on which platform.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2007
    Posts
    63
    I am using DB2 v 8.
    Last edited by prem18; 11-18-08 at 14:33.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What's the access plan for the statement? In there, you will also see the query that is shipped to the remote server. Are there sufficient indexes on the remote server to support that query?

    How many results do you get back? Maybe there are 1 billion qualifying rows, so that it just takes time to shuffle them over the network wire...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2007
    Posts
    63
    I can run access plan . Since I am not sure with what details are required for you, I am waiting for your reply so that I can get you what information you need
    Thanks

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Sorry, I don't understand your question. The only thing you have to do is take your query and produce the explain output/access plan for it.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Apr 2007
    Posts
    63
    There are sufficient indexes on the table. Not sure what would be the bottleneck

  8. #8
    Join Date
    Dec 2004
    Location
    Italy
    Posts
    32
    Prem, check the runstats you did on the object...

    ... are the stats all right?

  9. #9
    Join Date
    Dec 2005
    Posts
    273
    Code:
    AND CURRENT DATE BETWEEN PTPR_PRICE_START AND PTPR_PRICE_END
    that is not indexable. Rewrite it to

    Code:
    AND PTPR_PRICE_START <= CURRENT DATE
    AND PTPR_PRICE_END >= CURRENT DATE

  10. #10
    Join Date
    Apr 2007
    Posts
    63

    Thumbs up

    Finally i got it..!!
    For validation purposes,I declared the variable var1 as varchar(100) instead of database field type varchar(7). When I matched the size with the database field size, the query executes in no second..!! and this resolved my issue!
    Anyways Thanks so much for helpful suggestions

Posting Permissions

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