Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    4

    Unanswered: To define the table in DB2 cache or memory

    Hi,

    My question is DBA oriented

    I have a small table ~ 500 rows, each row 100 bytes.

    All the accesses to this table in on-line operations are for read and not for update or insert.

    But we have really many accesses to this table, about million every day.

    Is it possible for DBA to define the table in DB2 cache or memory, in order to cause selects not going to the disk, but to go to the memory?

    Thanks

    Jack

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes.

    1) create a bufferpool that is big enough.
    2) create a tablespace that uses this bufferpool
    3) create you table in the new tablespace.
    4) do not create any other tables in the tablespace
    5) do not create any other tablespaces that use this bufferpool

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    6) Create all necessary indexes on this table to ensure index-only access in most/all cases
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by stolze
    6) Create all necessary indexes on this table to ensure index-only access in most/all cases
    The table is very small and would only take up a few pages (depending on the pagesize). Creating an index would not guarantee their getting used because the table is so small. I have several table like this that have a PK and its index defined but in about every access plan I generate on SQL that uses these tables, the optimizer does a table scan even though the joins are on the PK.

    Table scans are not bad for small tables.

    Andy

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You are correct, Andy. The table has only about 15 pages...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Nov 2007
    Posts
    4
    Thanks, I will talk with our DBA.

Posting Permissions

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