Results 1 to 2 of 2

Thread: nodenumber help

  1. #1
    Join Date
    Oct 2010
    Posts
    94

    Unanswered: nodenumber help

    Hello,

    I am running a multithreaded application against a DB2 database - and I am trying to breakdown the total data retrieved by the query below into smaller subsets that each of the threads in the application could process independently -

    Select
    X.C1
    from X left outer join Y
    on
    X.Ck = Y.Ck*

    Ck - is the distribution key for both X and Y - they are collocated

    (This query is representative of the actual workload I am trying to optimize)

    The first query I wrote to split this workload down into 8 smaller subsets (as there are 8 partitions on our test DB server) was -

    Partition-1

    Select
    X.C1
    from X left outer join Y
    on
    X.Ck = Y.Ck
    where mod(X.Ck,8) = 0

    Partition-2
    Select
    X.C1
    from X left outer join Y
    on
    X.Ck = Y.Ck
    where mod(X.Ck,8) = 1

    and so on for other partitions

    however, I am told that this is running 8 joins - one for each thread - ON ALL PARTITIONS and then the mod predicate is being applied - that defeats the purpose of breaking the workload down

    So I am considering using the nodenumber function as an alternative to the mod function -

    obvious question - Would nodenumber function ensure the query issued from each partition be restricted to a single node?

    As always, thanks for your help!!!!

    - G

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The documentation of NODENUMBER() does not give such a guarantee, so naturally you cannot really rely on this. It depends on the plan that the optimizer chooses for the queries. Have you had a look at those?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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