Large Data Tablespace Page Allocation

[Pablo]

I have observed a significant discrepancy between the amount of space
used in a long tablespace when using import as compared to load.

Can anyone explain the following?

In an attempt to move data from one table/tablepace to another observed
the following:

Exported lobfile = 291 MB (average 60K / CLOB object)

Amount of space used in large tablespace under four scenarios:
CLOB column defined as Compact:
Using import = 294 MB
Using load = 884 MB

CLOB column defined as Not Compact:
Using import = 413 MB
Using load = 1.2 GB

The large tablespace is defined with
PAGESIZE 4096 and EXTENTSIZE 32

We are operating DB2/UDB version 8.x on AIX 5.x

I may experiment with different page sizes since 4K may not be optimal
for clobs averaging 60K in size.

I find nothing in the LOAD documentation that explains such a
discrepancy. Any insight will be appreciated.

[Tomas]

Re: Large Data Tablespace Page Allocation

Pablo,

that looks familiar. We've encountered the same thing. It's been
fixed in one of the later fixpaks - I believe it was in FP6, or maybe
FP7. We went from FP4 to FP7a, and the problem went away.

/T

Tomas Hallin
Database Administration
Robert Half International
Pleasanton, CA

Pablo wrote:[color=blue]
> I have observed a significant discrepancy between the amount of space
> used in a long tablespace when using import as compared to load.
>
> Can anyone explain the following?
>
> In an attempt to move data from one table/tablepace to another[/color]
observed[color=blue]
> the following:
>
> Exported lobfile = 291 MB (average 60K / CLOB object)
>
> Amount of space used in large tablespace under four scenarios:
> CLOB column defined as Compact:
> Using import = 294 MB
> Using load = 884 MB
>
> CLOB column defined as Not Compact:
> Using import = 413 MB
> Using load = 1.2 GB
>
> The large tablespace is defined with
> PAGESIZE 4096 and EXTENTSIZE 32
>
> We are operating DB2/UDB version 8.x on AIX 5.x
>
> I may experiment with different page sizes since 4K may not be[/color]
optimal[color=blue]
> for clobs averaging 60K in size.
>
> I find nothing in the LOAD documentation that explains such a
> discrepancy. Any insight will be appreciated.[/color]

[phu@cs.dal.ca]

Re: Large Data Tablespace Page Allocation

Hi Paul,

I assume that you know enough about the COMPACT option and how that
relates to LOB space usage. So the extra space could be the result
of rounding up to the next 1KB boundary.

In regards to LOAD discrepancy: how are you calculating the 'size'
of the data?

Pablo <paul.shaw@eds. com> wrote:[color=blue]
> I have observed a significant discrepancy between the amount of space
> used in a long tablespace when using import as compared to load.[/color]
[color=blue]
> Can anyone explain the following?[/color]
[color=blue]
> In an attempt to move data from one table/tablepace to another observed
> the following:[/color]
[color=blue]
> Exported lobfile = 291 MB (average 60K / CLOB object)[/color]
[color=blue]
> Amount of space used in large tablespace under four scenarios:
> CLOB column defined as Compact:
> Using import = 294 MB
> Using load = 884 MB[/color]
[color=blue]
> CLOB column defined as Not Compact:
> Using import = 413 MB
> Using load = 1.2 GB[/color]
[color=blue]
> The large tablespace is defined with
> PAGESIZE 4096 and EXTENTSIZE 32[/color]
[color=blue]
> We are operating DB2/UDB version 8.x on AIX 5.x[/color]
[color=blue]
> I may experiment with different page sizes since 4K may not be optimal
> for clobs averaging 60K in size.[/color]
[color=blue]
> I find nothing in the LOAD documentation that explains such a
> discrepancy. Any insight will be appreciated.[/color]



--
Bill Phu -- Dalhousie University (phu at cs dal ca)
"Three grand essentials to happiness in this life are something to do,
something to love, and something to hope for."
- Joseph Addison