Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: db2expln - Intra-Partition Parallel

    v9.7 FP7; no DPF.


    Would you know why db2expln shows "Intra-Partition Parallel = No" when INTRA_PARALLEL = YES?


    I created SP and then collected db2expln for the package (got created for SP).

    db2expln shows:

    Partition Parallel = No
    Intra-Partition Parallel = No
    ....

    Process Using 6 Subagents
    | Access Table Name = xxx.yyy ID = 3,41



    I know db2 is using multiple sub-agents during SP run and db2expln shows it's "Using 6 Subagents", but why does db2expln show Intra-Partition Parallel = No?


    I have some old db2expln from last year (for different SP/packages in this db/instance) and some show:

    Partition Parallel = No
    Intra-Partition Parallel = Yes (Bind Degree = ANY)


    but others show:

    Partition Parallel = No
    Intra-Partition Parallel = No



    I checked bind degree for the new package and it's set to ANY.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Ultimately the optimizer decides whether to use parallelism or not. The parameter shows that the optimizer can (not "must") consider parallelism.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    syscat.packages has INTRA_PARALLEL column.


    Package A:

    Code:
    > db2 "select degree, intra_parallel from syscat.packages where pkgname = 'xxxx'"
    
    DEGREE INTRA_PARALLEL
    ------ --------------
    ANY    N

    So, I think this explains why db2expln has "Intra-Partition Parallel = No". SP contains SELECT/INSERT/UPDATE statements and SELECTs are using multiple sub-agents, which is fine.



    Package B:

    Code:
    > db2 "select degree, intra_parallel from syscat.packages where pkgname = 'P7045472'"
    
    DEGREE INTRA_PARALLEL
    ------ --------------
    ANY    Y

    This SP also contains SELECT/INSERT/UPDATE statements and SELECTs are using multiple sub-agents.


    How does the optimizer decide which package would have parallelism enabled and which won't? If it's based on the statements in SP, then why one package has INTRA_PARALLEL = Y and another N when both SP's contain similar type of statements?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl View Post
    How does the optimizer decide which package would have parallelism enabled and which won't?
    I know (I'm told) it does, but I don't know the rules by which it makes the choice.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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