Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Location
    India
    Posts
    246

    Unanswered: LOBs - separate storage

    Hi all,

    I am using db2 10.1 on RHEL 6. my table DDL is as below:

    CREATE TABLE SCRAP.BLOB_TEST (
    ID INTEGER,
    TYPE BLOB(1M) INLINE LENGTH 164 LOGGED NOT COMPACT
    )
    IN USERSPACE1;

    now i wan to store the BLOB ( TYPE field) in separate Tbsp. How to do this:

    1. Can i use alter table statment to add a "LOBS in..." CLAUSE for the field?

    If not:

    Shall i use an aux. table or use a import of that table then export to a new table with desired LOBS IN clause..

    what can be done ?

    thanks in advance.

    sumit
    ssumit

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    new tablespaces - admin_move_table
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    When you create a new tablespace for large objects (LOB columns), make sure you have file system caching on (since LOB's don't user bufferpools). For all other tablespaces (except system temporary tablespaces) file system caching should be turned off and you allocate generous amounts of memory to each bufferpool.

    Also, for best performance, consider using a 32K tablespace for data and making the INLINE as large as possible. However, if almost all of you LOB data for that column exceeds the INLINE length, then maybe not worth it since the LOB tablespace would have to accessed every time anyway.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Thanks for all suggestions.. i agree.

    But i want to keep my table in TS1 and only LOB field data in LOBTBSP1. by design it was not initially incorporated in table DDL (LOBS IN ... clause).

    Now we want to achieve this. Shall i do an export and import in new table DDL or any other method is there Table size with LOB is 12GB.

    DB2 10.1 LUW. any suggestions ?

    sumit
    ssumit

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by przytula_guy View Post
    new tablespaces - admin_move_table
    my experience:
    - new tablespaces (32K)
    - admin_move_table
    - alter table inline lenght (80% will fit inline)
    - reorg table (longlobdata)

    When the table still sits in the old (pagesize < 32k) tablespace the alter to set the inline length will not be successful because it will not fit.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Posting Permissions

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