Field extentions, file conversion

[Brian Duddy]

I have to increase the size of several fields in an indexed file. Our files are created using a .fdl file with basic info like key structure, no field info. The files are accessed using vax basic code, the program includes a .map definition of all the fields, there's no key info in the maps, just data type and size for each field

i've previously extended field sizes on other files by creating a new .map, reading in the old records with the old map and writing records to the new file with the new map, all in vax basic code, i create the new file at dcl with create/fdl=

i'm trying this on a larger file (in test environment) and it takes over 48 hours, it slows down the further it gets.

although it worked eventually i have to do this for real with as little down time as possible and on several smaller files as well

I've read the help on analyze and convert but that doesn't work because the .fdl only defines key structure. Its the .map that defines all the field sizes and the .map's are not mentioned in the utilities

is there a utility/tool that i can use to do this

thanks

sys info
Alpha ES45 System Running OpenVMS V7.3-2

file info
File org: Indexed, Prolog: 3, Using 9 keys
File attributes: Allocation: 7155225 blocks
Record format: Fixed length 8160 byte recs

[Hein van den Heuvel · Accepted solution]

How many records?

Sounds to me you are creating the output file as indexed correct? Well, that's the wrong way to go about a problem like this.

Just create the output file and sequential and then: CONVERT/FDL/NOSORT/STAT=FULL/SECON=5

Yes there are tools to help with this, but you are almost ready with the tool you wrote. Just change the "OPEN #x FOR OUTPUT, INDEXED...." to drop the indexed and the keys, and ofcourse ALLOW NONE

How many records?
Be sure to pre-allocate the SEQUENTIAL temporary file to be number-of-records * new-record-size / 512.

And be sure te SET RMS /SEQ/BUF=4/BLO=112 before the RUN.

IF (and only IF) the records are highly compressed, and the calculated output size is well over 20m blocks (or so), then you might want to consider an INDEXED temp file,
with ONLY THE PRIMARY KEY, all compression, and a bucket size of 63... preallocated 5M blocks.

> is there a utility/tool that i can use to do this

Yes, typically I like plain old DATATRIEVE.
Cobol "MOVE CORRESPONDING" is also 'handy'

A professional tool to do this, and possibly faster than RMS ever can is vSELECT by EGH. (Evans Griffiths and Hart)

Contact me if interested in details about the select product, or need further, professional, help with the stated problem.

Hope this helps some already,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[Brian Duddy]

approx 3M recs

yes i am creating the new file with the full index and opening with index in my code

i never thought of doing it as sequential and then converting it

sounds like a plan

thanks for your help

brian

[Graham Burley]

> is there a utility/tool that i can use to do this

SORT could do it using a Specification File, it's really a very handy tool once you get into it. SORT is not just for SORTing :-)

[Hein van den Heuvel]

[I know the topic was closed, but still...]

Records with 9 keys, would obviously require 9 writes at the very minimum.

But here you might frequently need to grab a fresh empty bucket, update the areas, and add index records, more likely leading to 15 write IOs/record.
But to find where to write, you need to read down the index.
So that's about 3 read IOs for each key. And it is unlikely you had provided 30+ buffers, so you probably have 0% RMS cache hit rate (but 99% XFC cache hits).
In all I would expect an average of 40+ IOs per record once the indexes have build up some.
So I would expect the original process to use 150 million IOs or so, 30+M of them writes. Convert will do about a million IOs, half of them writes.

Also note that if the 3M guess is close, then there is a massive compression going on.
Let's say 5M blocks out of 7.1M were used for actual record storage. That's but 1 or 1 1/2 block per record, where the record itself would take, and will take in the seuqential file, 16 blocks. 85% compression!
The temp file might become 45M blocks.
Is the system ready to handle that?

Finally, Any INDEXED file which is created directly from a BASIC OPEN, is bounded to be 'sub-optimal'. It is likely to be just horrible and wide open for significant performance improvements.

If the application is at all relevant, then PLEASE review this approach and consider using FDL files and proper tuning.
You may want to solicit external resources to get help with that.

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[Brian Duddy]

don't worry, i do not create index files using basic open, i create any new file with fdl but use basic for all other operations, well apart from a reorg/optimisation. I open with index in basic, i do not create

anyway a new system on a new platform is planned this file conversion is just the first step on the long road to migration and migrane

thanks again

[Hein van den Heuvel]

Excellent! Thanks for the feedback. Hein.