Re: VSAM files to Oracle (or other RDMS)

andreyNSPAM_at_bookexchange.net (NetComrade)

> Is there are a good document describing how to convert files from VSAM
> (mainframe) to Oracle?

I doubt it. Conversion is IMO the wrong approach.

> Are there are any straightforward caveats?

Use logic & common sense. :-)

> 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?

It is possible, but then such a VSAM file design was usually made that way for performance reasons, e.g. emulating clustering by having a header struct record followed by its children - as oppose to storing it in separate VSAM files. It worked well with Cobol programming and the standard type of merge processing logic we used in the old mainframe days.

Do not think ito of VSAM file as a straight mapping to an Oracle table (the reason why I said that conversion is the wrong approach).

> What would be the best method of extraction? (Pro*Cobol?)

Not necassarily. If on the same platform (VSAM and Oracle), then yes. If not, I would use Cobol to extract the VSAM data into a fixed record length flat file for importing into Oracle via SQL*Loader.

> What are the best 'practices' to convert the files into decent
> relational tables?

Codd's standard relational model for a 3rd normal form design.

You have to rewrite your Cobol VSAM code anyway for Oracle. Rather do it properly in Oracle.

Many years ago in a government department far far away.. they got the job of porting/converting a Cobol CICS ISAM/VSAM environment to Natural/Adabase environment. I joined as a junior programmer towards the end of project. I was given the Cobol source code printout and told to code that in Natural. Everything was a straight port. System took almost a year to port. After 2 months of testing, the users threw the system out. It did not do anything new. Did not satisfy new requirements. Hell, it even looked like the old system. And had all of the old system's problems, peculiarities and even bugs.

Moral of the story - a straight port is often stupid as it does not make the new system any "better" than the old one.

ROI. User expectations. New platform for future growth. That is just some of what should be taken into consideration.

> 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.

No horror stories from my side. Some years ago we did a "port" of a mainframe database (binary files) data to Oracle. We designed a "proper" relational db in Oracle. Then the mapping of the mainframe data to the Oracle Schema's tables were done. We wrote Cobol programs to pull the data from the mainframe into flat files, which in turn was FTP'ed to Unix and SQL*Load'ed into Oracle.

--
Billy