11 replies to this topic

[bergsey]

Hi,

We are currently running Pro-IV v4.0 r518 on OpenVMS v7.1

We have a large number of files on an ISAM database over 384MB

In looking to move to the Unix platform, Pro-IV have indicated that there is an issue with files over this size, therefore we will have to convert to Oracle or C-ISAM.

Is this 384MB limitation unique to Unix platforms, or should have this never worked for us on VMS either ? (a couple of files are >1GB)

Thanks

[Tony Waszkiewicz]

Hi,

The PRO-ISAM 384MB limitation also applies to the PC platform as well as Unix.

On VMS the RMS file system is used and this is a part of the O/S and so does not have this limitation. In addition it also offers other functionality not found in PRO-ISAM and which you may be using such as alternate keys. You will know that you can use standard VMS utilities such as ANALYZE to pack the data, but with PRO-ISAM you can only use the supplied PRO-IV utilities.

Within the RMS records the data is held either in standard RMS format (fixed?) or as PRO-ISAM (variable).

It will be much easier for you to convert to C-ISAM than Oracle but note there is a 2GB limit on some platforms.

HTH

[Rob Donovan]

Hi,

I think that VMS uses a different version of ProISAM (VISAM maybe...not sure about that) and it seems not to have the size limitation.

In Unix, 384MB is the limit if the record size set to 3000. The record size has to be set manually for each file.

If the files gets above this limit will become corrupt and cause core dumps and loss of data.

For a quick solution I would go to C-Isam, as there is very little difference between ProISAM and Cisam.

Moving to Oracle would involve a great deal of changes and mods, to get transactions and rollbacks sorted out. Also, you need to include lots of embedded SQL to make the system perform well.

If its performance you need then C-Isam is best since it is very quick writting and getting data back. Provided the correct indexes are set up.

If transactions and data recovery is more important, I would go for Oracle, but it will involve alot more work to convert and also use afterwards (and maintenance).

Rob D.

[bergsey]

Thanks for the info.

The end game is to get to an Oracle back end on Unix. This then brings us into line with our other corporate applications (which all have Oracle back ends)

Is there an advantage in converting to C-ISAM, doing the Unix move and then converting to Oracle ? or is it just a waste of time having an intermidiate step? We are trying to reduce the risk so hopefully we can break the migration into chunks rather than big bang

Cheers

[Rob Donovan]

Oops,

Sorry Tony... posted at the same time

It think the 2GB limit is the limit on 32bit systems... on 64bit systems there is no 2GB limit, but... this is unsupported I think, because ProIV LTD and/or IMFORMIX/IBM have not tested this. They wanted a large amount of money to test that

Where I used to work, we (me and a Compaq Technician) looked into the 2GB limit and concluded that there was no such limit on Compaq Tru64 (64 Bit).

We used CISAM files that were over 2GB (upto 10GB the last time I checked) and there were no problems.

But, you should defrag and compress the indexes of these files that get this big to maintain good performance.

Rob D.

[Cleve Haynes]

Rob, the biggest CISAM file is 12.6 Gb now....

[Rob Donovan]

Sometime... we need to workout the maximum.. before you guys reach it

Depending on whether internally it holds the pointer into the .dat file as a integer or long..

As far as I can remember, in the .idx file there was plenty of room for a 'huge' number, but how it is held within the CISAM layer internally (and if ProIV manipulates it) might be a problem.

Rob D.

[Richard Bassett]

Just for clarity - there is no Pro-ISAM code whatsoever on VMS – any more than there was on mainframes, OS/400, GCOS/6, or any other operating system that already had a standard, adequate ISAM filing system. When you use Pro-IV "internal" files, the record format is the standard Pro-IV variable-length, internal format - this format has nothing inherently to do with Pro-ISAM and could be used (given support in Pro-IV) with any "ISAM-capable" storage system supporting variable-length records.

I believe the 2Gb limit on C-ISAM was lifted by patches or variants available from Informix a long time ago now and I was recently told that, under IBM's ownership, there are or will be "true" 64-bit versions of C-ISAM available that do not have this limit anyway. However..

There is a huge but little-mentioned architectural difference between C-ISAM and Pro-ISAM and other indexed fileystems, such as RMS, that were supplied with most traditional OSes. C-ISAM and Pro-ISAM run in "user space" of the process that is using them. This means that the buffers and structures of the ISAM system are completely unprotected from errors in the coding of user programs. By contrast, RMS runs in a more-privileged "ring" in the VMS operating system and it is impossible for errors in user programs to damage the structures and data-blocks of the ISAM files. (This ring-based protection is a more extensive form of the protection the Unix kernel has and which requires the overhead of making a "context switch into the kernel" when any system call is made).

A lot of people seem not to like hearing this and prefer to ignore or forget it.. What it means bluntly is that the integrity of (say) RMS files is protected from user programs by the fundamental capabilities of the OS but that corruption of C-ISAM or Pro-ISAM files is only a matter of time unless all executable © code in an app is developed to the same standard as the operating system.

Note that the variable-length form of C-ISAM (CISAMV in Pro-IV) is also less safe than fixed-length because the variable-length record "tails" are stored in the "complex" index part of the file and not in the "simple" data part. This means that a variable-length C-ISAM file cannot be rebuilt from the data part alone after a hardware or software problem has caused corruption or data loss.

There are several reasons that the popularity of relational databases soared on Unix systems in particular - but this user-program protection issue is one of them. Note that one of the reasons C-ISAM/Pro-ISAM are so fast is precisely because of this insecurity. Also note, as Cleve says, that these ISAM systems can need very frequent re-organizations because they were not originally designed to support very large scale systems.

If you are running an enterprise-class system on Unix, which you almost certainly are if you have multi-gigabyte files, then, personally, I believe you should not be using C-ISAM/Pro-ISAM for the user's data. Also, these ISAM systems are not appropriate for any application that might need to run 24x7 in the future.

Moving from C-ISAM to Oracle is no easier than moving from RMS to Oracle. I can see no reason to expend extra cost and effort to move to C-ISAM as an intermediate step. You will have to solve the transaction-processing issues anyway so you might as well bite the bullet and go straight to Oracle if that is the ultimate target.

Incidentally, I do not think it is always necessary to use much embedded SQL with ProIV to get an Oracle system to perform adequately. I know of a massive application that is currently using the same source base to run on both VMS/RMS and Unix/Oracle. BUT.. you should figure that the Oracle version of a system on Unix probably needs double the CPU horsepower and double the real memory to perform at a similar level to the same application in a C-ISAM/Pro-ISAM form.

HTH, Richard

[Rob Donovan]

Wow... thanks for that Richard, some interesting stuff there

The system I was on before was on C-ISAM (and a bit of ProISAM). The only corruption we got was when we got system crashes and the machines rebooted. Mostly it was the index part of the file, that was easily recreated.

We had many CISAM files, with near 1TB of disk space used!

We had all the indexes setup correctly for direct access to the records we needed. It was/is one of the fastest systems I've seen around (with comparable data sizes) within the industry. Most of the other systems I've seen were Oracle based, but needed 2,4 or 6 times the power just to run normally. And they were much slower than ours.

We did a project for 6 months (with Oracle consultants) to see if going to Oracle would be for us. After 6 months the answer was certainly no.

The biggest thing in our system was performance, with over 500 online users this was defiantly a priority.

Going to Oracle gave us an instant degrade in performance, and we were running on some of the Top Compaq servers around.

Oracle seems fast, if you want to select 10 records out of a 1,000,000 row table and its not part of the index. Which beats Cisam hands down.

But if all your indexes are setup correctly and direct hits are possible, selecting 10 records from a file is much quicker in CISAM than Oracle.

We were a 24/7/365 system and the system was only ever down for about 2 minutes a night to do backups.

Rob D.

[Richard Bassett]

> But if all your indexes are setup correctly and direct hits are possible, selecting
> 10 records from a file is much quicker in CISAM than Oracle.

In my experience and in tests I ran several years ago this is definitely not the case. However, you do need to be sure you are comparing like with like when trying to compare Oracle's performance directly with ISAM.

My findings were that if:
1/ The Oracle database was appropriately tuned and..
2/ The client program (ie. ProIV) was on the same box and..
3/ The connection was direct (ie. BEQ) and not TCP loopback or something and..
4/ The Oracle table had only one key and one data column and..
5/ I allowed for the SQL and data shared caches to be properly warmed

..then the performance of retrieval (select) and insert operations was getting very close to Pro-ISAM.

Note that point 4/ IS the correct scenario to compare equivalent functionality as fairly as possible, even though it is of course not the typical situation when using Oracle with ProIV.

The performance of updates and deletes was not as fast as with Pro-ISAM/C-ISAM however, partly I think because Oracle does more sophisticated management of free space.


Regs, Richard


BTW, I can't quite see how you can be a 24x7x365 system on C-ISAM and/or Pro-ISAM unless you never do a reorg..

[Rob Donovan]

BTW, I can't quite see how you can be a 24x7x365 system on C-ISAM and/or Pro-ISAM unless you never do a reorg..

Fair enough... We did have the system down for 2 nights a year to do defrags

Rob D.

[cplau]

Hi all,

I encountered stranged situation here.

We are using AIX 4.2.1, one of our C-ISAM files corrupted when it reached 999MB.