Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    29

    Unanswered: Db2 v9.1 aix(5.3)

    I'm having a hard time getting my head around sorts in DB2.

    Is it correct to state that an individual sort will only 'spill over' into using the memory in System Temporary Tablespace when the sort requires more memory then allocated in the SORTHEAP parameter?

    For example:

    Eg 1.

    If I have a transaction that requires 90MB of memory to perform it's sort and my SORTHEAP = 20000 (80MB per sort) then does it mean that the entire 90MB required to sort will utilized memory from the System Temporary Tablespace? Or will the 80MB be used from the SORTHEAP and 10MB will be 'spilled over' into System Temporary Tablespace?

    Eg 2.

    If I have a transaction that requires 70MB of memory to perform it's sort and my SORTHEAP = 20000 (80MB per sort) then does it mean that the entire will be done in memory from the SORTHEAP and NO memory will be required from System Temporary Tablespace?

    Thank you!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It's not that simple, so the answers to your three questions really are "no", "no", and "maybe".
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2011
    Posts
    29
    Quote Originally Posted by n_i View Post
    It's not that simple, so the answers to your three questions really are "no", "no", and "maybe".
    Hi N I, thanks for the reply but could you be more specific in your answer please?

    I was under the impression that the only time TEMP SPACE is used (with regards to sorting) is when there is not enough memory to perform the sort in the SORTHEAP, otherwise the TEMP SPACE is NOT used for sorting if there IS enough space in the SORTHEAP to perform the sort.

    Can you please clarify?

    Thanks,

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The SORTHEAP value determines the maximium amount of memory that can be allocated for sorting during the statement processing. However, the database manager may choose to allocate less than SORTHEAP. It's all described in the manual: IBM Knowledge Center

    Keep in mind that sorting is not the only consumer of SORTHEAP. Also, the agent may request less memory than is actually needed for a sort, which will cause a spill even though SORTHEAP value is sufficient.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jul 2011
    Posts
    29
    Quote Originally Posted by n_i View Post
    The SORTHEAP value determines the maximium amount of memory that can be allocated for sorting during the statement processing. However, the database manager may choose to allocate less than SORTHEAP. It's all described in the manual: IBM Knowledge Center

    Keep in mind that sorting is not the only consumer of SORTHEAP. Also, the agent may request less memory than is actually needed for a sort, which will cause a spill even though SORTHEAP value is sufficient.
    First of all, thank you kindly for your reply. Much Appreciated N I!

    Secondly, if there is enough memory available in the SORTHEAP for the transaction sorting, how could it possibly spill over?

    Also, I just want to confirm that the term 'spill over' means that the sorting for the transaction has moved entirely to the TEMPSPACE, meaning it's not possible to have a partial sort completed in the SORTHEAP and the remaining sorting required to occur in the TEMPSPACE?

    Thanks again N I!

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sanchez786 View Post
    if there is enough memory available in the SORTHEAP for the transaction sorting, how could it possibly spill over?
    I take the liberty of quoting myself, if you don't mind:

    the database manager may choose to allocate less than SORTHEAP. ... Also, the agent may request less memory than is actually needed for a sort
    Quote Originally Posted by sanchez786 View Post
    Also, I just want to confirm that the term 'spill over' means that the sorting for the transaction has moved entirely to the TEMPSPACE, meaning it's not possible to have a partial sort completed in the SORTHEAP and the remaining sorting required to occur in the TEMPSPACE?
    It's not possible for the sort to "occur in the TEMPSPACE". The computer CPU can only access data that are located in the RAM or registers, not in files. The agent will use whatever sort memory it could get from the DBM to sort the required data in chunks, while storing intermediate results in a temporary table.
    ---
    "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
  •