ISAM to Oracle Conversion
Posted 14 January 2004 - 01:05 AM
We are currently underway with an ISAM to Oracle conversion. We've read and heard from various sources that particular files should be left in ISAM to avoid performance and other issues. This hybrid however provides greater challenges in terms of backup and recovery.
Has anyone out there succesfully converted ALL ISAM files to Oracle ? including all of Pro-IV's internal workfiles etc ?
Again, everything I've read so far tells me we will end up with something left over whether we like it or not.
Posted 14 January 2004 - 07:14 AM
We looked at our Oracle (or SQL conversion) as a phased project. The first part of the project is to convert "true" database files into SQL.
If you have any counter type files - used for assigning unique number, they really need to be left in ProISAM and ultimately done away with. Converting them into Oracle will lead to extensive record locking and a system that is basically unusable at even minimal levels of concurrency.
Work files really should be left in ProISAM as well.
Old cross references files should be dropped out eventually as well. But, you really need to define your business needs. If your application size is significant, converting 100% to Oracle or any SQL database is an exercise that takes years.
Granted, in the first several months, you can get 80% of the way - assuming that your database is fairly normalized. However, sequencing and timing issues will take a significant period of time.
Our approach was:
1) Reasonably normalize the database.
2) Explicitly decided which files will be converted and which will not be converted.
(Our choice was to convert all standing data, and transactional tables - including cross reference tables; but not counter files, temp or work files, and source code related files).
3) Give explicit alternate names to each column of each Oracle table.
(Presumably, your end clients will want to datamine and explicit column names will assist them greatly.)
4) Test extensively and learn SQL
5) Tune the application especially keeping record locks open for as short as possible.
6) Remove cross reference files from Oracle and embed SQL
7) Implement "real" SQL features
We are currently on steps 5 and 6. This process has taken about 2 programmer years of work. We have two applications. One consists of about 300 SQL tables and the other 500 tables. They are support by about 2500 and 4000 ProIV functions respectively.
Depending on your business needs / marketplace, you may also want to consider PostgreSQL.
Feel free to contact me for any additional information
Posted 14 January 2004 - 02:26 PM
The counter type files that were pro-isam, we converted them to oracle sequences.
We have also droped all cross reference files and add oracle indexes. Using direct sql with these indexes has given us a a huge performance increase.
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users