Midrange News for the IBM i Community


Posted by: raja sekhar
How to reduce storage space of a large file with 3000+ fields
has no ratings.
Published: 05 Mar 2015
Revised: 01 Apr 2015 - 789 days ago
Last viewed on: 29 May 2017 (1635 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

How to reduce storage space of a large file with 3000+ fields Published by: raja sekhar on 05 Mar 2015 view comments(12)

We have a file with around 3300 fields with 3000 fields as packed. This file is eating up storage and reaching the limits of 1tb. Out of these fields, many fields remain un populated. I saw VARLEN is useful to reduce storage space but it is meant only for CHAR type. Please advise if there are any other approaches by which we can reduce storage space for this file.

Any help is greatly appreciated

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: GFuste
Premium member *
Jacksonville, FL
Comment on: How to reduce storage space of a large file with 3000+ fields
Posted: 2 years 2 months 27 days 1 hours 3 minutes ago

Have you thought about purging this file?  3300 fields?  How do you even look at this file with DBU or other applications?  Sounds like a database design change or something is needed here.  Using VARCHAR isnt going to solve your problem.  You will simply be trimming leaves off a massive oak tree.

Posted by: dag0000
Premium member *
Comment on: How to reduce storage space of a large file with 3000+ fields
Posted: 2 years 2 months 27 days 8 minutes ago

If there a lot of deleted records in the file. I would look into doing a reorg. If there are a lot of deleted records removing them may solve your immediate issue. Purging data like GFuste recomended would be my next option.

Posted by: raj250b
Premium member *
Comment on: How to reduce storage space of a large file with 3000+ fields
Posted: 2 years 2 months 16 days 6 hours 27 minutes ago

The design was made to reduce I/O. Other than database design change, are there any database compression techniques available in AS400. I have read that DB2 10, has features like DATA COMPRESSION, in which storage space can be gained by eliminating space for unpopulated fields and redundant data. 

Do we have any such techniques in which we can reduce the storage space occupied by this massive file. I am asking this, because not all fields contain data, so we can grant actual disk space for only fields containing data, the structure (though big) doesnt pose any trouble to database. 

I understand that if we change the design we can avoid this, but technically is there a way to handle this.

Any pointers would be greatly appreciated.. :)

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: How to reduce storage space of a large file with 3000+ fields
Posted: 2 years 2 months 15 days 22 hours 56 minutes ago

The only choice you may have is to partition the file. This is SQL-speak for creating a multi-member file to avoid the single member limitations.

Posted by: raj250b
Premium member *
Comment on: How to reduce storage space of a large file with 3000+ fields
Posted: 2 years 2 months 9 days 3 hours 15 minutes ago

@Bobcozzi: This way we are expanding the available space for the file. Is there any way we can shrink the data and store it in the existing alloted space. Sorry if am bugging... But thanks a lot for your time and advise.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: How to reduce storage space of a large file with 3000+ fields
Posted: 2 years 2 months 8 days 18 hours 10 minutes ago

No

Posted by: tdaly
Premium member *
Comment on: How to reduce storage space of a large file with 3000+ fields
Posted: 2 years 2 months 7 days 14 hours 36 minutes ago

> many fields remain un populated

 

Are your files normalized?

Posted by: DaleB
Premium member *
Reading, PA
Comment on: How to reduce storage space of a large file with 3000+ fields
Posted: 2 years 2 months 7 days 21 minutes ago

It might help if you could explain what you do with this file. Why so many fields, with most of them unpopulated?

Posted by: Ringer
Premium member *
Comment on: How to reduce storage space of a large file with 3000+ fields
Posted: 2 years 2 months 6 days 19 hours 16 minutes ago

> Are your files normalized?

I'll guess no. How did this design make it past peer review? And Q/A? 

Chris 

Posted by: raj250b
Premium member *
Comment on: How to reduce storage space of a large file with 3000+ fields
Posted: 2 years 2 months 2 days 1 hours 16 minutes ago

It is a ledger file which maintains 53 week data in respective week columns. And for each transaction, several record types will be written.

Consider we are writing details of 53th week. Then only columns of 53th week gets loaded and the rest of 52 week columns remain un-populated.

As I said earlier, I understand there is a design flaw. As of now, technically is there any way to

1) To freeup disk space for un occupied columns. Because once the record is written, space for entire record length is filled up

2) Can there be dynamic variable size (Note: 90% fields are packed)  

3) Can we split this file without major program changes.

4) Are there any database techniques to handle this

Posted by: DaleB
Premium member *
Reading, PA
Comment on: How to reduce storage space of a large file with 3000+ fields
Posted: 2 years 2 months 2 days 23 minutes ago

Decimal fields (and all other numeric types) are fixed length; there's no way to make them variable size. Making them NULL-capable wouldn't help; not only does that add programming changes to set and test the null indicators, it actually adds more space to each record.

You need to normalize; that much is clear. But this will require programming changes on anything that writes or reads to the file.

How far back does this data go? Can you archive and purge older records? That could at least buy you some time while working on the main problem.

Posted by: GFuste
Premium member *
Jacksonville, FL
Comment on: How to reduce storage space of a large file with 3000+ fields
Posted: 2 years 1 months 27 days 19 hours 22 minutes ago

How much storage space is this thread taking up on the MidrangeNews server?