Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: convert from "long in" to inline

    Hi, I am preparing the conversion from v9.5 to v9.7 for my client. On the current v9.5 system the lobs are stored separately in a dedicated table space. I am planning to set the new inline setting for a certain table to a value that 80% of the lobs will move from external table space to inline, and the too-big 20% will remain separate.
    I wonder, is a simple alter table followed by a offline reorg enough to accomplish that? If so, how do I check? How can I see which row has it's LOB inline and which row still has it stored in the separate table space? I can think of a "alter table space reduce" before and after the reorg and see the diff. but I hope there is a more sophisticated method.

    Another thing: the client will also convert from win2003 to win2008 server. I haven't read anything about db2 on win2008 so far. Are there any pitfalls? Maybe a nice link?

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Did some testing myself. Weird results.
    tried this:
    alter table ... set inline length 3072
    reorg table ...
    alter tablespace ts32_bprce_lob2010 reduce
    and then I got this:
    Code:
    2010-10-20-09.44.02.536000+120 E2235016F579       LEVEL: Warning
    PID     : 2812                 TID  : 3748        PROC : db2syscs.exe
    INSTANCE: TST                  NODE : 000         DB   : XXXXX
    APPHDL  : 0-2860               APPID: *LOCAL.TST.101020073903
    AUTHID  : XXXXX
    EDUID   : 3748                 EDUNAME: db2agent (XXXX)
    FUNCTION: DB2 UDB, buffer pool services, sqlbAlterPool, probe:15
    MESSAGE : ADM1534W  Table space "TS32_BPRCE_LOB2010" could not be reduced in 
              size because there are used pages at the end of the table space.
    I thougt V9.7 solved this ...?

    Even more stange. db2pd examined the LOB tablespace and reported
    Code:
    Address            Id    TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM        Max HWM    State      MinRecTime NQuiescers PathsDropped
    0x000007FEC4D641C0 45    459264     459248     136752     261504     60992      398256     398256     0x00000000 0          0          No
    Then I extended the inline length to 4096 and reran. Now db2pd reports:
    Code:
    Address            Id    TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM        Max HWM    State      MinRecTime NQuiescers PathsDropped
    0x000007FEC4D641C0 45    590336     590320     136752     392256     61312      529008     529008     0x00000000 0          0          No
    The totalpgs GREW! The freepages and pending freepages also grew (so I answered my initial question myself). So, why did the LOB tablespace grow when it was suppose to shrink and how can I reclaim all that space held in that tablespace?

    googled and tried this
    Code:
    db2 alter tablespace ts32_bprce_lob2010 lower high water mark
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL1763N  Invalid ALTER TABLESPACE statement for table space
    "TS32_BPRCE_LOB2010" due to reason "11"  SQLSTATE=429BC
    I did mention "autoresize yes" when I created that tablespace. What the <beep> is a "Reclaimable Storage attribute"?
    Last edited by dr_te_z; 10-20-10 at 05:18. Reason: encountered SQL1763N

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    I've never tried whatever you're doing so not sure what the process is. I should try this some day.

    Regarding db2pd, there were some free pending pages (PndFreePgs) and they increased after you "extended the inline length to 4096". Try "list tablespaces show detail" to free them.


    Regarding "What the <beep> is a "Reclaimable Storage attribute":

    "...only table spaces created with DB2 Version 9.7 or later have the reclaimable storage capability. If you want to be able to reclaim storage in table spaces created with earlier versions of the DB2 product, you either must unload then reload the data into a table space created with DB2 Version 9.7, or move the data with an online move."

    Some additional info:
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by db2girl View Post
    ...only table spaces created with DB2 Version 9.7 or later have the reclaimable storage capability.
    Hmmmm.. makes me wonder. When I build a new server with v9.7.2 and I populate it by means of a "redirected restore" based upon the current v9.5.5. The v9.7.2.restore then creates the tablespaces... Does that count? I'm afraid not. Has to be a db2move then.

Posting Permissions

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