Maximum size of a LOB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Malapha - 27 Mar 2008 12:52 GMT

Dear all,

I have got the problem to import large XML files (greater 2GB) as LOBs
into a DB V9.x. The specs specify the maximum size of LOB as 2 GB. I
am not quite sure how well Oracle does, but my information states that
their maximum size is 4 GB.

Now my questions:

1) Why is there a 2 GB limit (2^32 would be 4GB)
2) Is there a way to increase this limit
3) Does someone know if there are any plans to increase the limit in
future versions of DB2?

From my understanding, the limit is given by the architecture
underneath the Software. When talking about 64bit systems and 64bit
applications, I can't see any need to have a 2 GB limit other than
downward compatibility...

Of course I got the option to cut the XML-Files, but this would be a
manipulation of the original that I want to circumnavigate..

Thanks

Mala

Reply to this Message

Serge Rielau - 27 Mar 2008 13:04 GMT

> I have got the problem to import large XML files (greater 2GB) as LOBs
> into a DB V9.x. The specs specify the maximum size of LOB as 2 GB. I
[quoted text clipped - 4 lines]
>
> 1) Why is there a 2 GB limit (2^32 would be 4GB)

I think this limit goes back to the days when DB2 could log only 4GB.
You need to preserve 50% of capacity for recovery or so...

> 2) Is there a way to increase this limit

None without changing DB2 (i.e. PMR with feature request)

> 3) Does someone know if there are any plans to increase the limit in
> future versions of DB2?

I don't know. You can submit a request.

> From my understanding, the limit is given by the architecture
> underneath the Software. When talking about 64bit systems and 64bit
> applications, I can't see any need to have a 2 GB limit other than
> downward compatibility...

That is true in principle. There is the small problem of making sure
there isn't any code that relies on the length fitting into a signed
integer.

> Of course I got the option to cut the XML-Files, but this would be a
> manipulation of the original that I want to circumnavigate..

Mala, would you mind posting (or sending by email) your scenario.
I have a hard time imagining a >2GB XML file...

The mere fact that another vendor has a higher _theoretical_ limit isn't
a good incentive unless you actually have a _practical_ usage...

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

DA Morgan - 27 Mar 2008 16:44 GMT

> Dear all,
>
> I have got the problem to import large XML files (greater 2GB) as LOBs
> into a DB V9.x. The specs specify the maximum size of LOB as 2 GB. I
> am not quite sure how well Oracle does, but my information states that
> their maximum size is 4 GB.

Your information about Oracle is a bit dated. Current maximum BLOB size
is 128TB.

Reference:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1842

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

Reply to this Message

Malapha - 27 Mar 2008 17:29 GMT

> > Dear all,
>
[quoted text clipped - 11 lines]
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)

Daniel, thanks alot for this information! Seems IBM has some homework
to do..

Mala

Reply to this Message

DA Morgan - 28 Mar 2008 19:26 GMT

>>> Dear all,
>>> I have got the problem to import large XML files (greater 2GB) as LOBs
[quoted text clipped - 14 lines]
>
> Mala

All products are better at somethings and less so at others. I would
expect that DB2 will come closer, or match, this capability with the
Oracle compatibility features being built into future releases.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

Reply to this Message

Serge Rielau - 28 Mar 2008 21:26 GMT

> All products are better at somethings and less so at others. I would
> expect that DB2 will come closer, or match, this capability with the
> Oracle compatibility features being built into future releases.

Well someone is in a generous mood today.

Cheers
Serge

PS: I'd love to see the test setup for that 128TB LOB. But then against
who expects a vendor to test limits.... ;-)

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

DA Morgan - 29 Mar 2008 00:51 GMT

>> All products are better at somethings and less so at others. I would
>> expect that DB2 will come closer, or match, this capability with the
[quoted text clipped - 6 lines]
> PS: I'd love to see the test setup for that 128TB LOB. But then against
> who expects a vendor to test limits.... ;-)

I am aware of storage at a good percentage of this level.

My step-daughter is a professional astronomer and has no problem
collecting 1TB per night when observing. Then there is TV and movie
footage, spy satellites, phone intercepts, etc. There are a lot of
people generating single file in that size range which is why in 11g
we now have Exabyte tablespaces and Petabyte datafiles.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

Reply to this Message

Serge Rielau - 29 Mar 2008 02:56 GMT

>>> All products are better at somethings and less so at others. I would
>>> expect that DB2 will come closer, or match, this capability with the
[quoted text clipped - 14 lines]
> people generating single file in that size range which is why in 11g
> we now have Exabyte tablespaces and Petabyte datafiles.

Takes me back when Microsoft felt it had to prove that SQL Server 2000
scales:
http://research.microsoft.com/~tbarclay/TerraServer.NET_files/frame.htm

I don't doubt that Terabytes of data are collected in a night of
astronomy. But why on earth would one want to store it in a single value
in a relational database?

A 128TB value is not the same as a 128TB data warehouse.
The later has value.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Bernd Hohmann - 29 Mar 2008 03:09 GMT

> I don't doubt that Terabytes of data are collected in a night of
> astronomy. But why on earth would one want to store it in a single value
> in a relational database?

Thats the way fresh decorated engineers would to the job.

I'd seen real live examples of this in the last weeks (not Terabytes but
close enough) including full transaction logging, backup during all
operations, a lot of programming dealing with all errors/problems during
operation and a fine users manual what to do during the commit phase
(take a break, go shopping)

We had to change the organic grown process (aka "compost pile") a little
bit after we got the project to speed up everything.

Bernd

Signature

... and don't forget to boogie!

Reply to this Message

DA Morgan - 29 Mar 2008 16:52 GMT

>>>> All products are better at somethings and less so at others. I would
>>>> expect that DB2 will come closer, or match, this capability with the
[quoted text clipped - 27 lines]
> Cheers
> Serge

Anne assures me your doubts will not affect her research. The
amount of data she collected while at Cerro Tololo IntraAmerican
Observatories in Chile and the Institute for Astronomy at University of
Hawaii at Manoa dwarf your assumptions by more than a magnitude.

128TB ... in one row ... in one column ... in one table. Then insert
another row.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

Reply to this Message

Bernd Hohmann - 29 Mar 2008 18:26 GMT

> Anne assures me your doubts will not affect her research.

I'm in a Dilbert comic - please help me out....

Signature

Unsere Identität entnehmen Sie bitte dem beigefügten Auszug aus
den Personenstandsbüchern. Gegen die Assimilierung in unser
Kollektiv ist nach dem ABGB (§66.4) kein Rechtsmittel zulässig.

Reply to this Message

DA Morgan - 29 Mar 2008 22:47 GMT

>> Anne assures me your doubts will not affect her research.
>
> I'm in a Dilbert comic - please help me out....

Mauve: Mauve has more RAM.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

Reply to this Message

Mark A - 29 Mar 2008 20:34 GMT

> Anne assures me your doubts will not affect her research. The
> amount of data she collected while at Cerro Tololo IntraAmerican
[quoted text clipped - 3 lines]
> 128TB ... in one row ... in one column ... in one table. Then insert
> another row.

I don't understand what benefit there is to store such data in a row of a
relational database (discounting Oracle machismo). Most applications store
the data in a file and then store the file name in a VARCHAR (VARCHAR2 for
Oracle).

Reply to this Message

Richard D. Latham - 29 Mar 2008 03:15 GMT

>> All products are better at somethings and less so at others. I would
>> expect that DB2 will come closer, or match, this capability with the
[quoted text clipped - 6 lines]
> PS: I'd love to see the test setup for that 128TB LOB. But then
> against who expects a vendor to test limits.... ;-)

Lets see, I like RAID-1 , so to test a database with 4 rows, I have to
have a PetaByte of storage :-)

Signature

#include <disclaimer.std> /* I don't speak for IBM ... */
/* Heck, I don't even speak for myself */
/* Don't believe me ? Ask my wife :-) */
Richard D. Latham lathamr@us.ibm.com

Reply to this Message