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.

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.

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.. :)

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.

@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.

No

> many fields remain un populated

Are your files normalized?

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

> Are your files normalized?

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

Chris 

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

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.

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