Converting RMS files to Oracle 10g DB

[Ian Derringer]

Help! Our developers looking into using SQLplus to convert our RMS file into Oracle 10g DB and they said it will take roughly 350-500 hours to do so. Is there any tools out there that I can find out or to optimize the conversion process a little bit less?? I am looking for some ideas on how to cut the conversion time atleast half.

Please help.

Thank you in advance.

Ian

[Hein van den Heuvel]

>> Our developers looking into using SQLplus to convert our RMS file into Oracle 10g DB and they said it will take roughly 350-500 hours to do so.

Hmmm, plenty of time to look for new developers, and thus get the job done sooner.

Why not use SQL*loader? It is made for that job. Direct path load, simple load, whatever.

Or write an OCI program.

Or... use a relatively new Oracle option, the: EXTERNAL TABLE


Here is a simplified example:
First...

CREATE OR REPLACE DIRECTORY rms_dir AS '...';
CREATE TABLE rms_table (username VARCHAR (20)...)
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY rms_dir
ACCESS PARAMETERS ( records delimited by newline)
LOCATION ('rms.dat')
);

create imported_data as select * from rms_table;

Done.

You may want some SQL to commit in chunks.

But really... just use the Oracle provided SQLloader.

Cheers,
Hein.

[Hein van den Heuvel]

Ian, any feedback on this? Did you come up with a solution that you can outline/share for the benefit of others?

Regards,
Hein.

[Ian Derringer]

Hein,
You're correct about using SQL*Loader and they're and will be using that. This is all I know about this time. I did asked for the coversion script so that I can see how it's being done and I may share with you & the group for feedback, etc.

Thanks for following up on this matter.

Regards,
Ian

[Bennett_2]

The physical layout of data between the RMS file and the database relations may require some data normalization. For example - arrays in RMS file would be unpacked and coded into 1 item in its own relations... Normalization ...Codds rules etc...

Once this normalization process has occured one tool I've used in the past from the OpenVMS side is DTR Datatrieve. It can be used to prep the data... deliniate fields, unpack fields or change datatypes around... Basically get the data ready for the import process... RDB IMPORT or SQLloader the step that populates the database.

The key with this type of conversion is to nail down how the data will be funneled over to the SQL / relational environment.