vax manipulation of .DAT file

[Ratzie]

Here is what I need to do, I have been trying to manipulate a file that is on an alpha vax machine.

Since my skills are on unix, I transfer the file to a unix system, and run a perl script to "massage" data.

This what I need to do:
have a flat file the is 773 charecters long.

What I need to do is swap two data fields around.
Both are 38 characters, so I do not need to pad. (blank spaces)

I need to swap char 1-38 data field with char 87-124 data field for each line.
BUT only on lines that at the 206 & 207 char = XG.

But, every time I place the file back my system craps out.

Here is the dir /full on the correct file and on the transfered file:
CIRCUIT.BAK;7 File ID: (196,50,0)
Size: 91700/91700 Owner: [SSTRS,SSTRSSYS]
Created: 9-SEP-2006 21:02:08.37
Revised: 9-SEP-2006 21:04:10.52 (1)
Expires:
Backup:
Effective:
Recording:
File organization: Indexed, Prolog: 3, Using 8 keys
In 16 areas
Shelved state: Online
File attributes: Allocation: 91700, Extend: 6553, Maximum bucket size: 9
Global buffer count: 450, No version limit
Contiguous best try
Record format: Fixed length 773 byte records
Record attributes: Carriage return carriage control
RMS attributes: None
Journaling enabled: None
File protection: System:RWED, Owner:RWED, Group:RE, World:
Access Cntrl List: None


******************************
CIRCUIT.DAT;7 File ID: (81,55,0)
Size: 132706/132708 Owner: [SSTRS,SSTRSSYS]
Created: 11-SEP-2006 16:58:22.67
Revised: 11-SEP-2006 17:01:24.55 (1)
Expires:
Backup:
Effective:
Recording:
File organization: Sequential
Shelved state: Online
File attributes: Allocation: 132708, Extend: 0, Global buffer count: 0
No version limit
Record format: Variable length, maximum 0 bytes, longest 773 bytes
Record attributes: Carriage return carriage control
RMS attributes: None
Journaling enabled: None
File protection: System:RWED, Owner:RWED, Group:RE, World:
Access Cntrl List: None


I guess there are two questions:
1) what am I doing wrong
2) I have no idea how to write a vax script!

[Steven Schweda]

> File organization: Indexed, Prolog: 3, Using 8 keys

> File organization: Sequential

Well, I guess you hosed that one. There's a
difference between "massage" and "grind into
dust". This is an indexed file, and, as such,
it has some internal structure, so it's not
likely to survive intact after being
converted to a simple stream of bytes.

Sadly, I don't do enough with indexed files
to suggest anything more than to stop doing
what you're doing now, as it's probably
doomed.

[Andy Bustamante]

Here's the key bit of info from circuit.bak:

>>File organization: Indexed, Prolog: 3, Using 8 keys

You're moving an indexed file to a system that doesn't support the file structure. The first step is to convert from indexed to to sequential file.

Create a file definition

$ analyze/rms_file/fdl

Edit the FDL file

$ edit/fdl circuit.fdl

change the format from index to sequential

$ convert circuit.bak /fdl=circuit.fdl circuit.asc

Move circuit.asc to your unix system and go forward.


Andy

[Andy Bustamante]

Continued:

Now if you want to move the file back into service on the VMS side, you'll want to run convert again, using the original FDL.

I'm assuming you're stopping any updates on the file in use while this processes takes place.

Andy

[Hein van den Heuvel · Accepted solution]

> I guess there are two questions:
> 1) what am I doing wrong
> 2) I have no idea how to write a vax script!

Answer 1: You have no idea what you are doing. And I mean that in the nicest possible way! Who asked you to do this with so little guidance!?

Answer 2: There is no such thing as a VAX script. VAX is hardware, Alpha is different hardware. Both run the OpenVMS Operatnig system. Compare to PA-risc, Itanium, HPUX.

" a unix system, and run a perl script to "massage" data."
You can get perl on OpenVMS

" my system craps out. "
And the error message is...


You face several challenges, but you may be close.

First let me explain what goes wrong.

- The input file is not just TEXT, it is INDEXED, has (many!) KEYS as well as data.
- In you transfer you lose the index, unix does not knwo about this.

To resolve then you may be able to 'simply' do:

$ANAL/RMS/FDL circuit.bak ! Will take a minute
$CONVE/FDL=circuit/STAT/SORT/FAST circuit.unix circuit.new

Now check with DIR/FULL, and rename as needed.
The ANAL/RMS will create an 'FDL' file describing the keys as found in the backup file.
The CONVERT will take the file from unix, and apply the same key structure as defiend by that same FDL file to a new, compatible, output file.

There is a DCL (VMS native script) way to do
this. Using FAKES offset it would look like:


$ type tmp.com
$convert/sort/stat/pad="_"/trun/fdl=sys$input: sys$input tmp.idx
$deck
file; org ind; record; form fixed; size 76; key 0; seg0_l 4
$eod
aap xx1111AAP
noot XG2222NOOT
mies XG3333MIES
teun xx4444TEUN
$
$type tmp.idx ! Before picture
$
$open/read/write tmp tmp.idx
$loop:
$read/end=done tmp record
$if "XG".nes.F$EXTR(6,2,record) then goto loop
$hold = F$EXTR(8,4,record)
$record[8,4] := "''F$EXTR(12,4,record)'"
$record[12,4] := "''hold'"
$write/update/symbol tmp record
$goto loop
$
$done:
$close tmp
$write sys$output "--- after edit ---"
$ type tmp.idx


The challenges you face are

1) If the PRIMARY key is part of that 1-38 range, which is extremely likely, then the record can not be updated but needs to be deleted and re-writted. Do-able, but not pretty

2) Depending on the OpenVMS verion used, DCL may BARF at record larger then 256 bytes for certain operations. It shouldn't. The limit is 1024, but it barfs with 776 for the string operation:
[offset,length] := other_string


Welcome to OpenVMS
Now please get some help and come to appreciate the neat things it has to offer.

Hein.

[Hein van den Heuvel]

Please ask around in the site which tools they normaly use.

They may well have DATATRIEVE or COBOL or BASIC. The task you mention is trivial in any of those languages.

Going the 'flat file' route is not bad either, but you should consider either PERL or a TPU procedur to just do the job on VMS

More over, you should try to understand the data. That selection field at offset 206 may well be an ALTERNATE KEY.
Let's say it is alternate key number 4.

A dcl script would then look like:

$OPEN...
$READ/ERRO=done/INDEX=4/KEY="XG"/MATCH=GE...
$loop:
$if "XG".nes.F$EXTR(6,2,record) then goto done
$swap...
$READ/END=done...
$goto loop:

The script got a little longer, but if you have only a few records to update then the runtime may have come down from minutes to sub-second. If you need to do thise frequently, then that's important.

To satisfy out curiosity please attach the FDL file as .TXT and or reply here with the output from

$ANALYZE/RMS/FDL CIRCUIT.BAK
$SEARCH circuit.fdl "KEY ",_LEN,_POS

cheers,
Hein.

[Ratzie]

Yes, I beleive I am way over my head.
But, support is hard to come by. So, I was under the impression that the "file" manipulation can be done.

I was thinking the same thing with the first 1-38 characters is a primary key.
But, the way I tested it, is similar to an oracle check. I was able to go into the application and change that field to something else with out it complaining, so I thought it could be done.

-How do I tell if it is a primary key?
If I look at the fdl file it mentions nothing about primary key for first key.
KEY 0
CHANGES no
DATA_KEY_COMPRESSION yes
DATA_RECORD_COMPRESSION yes
DATA_AREA 0
DATA_FILL 100
DUPLICATES no
INDEX_AREA 1
INDEX_COMPRESSION no
INDEX_FILL 100
LEVEL1_INDEX_AREA 1
NAME "COMP CCT"
NULL_KEY no
PROLOG 3
SEG0_LENGTH 38
SEG0_POSITION 0
TYPE string

My support person said it can be done in DCL, with some hints but I can not make heads or tails.

***All I want to do is swap two data fields around.
Both are 38 characters, so I do not need to pad. (blank spaces)

I need to swap char 1-38 data field with char 87-124 data field for each line.
BUT only on lines that at the 206 & 207 char = XG.



$ COPY SSTRS_CIRCUIT_FILE :



The guts of the script would look something like this:



$!

$! Open old and new files

$!

$ OPEN/READ old_file : /ERROR=all_done

$ OPEN/WRITE new_file :NEW_ /ERROR=all_done

$!

$! For each record in old file:

$!

$ xg_value = "XG" ! note: pad this with blanks to the size of the Svc Type field

$next_record:

$!

$! Read the old record. The record buffer can be treated like a string,

$! which allows you to extract and examine potions of it.

$!

$ READ old_file old_record /ERROR=all_done /END_OF_FILE=all_done

$!

$! Extract the Svc Type; if it is "XG", rebuild the record; otherwise, just

$! write the old record to the new file. You will need to know where the

$! Svc Type field is in the record () and how many bytes long

$! it is.

$!

$ svc_type = F$EXTRACT( , , old_record )

$ IF ( svc_type .EQS. xg_value )

$ THEN

$!

$! This part is cumbersome. You need to extract the parts of the record before,

$! between, and after the two fields which are being swapped (there is no "before"

$! if one of them is the first field in the record, no "after" if one of them is the

$! last field in the record, and no "between" if they are adjacent in the record),

$! as well as the field values. Those (5 or fewer) parts need to be concatenated

$! together to form the new record buffer. If the field values have different lengths,

$! it will be necessary to pad/trim them to fit - when the WRITE command is

$! executed, the length of the record buffer must be correct for the file (773 bytes

$! for SSTRS_CIRCUIT_FILE).

$!

$ new_record = F$EXTRACT( 0, , old_record ) - ! "before"?

+ F$EXTRACT( , , old_record ) - ! first field being swapped

+ F$EXTRACT( , , old_record ) - ! "between"

+ F$EXTRACT( , , old_record ) - ! second field being swapped

+ F$EXTRACT( , F$LENGTH( old_record ) - , old_record ) ! "after"

$ ELSE

$ new_record = old_record

$ ENDIF

$ WRITE new_file new_record

$ GOTO next_record

$!

$! Clean up

$!

$all_done:

$ CLOSE old_file

$ CLOSE new_file



Then, if the script has executed properly:



$ COPY :NEW_ SSTRS_CIRCUIT_FILE



To pad field values to correct widths, it's easiest to use F$FAO.



field_value = F$FAO( "!AS", field_argument ) ! n = field width



For example, suppose the Svc Type field is 4 characters wide. Then a correctly formatted "XG" value could be obtained with:



xg_value = F$FAO( "!4AS", "XG" )

[Hein van den Heuvel]

The hints you were provided were not too bad.
Why did you not share those right away?

It looks like you woudl still need a CONVERT to get back to an indexed file.

Looks like you have an FDL already, too bad you did not show the 'search' output.

The application can update the records if it replaces a standard update by a read+write, which is what COBOL does automatically.

How many records are to be changed?
If is is only a fraction, then personally I would update the records 'in place'.

Here is my example again, with some of the hints, but with the wrong offsets/size to make the demonstration on 4 sample records easy to see.

First, as before..
However... I changes it to change the flag XG to something else to make sure the loop does not find the same record back with its update key value.
Just try it!

$!---- tmp.com ----
$delete *.tmp.*
$convert/sort/stat/pad="_"/trun/fdl=sys$input: sys$input tmp.idx
$deck
file; org ind; record; form fixed; size 776; key 0; seg0_l 4
$eod
aap xx1111AAP
noot XG2222NOOT
mies XG3333MIES
teun xx4444TEUN
$purge/nolog tmp.idx
$write sys$output "---- before ---- "
$convert/fdl=sys$input:/trun tmp.idx tmp.tmp
file; org seq; record; form fixed; size 20
$type tmp.tmp ! Bug, should not need intermediate file, but sys$output did not .
$
$close/nolog tmp
$open/write out deleted.tmp
$open/read/write tmp tmp.idx
$
$loop:
$read/end=done tmp record
$loop_already_read:
$if "XG".nes.F$EXTR(6,2,record) then goto loop
$write/symbo out record ! Just in case.
$
$old_key = F$EXTR(0,4,record)
$new_key = F$EXTR(8,4,record)
$mid = F$EXTR(4,2,record)
$flag = "xx"
$rest = F$EXTR(12,999,record)
$read/dele/key=&old_key tmp record
$new_record = new_key + mid + flag + old_key + rest
$write/symbol tmp new_record
$read/key=&old_key/match=GT tmp record
$goto loop_already_read
$
$done:
$close tmp
$close out
$write sys$output "--- after edit ---"
$convert/fdl=sys$input:/trun tmp.idx tmp.tmp
file; org seq; record; form fixed; size 20
$
$type tmp.tmp


Next, a method using a 'helper file'
First identify the records to be updated,
then read & delete those records and re-write updated.



$! ---- tmp2.com
$
$close/nolog candidates
$close/nolog data
$delete *.tmp.*
$convert/sort/pad="_"/trun/fdl=sys$input: sys$input tmp.idx
$deck
file; org ind; record; form fixed; size 776; key 0; seg0_l 4
$eod
aap xx1111AAP
noot XG2222NOOT
mies XG3333MIES
teun xx4444TEUN
$purge/nolog tmp.idx
$write sys$output "---- before ---- "
$convert/fdl=sys$input:/trun tmp.idx tmp.tmp
file; org seq; record; form fixed; size 20
$type tmp.tmp ! Bug, should not need intermediate file, but sys$output did not .
$
$open/read/write data tmp.idx
$open/write candidates candidates.tmp
$
$find_candidate_loop:
$read/end=candidate_done data record
$if "XG".eqs.F$EXTR(6,2,record) then write/symbo candidates record
$goto find_candidate_loop
$
$candidate_done:
$close candidates
$open/read candidates candidates.tmp
$loop:
$read/end=done candidates record
$old_key = F$EXTR(0,4,record)
$new_key = F$EXTR(8,4,record)
$mid = F$EXTR(4,4,record)
$rest = F$EXTR(12,999,record)
$read/dele/key=&old_key data record
$new_record = new_key + mid + old_key + rest
$write/symbol data new_record
$goto loop
$
$done:
$close data
$close candidates
$write sys$output "--- after edit ---"
$convert/fdl=sys$input:/trun tmp.idx tmp.tmp
file; org seq; record; form fixed; size 20
$
$type tmp.tmp


Enjoy!
Hein.

[Jan van den Ende]

LHradowy,

first, let me follow Hein in welcoming you to VMS (or OpenVMS as is the politically correct, marketeering, official name nowadays).

second, congrats on finding Hein to help you: he is _THE_ worldwide expert on RMS (the VMS file system).

for us lesser souls, it would be very helpful if you could execute (allready indicated by Hein):
$ anal/rms/fdl circuit.dat /output=circuit.txt
and attach the resulting circuit.txt to your posting.

Some of the assumptions Hein made above will be confirmed or negated, which will make the answers more precise.
Also, the output of
$ write sys$output f%getsyi("version") would help.

Proost.

Have one on me.

jpe

[Ratzie]

Wow!
I appreciate all you work! Thank you.
Sorry, was trying to sort thru the code and missed the last line.

Basically KEY 0 and KEY1 need to be swapped where there is XG starting at 206 character.
The file is 773 and that field will not need to be padded.

And here is the SEARCH results.
KEY 0
NULL_KEY no
SEG0_LENGTH 38
SEG0_POSITION 0
KEY 1
NULL_KEY yes
SEG0_LENGTH 38
SEG0_POSITION 86
KEY 2
NULL_KEY yes
SEG0_LENGTH 38
SEG0_POSITION 38
KEY 3
NULL_KEY yes
SEG0_LENGTH 10
SEG0_POSITION 76
KEY 4
NULL_KEY no
SEG0_LENGTH 30
SEG0_POSITION 124
KEY 5
NULL_KEY yes
SEG0_LENGTH 30
SEG0_POSITION 154
KEY 6
NULL_KEY yes
SEG0_LENGTH 5
SEG0_POSITION 184
KEY 7
NULL_KEY yes
SEG0_LENGTH 8
SEG0_POSITION 189
ANALYSIS_OF_KEY 0
MEAN_DATA_LENGTH 773
MEAN_INDEX_LENGTH 40
ANALYSIS_OF_KEY 1
MEAN_DATA_LENGTH 14
MEAN_INDEX_LENGTH 40
ANALYSIS_OF_KEY 2
MEAN_DATA_LENGTH 15
MEAN_INDEX_LENGTH 41
ANALYSIS_OF_KEY 3
MEAN_DATA_LENGTH 21
MEAN_INDEX_LENGTH 13
ANALYSIS_OF_KEY 4
MEAN_DATA_LENGTH 28
MEAN_INDEX_LENGTH 33
ANALYSIS_OF_KEY 5
MEAN_DATA_LENGTH 40
MEAN_INDEX_LENGTH 33
ANALYSIS_OF_KEY 6
MEAN_DATA_LENGTH 1820
MEAN_INDEX_LENGTH 8
ANALYSIS_OF_KEY 7
MEAN_DATA_LENGTH 21
MEAN_INDEX_LENGTH 11

[Hein van den Heuvel]

A lot of words were written already (by me :-), but it may still be simple.

Did you try a straight 'convert' on the file that was 'fixed' on Unix, using the fdl?
That may be all that is needed.

Below you'll find one final contribution, with a vms-only dcl script doing the work pretty much as outlined. It is UNTESTED, so read and study carefully. Use VMS HELP as needed.
Along its path it reports (or intents to, remember untested :-) the first 3 records modified and actually dumps the last one of those to allow you to verify the changes before putting the new file into operation.

Enjoy,
Hein.

$old = "circuit.bak"
$new = "circuit.new"
$fdl = "circuit.fdl"
$tmp = "tmp.tmp"
$mod = 0
$num = 0
$
$close/nolog old
$close/nolog tmp
$creat/fdl=sys$input 'tmp
file; allo 20000; exte 5000; record; size 773; form fixed
$open/appe tmp 'tmp
$open/read old 'old
$
$loop:
$read/end=done old record
$num = num + 1
$if "XG".eqs.F$EXTR(205,2,record)
$then
$ mod = mod + 1
$
$ old_key = F$EXTR(00,38,record)
$ new_key = F$EXTR(86,38,record)
$ mid = F$EXTR(38,48,record)
$ rest = F$EXTR(124,999,record)
$ record = new_key + mid + old_key + rest
$
$ if mod.lt.3
$then
$ write sys$output "Record ''num', Old=''old_key', New=''New_key'"
$ sample = num
$endif
$
$endif
$write/symbol tmp record
$goto loop
$
$done:
$write sys$output "''num' records read, ''mod' modified."
$close old
$close tmp
$dump /record=(count=1,stat='sample') 'tmp
$conv/stat/fast/sort/fdl='fdl' 'tmp 'new
$exit

[Ratzie]

Thank you very much, I have used your script, and get some of the expected results, but with questions.

First it looks like it errors on a variable. That I can not figure out from where.

Unrecognised key word...
Duplicate Key encounter...
It made the swap (all except two) what does this mean?

beta::[SSTRSSYS.DATA] @MOD_CIRCUIT.COM;2
Record 81763, Old=399199 , New=WNPGMB05ONU6

Record 81764, Old=399256 , New=WNPGMB01ONU5

113167 records read, 1426 modified.
%DCL-W-IVKEYW, unrecognized keyword - check validity and spelling
\STAT\
%CONVERT-I-DUP, duplicate key encountered
%CONVERT-I-DUP, duplicate key encountered
%CONVERT-I-DUP, duplicate key encountered
%CONVERT-I-DUP, duplicate key encountered
%CONVERT-I-DUP, duplicate key encountered

CONVERT Statistics
Number of Files Processed: 1
Total Records Processed: 113167 Buffered I/O Count: 215
Total Exception Records: 5 Direct I/O Count: 63498
Total Valid Records: 113162 Page Faults: 12080
Elapsed Time: 0 00:27:06.95 CPU Time: 0 00:08:45.32

I will need to do additional testing with this but I believe I am on to something.

[Hein van den Heuvel]

>> Record 81763, Old=399199 ,New=WNPGMB05ONU6
>> Record 81764, Old=399256 , New=WNPGMB01ONU5

Those are just two key samples from the first records encountered which needed change.

>> 113167 records read, 1426 modified.

Just a status message.
The numbers suggests that an inline delete+rewrite would have been reasonable.

>> %DCL-W-IVKEYW, unrecognized keyword - check validity and spelling
\STAT\

Ooops, that should have been "START"
The DUMP is just an other sanity check that the script is doign the right thing best we can tell.
You can do the same dump on both files and compare byte by byte if need be.


>> %CONVERT-I-DUP, duplicate key encountered
>> %CONVERT-I-DUP, duplicate key encountered

Now you get into application space.
The file is designed NOT to allow duplicates.
The swap apparently created duplicates.
CONVERT can not decide which is good, which is bad.

Please add /EXCEP=circuit.exception to the final convert command line an try again.
Convert will put those problem records in the exception file and yhe application folks will have to decide what to do next.

They may (should!) have the tools to play with records. Datatrieve perhaps.

If they are at loss, the carefully study the other scripts I provided. They show how to read and delete or write records from an indexed file using DCL. You can use that to play with individual records, by primary or by alternate key (add /IND=1)

Try for example
$OPEN/read/share=write x circuit.old
$READ/KEY=WNPGMB05ONU6/IND=1 x y
$SHOW SYMBOL y

Total Exception Records: 5
Direct I/O Count: 63498
Total Valid Records: 113162

That seems reasonable.

>> I will need to do additional testing with this but I believe I am on to something.

Close, very close...
And a lot learned!

Hein.

[Ratzie]

I want to thank Hein for all his work, I can say that I would not be anywhere close if it was not for him.
I have been testing all week, and I am confident that I can go ahead with these changes.

Appreciate the help.
Thanks
Laura