RE: Specifying Maximum Length of BLOB

[Bruce Vining <bvining@xxxxxxxxxx>]

Included in the 32K max record length? Yes (SQL0604 - ALLOCATE rule)

Allocate and then look?  Kind of.  Some buffers can be reused so you may
get a 2GB buffer allocated but when used in conjunction with X BLOBs at
runtime  none of the BLOBs exceed Y MB in reality.  It's the reuse feature
that generally is 'goodness' but in this case wasteful due to you
specifying a size way beyond what is really needed.  The reuse is good as
it avoids repititive buffer allocations.

Was 16MB just a number I picked or something more meaningful?  It is
something more meaningful :-)  Stay at or below 16MB if possible.

Can ALTER change the BLOB characteristics?  Yes, you can change BLOB size
and ALLOCATE (or at least it just worked for me on a V5R3 system with a
quick test)

Bruce Vining



                                                                           
             "Walden H.                                                    
             Leverich"                                                     
             <WaldenL@techsoft                                          To 
             inc.com>                  "Midrange Systems Technical         
             Sent by:                  Discussion"                         
             midrange-l-bounce         <midrange-l@xxxxxxxxxxxx>           
             s@xxxxxxxxxxxx                                             cc 
                                                                           
                                                                   Subject 
             01/05/2006 03:02          RE: Specifying Maximum Length of    
             PM                        BLOB                                
                                                                           
                                                                           
             Please respond to                                             
             Midrange Systems                                              
                 Technical                                                 
                Discussion                                                 
                                                                           
                                                                           




>If you use ALLOCATE then the BLOB (when less than or equal to ALLOCATE
>size) will be stored in the same base record, and one I0 operation will
get both.

Great to know. I assume that that also means that the blob field will
take that amount of space in the record even if it's smaller than the
allocate size, right? And I assume that space is included in the max 32K
record size, right?

>Allocating a 2GB buffer can be more resource intensive than allocating
a 100K buffer...

Yup. But are you saying you allocate the space and then look at the
actual size?

>I would suggest trying to define the BLOB column as up to 16MB rather
than 2GB
>when you really don't need it any larger.

16M is a "magic" number on the iSeries. Was that just a number you
picked, or do you do one thing when the max size is < an extent and
another when it's > 16M?

I guess my issue is, if I don't know the max size, why limit myself.
It's not _likely_ that my blobs would be > a couple meg, but in my case
I'm storing RTF documents, and if the user inserts a couple of images in
a word doc and then converts to RTF it can get big fast!

Also, Can I modify the max size with an alter table, and if so, how ugly
is that? If I put a max of 15M but discover I need to go to 30M after I
have a million rows, have I created a nightmere for myself?

The other obvious solution is to store the RTF file in the IFS and store
a link (datalink, or just a varchar w/the path) in the database.

Thanks,
-Walden


------------
Walden H Leverich III
Tech Software
(516) 627-3800 x3051
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.