Flat file woes, can an index/lf be added with extra fields for date/sequence?

[ChrisH]

I have not dealt with flat files in ages but I have two that have shown up that developers use for research, however they are 500g+ in size so searches are annoying. I cannot change the flat file but I would like to add a date field and perhaps another through a view/logical/etc. Is this possible?

[tomliotta]

What is "add a date field"? The only "date" that seems reasonable is one based on CURRENT DATE, that doesn't seem useful.

[kitvb1]

If you want an LF, you could use the substring function to define the field. Then define the new field as a key.

...
A            ALTB38             I      SST(ALTI38 1 25)            
A                                      TEXT('Alternative Item Nr')
...
A          K PNUM38

Kit
www.ecofitonline.com
DeskfIT - ChangefIT - XrefIT

[ChrisH]

With the physical file layout untouchable I need a way to delete records based on age and make it easier for a person to search the file for data needed to solve a problem. No program reads the file, only writes are done and its effectively just a copy of incoming data. It is not maintained, does not allow reuse, etc. So I am trying to attach a value/values that make it easier to clean up and search. Right now all I can do it is swap it out for an empty file periodically and delete the older one when the data is no longer relevant

[tomliotta]

Okay, so assume that this file is named MYDATA and it consists of a bunch of CHAR (128) records. As it is right now, record after record is written to the file. The file is apparently a kind of "log" that tracks whatever the records are. That's all fine except you'd like the file to also have a second field that's either a DATE or TIMESTAMP field that tells when each record is written to the file. That would let you run a DELETE...WHERE... statement periodically to delete old records, right?

If so, then imagine creating a new table named MYDATAPF that would have those two field definitions. The new DATE or TIMESTAMP field would be defined with DEFAULT CURRENT_DATE or CURRENT_TIMESTAMP. You could then INSERT the CHAR (128) data from MYDATA into your new table and have the new field default value be set in every row.

Then consider dropping table MYDATA. It's not really needed since you now have the data in the form that you need. Unfortunately, you have existing programming that needs a file named MYDATA and having a CHAR (128) field.

Well, wouldn't it be nice if you could create one, e.g., with something like CREATE VIEW MYDATA AS SELECT MYCHAR128 FROM MYDATAPF RCDFMT MYDATARF? About all you'd need to do is match the names and the single field data definition. As long as all the names and the format ID match the original physical file and the authorities get set to match what exists now, your programming shouldn't even notice a difference. And as long as you could write to the new view and have the DEFAULT field handle itself, it'd be just about perfect. You wouldn't have to change any existing application programming at all.

You just need a system and database that could handle such a change.

With a little more info about exactly what definitions you have now and how your programming adds records, maybe we could work out enough details to make it work for you.