Re: VSAM files to Oracle (or other RDMS)

andreyNSPAM_at_bookexchange.net (NetComrade) wrote in message news:<3f21aabd.347726714_at_nyc.news.speakeasy.net>...
> Is there are a good document describing how to convert files from VSAM
> (mainframe) to Oracle?
> Are there are any straightforward caveats?
> My understanding is, there is a possibility to have rows in VSAM files
> that are different in structure from other rows. How does one get
> around that?
> What would be the best method of extraction? (Pro*Cobol?)
> What are the best 'practices' to convert the files into decent
> relational tables?
> Are there are any books available?
> The best info would be for somebody who knows VSAM well, but Oracle
> (or any relational DB) not so much, beyond concepts.
> Any pointers, links, success/horror stories are greatly appreciated.
>
> Thanks in advance
> .......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email

Net, Andrew and Billy both gave you some good advice. The key point will be to find record layouts for the data stored in the VSAM files so you know what the data is. You will want to design a set of tables in Oracle to hold the data. It is possible that the VSAM files already represent a relation structure, that is, each file coresponds to a table, but you probably are not going to be that lucky.

And yes, it is possible that the data in a single VSAM file represents multiple records. It was very common in the older days to use a type indicator which is part of the key to create a hierarchy of records in the file. Program logic would navigate the hierarchy. Here is an example:

key => part_no + record type [+ sequence]

1001001 10  ==>  root part record
1001001 20  ==>  part receipt record
1001001 30  ==>  part cost record

Or some such. The dependent segments may repeat. In this example there would probably be multiple part receipt records. To order the records a additional set of bytes would often be added to the key. Depending on the record type the data might be the receiver number or some other value that would be used to order the records within the sub-type. What is in the third portion of the key may well vary depending on the record type. A quick and dirty approach may be to turn each segment type into a dependent table that inherits the key of the parent if the time/money for a clean design is not allowed.

• Mark D Powell --