OS400 PF: how to reduce unused space in a BLOB field.

Actual issue :  there is a PF with 5.000.000 records  (reclen 1000 Bytes, but with a BLOB field.) PF occupies about 1,5 TB (!)
A deletion Job deletes older records  periodically. REUSE  is set to *YES.
Problem: BLOB field-size will not be reduced, also if new records have smaller documents (because of optimized application) as in the old, deleted records.

Theoretical solution:  set PF  to RERUSE = *NO,  write new records  - with smaller documents/smaller BLOB-size  on new diskspace, and then do a RGZPFM,
to remove deleted records (with larger BLOBs) may reduces filesize.
But there is not enaugh diskspace for a RGZPFM.
Is there any other solution to reduce unused BLOB-space in a BLOB field ?
(OS400 with V7.3)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gary PattersonVP Technology / Senior Consultant Commented:
Please show DDS or SQL DDL for the file.  

Also, how big is the file (DSPFD), and how much disk space do you have available?  

How many access paths over teh file?

BLOB fields, properly declared, are variable length.   Do you have long sections of embedded blanks or leading or trailing blanks that you can recover by reading, compressing, and updating each row?


1) Save and restore a copy of the file to another system with more disk space and CPYF COMPRESS(*YES) or RGZPGM ALWCANCEL(*NO) there, then restore smaller file back to original system.

2) Temporarily free enough disk space to RGZPFM by backing off other libraries or files, then RGZPFM, and restore libs/objects temporarily removed.

3) Depending on your OS version, you may be able use RGZPFM while active, but it will only compress fixed space, not AUX space where blobs are held - and in you case that is probably where all the excess disk space usage is.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
asdf13Author Commented:
is there any common file tool (beneth RPGIV/SQL) to get Information  (or  stored BLOB documents  itself)   from a BLOB field, defined in SQL created PF ?
Gary PattersonVP Technology / Senior Consultant Commented:
Here is an example of an RPG program to extract BLOBs to a stream file.  Is that what you are looking for?

asdf13Author Commented:
yes, the example is helpful for understandig, how BLOB fields are handled with RPG/SQL.

My concerns -  from System perspective - are:   how to get those "monsterfiles" with 1,5 TB smaller . . .
A question regarding to IBM design :
Why is the BLOB fields-size the real document size, because only a pointer points to BLOB-field space ? This causes an issue, because simple PF member max size is about 1,7 TB . . .  (without partitioned database)
Gary PattersonVP Technology / Senior Consultant Commented:
All of the data resides in the physical file object - fixed fields and LOBs (BLOBs, CLOBs).

Physical files have a "data space", which contains the fixed length portion of each record, including a pointer for each LOB field in the record.

Physical files also have "auxiliary space", which contains LOBs (and possibly some or all of variable length fields).  This pointer in data space points to a location in the "aux space".

Keyed physical files also have an access path - a built-in index.

Strategies for avoiding this problem:

1) SIMPLY DON'T STORE LOBS IN DATABASE FILES!  For a small table, no problem.  But for a table that could grow large, this often isn't the most efficient way to manage this type of data.

Instead, store them as stream files (XML, JPG, etc) in the IFS, and store the path in a text field, or store the path in a DATALINK field, or store them in Amazon S3 (or other local store or cloud-based storage).  Additional benefits often apply: for example, no need to extract and save to the OS file system to use in web pages and other applications that can directly use the stream files.  

2) If you can't remove them, then consider compressing LOBs before storing them, and decompress them when you need to use them.  Trim leading and trailing spaces, then compress data using a user-defined function to reduce storage utilization.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.