Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2012
    Posts
    21

    Question Unanswered: SQL0955C Sort memory cannot be allocated to process the statement

    FUNCTION: DB2 UDB, CDE query execution, scanner, and evaluators, WorkUnitVectorMemInfo::increaseWorkUnitVectorMemRe , probe:406
    DATA #1 : String, 126 bytes
    Sortheap insufficient to reserve minimum work unit memory.
    Minimum pages: 32768
    Pages requested: 4000
    Sort heap pages: 4000
    DATA #2 : Function name in Library, 75 bytes
    ibm_cde::query::WorkUnitVectorMemInfo::increaseWor kUnitVectorMemRes(Uint64)
    DATA #3 : File name, 25 bytes
    WorkUnitVectorMemInfo.cpp

    I converted a row based database to column based using the db2convert command.
    I currently cannot query any tables and I am running into memory issues. Any suggestions would be appreciated.


  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    What operating system? What hardware platform? How much RAM is available to your V10.5 instance? IS that amount greater than the minimum required for column organized tables ? What is the setting for your sort related parameters?

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I guess you'll need to follow the recommendations in the manual related to the tuning of your database for column-organized tables.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Oct 2012
    Posts
    21
    db2mor:

    OS - AIX 6
    DB2 V10.5
    INSTANCE MEMORY - AUTOMATIC(3G rounded)
    BLU accelration I believe requires a system w/ 64GB is this true?
    The system currently only has 4GB of memory
    SORTHEAP=AUTOMATIC(500)
    SHEAPTHRES_SHR=AUTOMATIC(1000)

    I'm new to column organized tables and just wanted to garner some thoughts on optimal configurations.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I searched by the words "tuning column-organized tables" on IBM DB2 10.5 Information Center for Linux, UNIX, and Windows
    then found an article
    Column-organized tables


    There was a paragraph in the article, like...
    Shared sort heap

    The execution of queries against column-organized tables involves parallel processing, and therefore requires use of the shared sort heap. The shared sort heap is allocated when at least one of the following conditions is true:
    •The intra_parallel database manager configuration parameter is set to YES.
    •The sheapthres database manager configuration parameter is set to 0.
    •The connection concentrator is enabled.

    The shared sort heap is also allocated when DB2_WORKLOAD=ANALYTICS. If you choose to run without DB2_WORKLOAD=ANALYTICS, set intra_parallel to YES or sheapthres to 0 to ensure that the shared sort heap is allocated on your database. Attempting to run a query that accesses a column-organized table when no shared sort heap is allocated will result in the query failing with SQL1406N; if there is insufficient sort heap memory, SQL0955C is returned.
    Looking in a sentence
    " if there is insufficient sort heap memory, SQL0955C is returned. ",
    you may simply need to allocate more memory for DB2(or if it was impossible, add more memory to your hardware configuration).


    The following paragraph might also be useful
    System and database configurations

    Column-organized tables are supported only on Linux (x86-x64, Intel and AMD processors) and AIX® (POWER® processors).

    The following system and database configurations do not support column-organized tables in the DB2 Version 10.5 release.
    ...
    ...

    The following additional restrictions apply to column-organized tables in the DB2 Version 10.5 release:
    ...
    ...


    This Redbook might be helpfull too.
    IBM Redbooks | Leveraging DB2 10 for High Performance of Your Data Warehouse

    Table of contents

    Part 1. Overview
    Chapter 1. Gaining business insight with IBM DB2
    Chapter 2. Technical overview of IBM DB2 Warehouse
    Chapter 3. Warehouse development lifecycle
    Part 2. Technologies
    Chapter 4. Column-organized data store with BLU Acceleration
    Chapter 5. Row-based data store
    Chapter 6. Data movement and transformation
    Chapter 7. Monitoring
    Chapter 8. High availability
    Chapter 9. Workload management
    Chapter 10. Mining and unstructured text analytics
    Chapter 11. Providing the analytics
    4.2.1 Capacity planning
    ...
    ...

    ... The minimum system requirements for BLU Acceleration
    are eight cores and 64 GB of memory. As a preferred practice, maintain a
    minimum ratio of 8 GB of memory per core.

    For more information about sizing guidelines, see Best Practices: Optimizing
    analytic workloads using DB2 10.5 with BLU Acceleration
    , found at:
    https://www.ibm.com/developerworks/c...ation_0913.pdf

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Run the AUTOCONFIGURE command to set the most appropriate configuration (don't forget to run "db2set DB2_WORKLOAD=ANALYTICS" and restart the instance first).

    I must say that, while you will be able to create and use column-organized tables, you will not see any performance benefits from them on such a puny server.

    And you may want to read this before going any further: https://www.ibm.com/developerworks/c...ation_0913.pdf
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Quote Originally Posted by manawa View Post
    db2mor:
    ...
    The system currently only has 4GB of memory
    ...

    I'm new to column organized tables and just wanted to garner some thoughts on optimal configurations.
    I started noticing a difference at 128GB ram.

  8. #8
    Join Date
    Oct 2012
    Posts
    21
    Cool. Well currently the system is pooling / sharing resource with other servers.
    The best allocation I could get at the moment is 8GB. We will be looking to purchase more RAM.

    Thanks all for the input.

  9. #9
    Join Date
    Sep 2007
    Location
    Moscow
    Posts
    41
    Quote Originally Posted by manawa View Post
    The system currently only has 4GB of memory
    SORTHEAP=AUTOMATIC(500)
    SHEAPTHRES_SHR=AUTOMATIC(1000)
    BLU is very hungry for CPU and RAM.
    When we test our query's SQL0955C is gone only after setting SORT_HEAP to 2Gb.
    Also, need have UTIL_HEAP_SZ=6GB for good compression on column-tables (for the same compression ratio as classic adaptive row compression)

    Try to incrementally increase SORT_HEAP to avoiding SQL0955C

    In our case we can comfortable testing BLU with 12GB RAM LPAR

  10. #10
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by manawa View Post
    The best allocation I could get at the moment is 8GB. We will be looking to purchase more RAM.
    Do not start using BLU with under-allocated hardware. You are wasting everybody's time and you do not give BLU a fair change.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  11. #11
    Join Date
    Oct 2012
    Posts
    21
    Quote Originally Posted by mitrofun View Post
    BLU is very hungry for CPU and RAM.
    When we test our query's SQL0955C is gone only after setting SORT_HEAP to 2Gb.
    Also, need have UTIL_HEAP_SZ=6GB for good compression on column-tables (for the same compression ratio as classic adaptive row compression)

    Try to incrementally increase SORT_HEAP to avoiding SQL0955C

    In our case we can comfortable testing BLU with 12GB RAM LPAR
    Thanks mitrofun. Things seem to be working now. I've definitely saved a lot of space, about 500GB w/ the column organized feature.

Posting Permissions

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