convert

[Pam Stafford]

I have a legacy system running on a VAX 4700. we have one file that is too large to run a convert/fdl on due to space constraints. I was of the impression from research that I can backup the file to tape and convert back to disk. Does anyone know how to do that?

[Hein van den Heuvel]

If you go by tape then CONVERT will create a temp file to hold all primary data. No point.

What you want to do (besides getting more and/or bigger disks) is to roll out the file to a single key indexed file with key compression, record compression and index compression enabled and a bucket size of 63.
The (Primary) key deifition should be the same as the original and use:
CONV/STAT/FDL=tmp/FAST/NOSORT old tmp_disk:tmp
You can even use CONV/SHARE, while the app is still active, for this to see how big the file will become.
tmp_disk should be a staging area away from the main file. When that is done, you can (optionally) delete the original (you have a backup right?) and then do the target convert. You also might want to direct sys$scratch to a diffent disk (not in, nor out)
The number of records from the first convert is all you need as input for an edit/fdl/nointer besides any old fdl.

I'll attach a C program that actually does most of the above all for you!

If this does not help enough, then please reply again with a attachemnt hlding a fairly recent ANAL/RMS/FDL output and a "show dev d" output.

[Mobeen_1]

Pam,
I think what you referring to is the convert process in VMS. Its got nothing to do with backing up and restoring the files :-).

Our friend has very rightly presented in the post prior to mine, how to do this and also has offered to give you some tools.

Basically you create the FDL and then move the data from your source files to the dest files.

There are many tools from software vendors available for you to do converts, like process software and others

regards
Mobeen

[Hein van den Heuvel]

Oops, the attach failed. Trying again.

Hein.

[Antoniov.]

Hello Pam,
I've attached a little DCL procedure I use to convert and/or compact indexed files. It's a part of my financial software but may be useful for you.
I've not update so I have to explain some bullets.
a) Procedur is in italian but there are a few string to understand; if you need I can rewrite an english version.
b) Procedur has 3 routine: convert form indexed to sequential, convert sequential to indexed ed indexed to indexed.
c) GZ$* are logical name of my procedure, you could update theese part of code.
d) Convert form indexed to sequential use a sequential file definition called GZ$BIN:KSEQ.FDL; I'll attached it.
d) Convert from sequential to indexed make a new copy of FDL file making some simple optimitation; it's easy add buccket size = 63 as hint of Hein.

When I use this procedure I can compact indexed file and application goes quickly.
As Hein hints, you have define SYS$SCRATCH on a free disk.
If you leave me some times I can rewrite a custome version for you.

Antonio Vigliotti

[Antoniov.]

Here FDL

[Hein van den Heuvel]

Hello there Antoniov,

It is good to see someone else thinking about converting and having procedures in place.

I do have a couple of observations though:

The key idea that I tried to convey in my reply was: DO NOT USE A SEQUENTIAL FILE as intermediate. Doing so is just a waste of time, space and information. Instead, just use an indexed file! The benefit of doing so are:
- The records stay in order by primary key, and that convert knows this automatically.
- The records can be compressed.
- The average compression can be known (good for detail fine tuning).
- The temporary file can still be used for basic reporting and data verification using the primary key.

> it's easy add buccket size = 63 as hint of Hein

Well, please be aware that this hint was strictly intended for the intermediate, single-key, file. For the real production file, 63 is unlikely to be the ideal bucket size (but probably less wrong than any bucket size less than 6 or so).

In your script you MUST add /NOSORT on the convert to avoid convert wasting time sorting by primary when the input file was already sorted (if the primary key for input is the same as for output, then convert defaults to /nosort, but it can not do this for sequential input file (that 'loss of information' I mentioned).

> $ ALLOQ=F$FILE(FSEQ,"EOF")
> $ EXTEN=ALLOQ/8

It is interesting to use the size of the sequential file as the initial indexed file file. It might be too big ( compression ) but probably just fine. (add index, bucket overhead, bucket boundaries).
However... if the intend is to shrink the file to the max, then you probably should start with ALLOQ=ALLOQ/2 or 3*ALLOQ/4 in general. Your script does this, but just for one exception.

> $ CONVERT/STAT 'FIDX' 'FSEQ' /FDL=GZ$BIN:KSEQ

The contents of KSEQ.FDL is equal to just using NL:. So I would recommend using that (really, I mean that, just use CONV/FDL=NL: and you'll have one less file to worry about!)
If you use this for large files however, then you will fragment the output to death.
So be sure to use SET RMS/SEQ/EXT=10000 before that convert, or generate an FDL with ALLOCATON = (indexed-allocation/8)
EXTENT = (allocation/8)

Take a minute and study the C program I attached earlier. You might find it interesting.

Regards,
Hein.

[Antoniov.]

Hein,
I'm honoured you have commented my little procedure and I use your hints for future works.
Your solution it better but I posted my little solution if Pam can't compile using C; I used convertion routine in C and I use DCL when I have no alternative.
About bucket size I'm courios how this work because I always leaved as is; I'll read my VMS documentation about this parameter.
Good idea /NOSORT, I don't know why I haven't thought abotu it; /FLD=NL is good too.
Alloc/8 is a sperimental value: there is no technical reason to this; in theese year I've tried some value and 80% is a good value. Formula is very simple and DCL work well.
In my C application I use more complex dedicated algorithmes.
Hein, thanks.

Antonio Vigliotti

[Doug Phillips]

Hein and all,

The way I read Pam's question, it sounds like she might not have enough free disk to do any of the above suggestions.

Rather than "backup", could she strip out a sequential file to tape via whatever means (DCL, program, convert,...) and since that file will be in primary key sequence, would she be able to then use that file as input to the convert to rebuild her file on disk?

I thought the /nosort would still work on a sequential input file but give an error if it found the primary key out of sequence. Is that wrong?

However, if space is that tight, she would probably have to delete the isam file before recreating it so doing this after doing a good backup would lessen the stomach churn a little;-)

------
Pam,

You say it's a legacy system, but you should be able to pick up another disk drive or a larger drive pretty cheap. There are still people who sell this stuff.

Really, if the file is important, you should be able to justify spending a little on it even if you are planning to move off to another platform someday soon.

You might find that the cost of adding disk is less than the cost of the time you'll waste otherwise. Not even counting the anti-acids you'll be consuming knowing that the only copy of your file exists for a while on a little thin fragile piece of tape!!

I've had situations like yours and have always found that adding bigger or additional storage was the best solution.

Doug

[Hein van den Heuvel]

Doug wrote>
she strip out a sequential file to tape via whatever means (DCL, program, convert,...) and since that file will be in primary key sequence, would she be able to then use that file as input to the convert to rebuild her file on disk?

In my first answer I suggest that convert would use an intermediate file on disk anyway, but thinking more about it, I think you are right and convert, notably in NOSORT mode may be able to load straight from tape.
As you indicate, she would still probably need to delete the original. Yikes! Not my preferred way to run a business!

Doug wrote>
I thought the /nosort would still work on a sequential input file but give an error if it found the primary key out of sequence.

That is correct. But the default becomes to sort, so that can byte you in the behind.

Antoniov wrote>
About bucket size I'm courios how this work because I always leaved as is;

Bucketsize is the single most important tuning knob you have for an indexed file.
edit/fdl/nointeractive will make a reasonable attempt to optimize it automatically, but for serious performance concerns you should try and understand the file better and help EDIT/FDL.

Check out the VMS Guide to file:
http://h71000.www7.hp.com/doc/731FINAL/4506/4506pro_008.html#apps_tuning_indexed_file

Also check out my presentation on the VMS Freeware V5.0 in [RMS_TOOLS]

Hein.