convert from "long in" to inline

dr_te_z · #1 (**permalink**) 10-19-10, 06:39

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?

dr_te_z · #2 (**permalink**) 10-20-10, 04:04

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"?

db2girl · #3 (**permalink**) 10-20-10, 15:07

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

dr_te_z · #4 (**permalink**) 10-21-10, 06:42

Quote:

Originally Posted by db2girl

...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.

Internet Services

Web Development

Digital Marketing

Consumer Tech