Question How does progress store LOBS?

[Cringer]

Random question of the day! How are LOBS stored in the DB? We have a table which is used to store PDFs and Word Docs as BLOBS in the DB. I've done a tabanalys and it reckons the table is taking up 20.9M for ~210k records. Which is surprising as the files stored average around 2MB. Or am I missing something in my interpretation of tabanalys?

                                                           -Record Size (B)-           ---Fragments--- Scatter
Table                                    Records    Size   Min   Max  Mean                Count Factor  Factor
PUB.BinaryObject                          210595   20.9M    48   282   104               210595    1.0     1.0

 

[TomBascom]

DBAnalys is blind to LOBs.

Rich Banville considers it a bug. You might try reporting it as such -- maybe if enough people finally report it they will fix it. It has been known for a long time but it somehow never makes it onto the fix list.

 

[Cringer]

Thanks Tom. How do you determine the RPB for a table with LOBs?

 

[TomBascom]

Guess.

 

[TomBascom]

... but "4" is probably a good value.

Most LOBs are going to be split across multiple blocks. On average the "leftover" fragment should be about 1/2 of a block. So either 2 or 4 is probably a good value.

 

[Cringer]

Thanks Tom - I shall stick my finger in the air...

 

[Cringer]

Raised as a bug with Progress.

 

[Rob Fitzpatrick]

In the schema you can (and probably should) specify a separate area for the LOB. Once it is in its own area you can more easily gauge its size.

 

[Cringer]

Yeah thanks Rob. Aware of that - although we don't have the disk space available to do a D&L (long story!).
I've had a response from Progress with a way of working out how much space the LOBs are taking up and for us it is 175GB, more than half the whole database. Which explains a lot.

 

[Rob Fitzpatrick]

Yeah thanks Rob. Aware of that - although we don't have the disk space available to do a D&L (long story!).

Yikes.

I've had a response from Progress with a way of working out how much space the LOBs are taking up

I don't use LOBs but for those who do would you care to share?

 

[Cringer]

Use length(lv-Lob,"RAW").

Edit: That gives you the number of bytes the lob takes up so you can tot it up via a for each on the table to give you the full space taken. The value for all our lobs plus the space on disk from the latest dbanalys is pretty much exactly the size of the db.

 

[RealHeavyDude]

Acutally, since we store all LOBs in dedicated storage areas I can live with only knowing the size of these areas. I have to add that I have developed a process on how df changes are deployed to test and production systems that ensures that invalid storage area associationes - be it tables, indexes or LOBs - are rejected.

Heavy Regards, RealHeavyDude.

 

[Rob Fitzpatrick]

I have to add that I have developed a process on how df changes are deployed to test and production systems that ensures that invalid storage area associationes - be it tables, indexes or LOBs - are rejected.

Interesting. Does that mean you parse the .df and compare the assigned area name to the object type? Like if the statement is "ADD TABLE <table>" followed by "AREA <area>", you reject the .df if the name of <area> matches "index*" or "LOB*" (or whatever your area naming convention is)?

 

[RealHeavyDude]

Yes, that's exactly what I do - parse the df for the area names associated with ADD TABLE, ADD INDEX and LOB-AREA.

I have to add that we follow strict rules as to how areas are named. The area name is always prefixed with data, index or lob and the part of the name after the prefix always matches for a table / index area pairing. I check the df against this rules and can then reject the df when a developer associates an index to a table area or vice versa, or when a developer associateds the table to area data_xyz and its indexes to index_abc.

Our main production database is about 500 GB large and consists of rougly 1700 tables. At this time we have only one storage area to hold all LOBs which is about 50 GB large. Needless to say that I don't like to have such a large LOB area. But then again, business users love to be able to attach documents to any kind of information - you are giving them the small finger and they take your hand ...

Heavy Regards, RealHeavyDude.