Jump to content


Photo
- - - - -

Oracle & Pro4


13 replies to this topic

#1 Rob Fantini

Rob Fantini

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 267 posts
  • Gender:Male
  • Location:Haverhill, United States

Posted 02 September 1999 - 02:54 PM

I'm considering using Oracle weith Pro4...

To those who are currently using Oracle:

What are the real advantages?
Disadvantages?
Is it difficult to convert pro4 isam files to Oracle format?

How much did the Oracle addon to pro4 cost?

#2 Guest_bob neville_*

Guest_bob neville_*
  • Guests

Posted 02 September 1999 - 03:36 PM

rob

we are using Oracle 8.0.5, ProIV 4.6 and windows NT 4 at present. We have found that there are numerous problems
with this combination, but the biggest one we have found is
that the transparent SQL generated by PRO IV is at least twice as slow as full function SQL and also the CPU usage
is almost 100% with transparent SQL.
We have migrated 800+ functions from Unix and CISAM using
transparency mode and we are now probably faced with a
re write of all of them to add full function SQL to enable
the system to perform properly.

We converted our CISAM files by writing our own extracts
and then using SQL LOADER to put them into ORACLE. Do NOT
use the PRO IV FACT utility as it is very slow - what we
wrote ourselves took 6 hours to unload and reload 100 tables
(about 1gb), the FACT utility took 5 (yes five) days !

You may like to look at the message I posted today as we are still having problems with the Oracle/PRO IV interface
which PRo IV can't solve.

I can't help you with the cost of the interface as it is
supplied to us by one of our customers.

Regards

Bob

#3 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,640 posts
  • Gender:Male
  • Location:Spain

Posted 02 September 1999 - 03:47 PM

Hi,

We have just done performance testing with 4.6 and oracle 8.0.5 and our results are the same as Bob's. Embedding SQL into PRO-IV seemed the only way to speed things up as PRO-IV's default was very slow.

Also our big batch processes need to be totally re-written to take advanted of Oracle performance issues, as PRO-IV just output too much SQL.

Never heard of FACT but we wrote our own extract functions and used SQLLOADER too. We had 40GB of CISAM & PRO-ISAM to convert (With all our history it's around 400 GB!!), so it would have taken weeks by the sound of it with FACT.

Also Oracle needs alot more 'babysitting' and monitoring once in a live system, compared to CISAM which just looks after it's self.

Rob

#4 Dan Shannon

Dan Shannon

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 374 posts
  • Gender:Male
  • Location:Australia

Posted 02 September 1999 - 03:59 PM

Ditto on the above replies - you NEED to write full-function SQL, and be very careful with sequence numbers if you're generating them out of control files - avoid converting these to Oracle unless you want to paralyse the system!

We're doing Oracle 7.3.4 on PRO-IV 4.0 on VMS and there's LOTS of bugs in the PRO-IV kernel.

One good story - we used a modified version of Glovia's DBC tools to do stage 1 of conversion and it is reasonably fast...

#5 Rob Fantini

Rob Fantini

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 267 posts
  • Gender:Male
  • Location:Haverhill, United States

Posted 02 September 1999 - 04:49 PM

Thanks for the resposes.

Are there any advantages to using an Oracle database?
I assume that an Oracle database would be the way to go to for use in making web applications.

#6 Dan Shannon

Dan Shannon

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 374 posts
  • Gender:Male
  • Location:Australia

Posted 02 September 1999 - 08:09 PM

1. disaster recovery
2. data integrity
3. speed *can* be improved in some cases
4. open database - you can use tools other than PRO-IV
5. PL/SQL is good for things like interfaces etc.
6. Indexes - you don't need to maintain cross-reference files any more because Oracle does it for you

There are many benefits, but you *do* have to be careful to make sure you make the most of them!

Cheers

Dan Shannon
GREYCELL SYSTEMS LIMITED

#7 Gareth Thomas

Gareth Thomas

    Newbie

  • Members
  • Pip
  • 5 posts
  • Gender:Male
  • Location:Ventura, United States

Posted 03 September 1999 - 01:00 AM

Personally I wouldn't go with Oracle its a beast to maintain, administer and install. We are currently waiting for Pro-IV to get their act together and ship the SQL Server 7 interface (we have 2 clients waiting to convert from Pro-ISAM). Although I have misgivings about anything new out of Pro-IV (not sure why I'm restricting myself to new since most of the old stuff has bugs in it) but the reality is SQL Server 7 is a cracking product that has Oracle very worried. We are certified on it for one of the products we sell and I can highly recommend it. If Pro-IV ship even a half decent product this has to be the way to go. I noticed a post further down from someone regarding web integration - again wait for SQL Server, sure you can do it with Oracle but Microsoft provide a superb tool in Visual Interdev which is closely tied to SQL Server 7 for client and server side development.

#8 Malcolm Fell

Malcolm Fell

    Member

  • Members
  • PipPip
  • 30 posts
  • Gender:Male
  • Location:NSW 2087, Australia

Posted 03 September 1999 - 03:22 AM

We had no option but to go to Oracle as we were beaking the ProIV limit on file sizes. Yes its a pain and we had to upgrade our RS6000 590 to a RS6000 F50 add memeory and massive amounts of disk space.
Yes it runs slower than native ProIV but we are looking into using stored procedures as we have been told these are heaps faster.
The only way to load the tables is to write a ProIV unload function and use SQLLOADER to load the Oracle tables. You will need assistance of DBAs to get Oracle to perform properly.
Lots of advantages because now simple reporting requests can be done using SQL. We dont use Oracle for rollbacks etc as we dont have proper commit points across transactional boundaries and we have had to add commits to some functions to get them to work properly.

#9 Ralph Gadsby

Ralph Gadsby

    Expert

  • Members
  • PipPipPipPip
  • 154 posts
  • Gender:Male
  • Location:United Kingdom

Posted 03 September 1999 - 09:50 AM

I've been told by someone that did performance comparisons on PRO-ISAM, C-ISAM and Oracle that in order to get the same performance in Oracle, without changing the code, you need a machine that is at least six times as powerful. How you achieve the increase in power depends on the computer system and current configuration. Spending a lot of time changing your code to run your own, more efficient, SQL can get the performance without upgrading the computer, however you have to way up the programming costs against the hardware costs. Usually hardware wins.

#10 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 19 December 1999 - 10:44 PM

For the last few years (on and off) I've been helping a client port a huge (say 6 million lines of code) Pro-IV application to Oracle.
Obviously quite a lot of effort has been expended ensuring adequate performance, including spending several weeks at a benchmark centre.
This experience suggested it was possible to achieve performance parity with ISAM using approximately twice the CPU horsepower plus twice the RAM.
We used no explicit SQL whatsoever.
Nothing's as simple as you think

#11 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 19 December 1999 - 10:48 PM

So, when can I have SQL Server 7 for OpenVMS, HP-UX, Solaris and Linux? :-)
Nothing's as simple as you think

#12 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 19 December 1999 - 10:55 PM

Yup, SQL*Loader is the only way to go for loading.
I actually implemented an extension to the Pro-IV kernel to speed up dumping the data in the correct format for SQL*Loader.
Running multiple dumps and loads in parallel on a beavy-duty HP machine I've seen 10Gb of data dumped and loaded in a couple of hours.
Nothing's as simple as you think

#13 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 19 December 1999 - 11:05 PM

There are innumerable advantages to using an Oracle database and Dan's points are a good summary.
Some of the most basic advantages are that your customer's files don't get corrupted and can grow beyond 192Mb/384Mb/2Gb and that you can run 24x7.
Nothing's as simple as you think

#14 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 19 December 1999 - 11:12 PM

I should add an important caveat to this - we asked Pro-IV to make a couple of changes to the Oracle interface for performance reasons and these did make a difference. Hopefully they'll be in the standard product soon.
Nothing's as simple as you think



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users