sql database v. COBOL indexed files 4

[philhickling]

What are the relative advantages and disadvantages of an sql database system as compared with COBOL indexed files?

I have a client using a networked system for 100+ users, developed using MicroFocus NetExpress, accessing a database of 30 indexed files, 3 Gb in total size, 1Gb largest file (plus 5 Gb archived data), about 10 million live records. About 80% of users connect via the local NT4 LAN, the remainder via a WAN using Citrix WinFrame. It all works OK,
but we face a possible 10 times expansion, and are concerned that performance may disintegrate.

Would an sql database system such as Oracle, Sybase, etc. be better than indexed files? Are there other options for improving performance? Can COBOL systems and indexed files cope with 1000+ users, and 30Gb databases?

 

[k5tm]

Would an sql database system such as Oracle, Sybase, etc. be better than indexed files?

Certainly more expensive! But probably not "better" -- only you can tell.

Are there other options for improving performance?

What is your performance metric?

Can COBOL systems and indexed files cope with 1000+ users, and 30Gb databases?

They have for years -- decades! -- on mainframes. With 100+ users your existing load, you would probably know most of the performance bottlenecks. If the 900+ users are added to the system at once, I would expect to see some performance issues crop up. It is important to realize, however, that Oracle (DB2, SQL Server, etc) based applications also have similar problems scaling, since scalability is primarily an application architecture issue.

While I can't speak directly to NetExpress, I can assure you that I have seen RM/COBOL based applications running hundreds of terminal devices (including cash registers, etc) over a WAN and giving quite good response times. Such a system takes some fine tuning, but, compared to the expense of a total changeover in application, the cost of the fine tuning should not be too onerous (given you are already at 100+). Tom Morrison

 

[philhickling]

Thanks, Tom,

>What is your performance metric? < I don't have one, other than 'Does it work sufficiently fast?', which it does. To be frank, I don't know enough about how to measure performance, or estimate the effects of an increase in traffic - other than by testing and trying to simulate different situations.

Phil.

 

[Dimandja]

Hi Phil,

&quot;COBOL&quot; indexed files as well as SQL type databases can and do handle very large files/tables - the limitations are usually imposed by your hardware and your operating system - oh, and your budget. Network traffic is also largely constrained by your hardware.

As you know, disk I/O is usually the slowest portion of most applications. When disk I/O increases, so will your system response. This problem has usually been addressed by upgrading the hardware, or refining how the applications access disks - more efficient data cashing, improved paths through the tables, better indexing, etc...

My experience has been that no matter which database you adopt, you will run through the same problems. However, relational databases will probably give you the most headaches - because they are also the most complex to implement.

There is a bigger learning curve to effectively make use of relational databases in applications. While indexed files behavior is largely predictable, programmers need to study/understand the effects of their statements on a relational database.

Relational database engines use a set of proprietary routines to access the database; these routines are usually beyond the reach of the application: the application programmer must make experienced use of &quot;cursors&quot; and &quot;select&quot; statements, for example. The impact of those statements is not always obvious to the application programmer.

Dimandja

 

[stoggers]

Hi,

I would also add that most DBMS's have functionality to support clustering, resilience & recovery, and n-tier architectures. If you need this type of functionality then it's better to opt for relational DBMS's as it's provided as part of the core functionality rather than building it yourself.

One feature of using indexed files that is directly related to performance is that, usually, unless you are using a TP monitor, most of the processing takes place on the workstation and if you hit performance problems you need to look at the hardware used by each workstation. Having a server based approach means that performance is often a matter of the power of a single box - the server.

Interestingly, a lot of the really high volume global applications - ATM's & airline booking services stayed well clear of RDBMS's for a long time because of the overheads of processing the required number of transactions/second.

Regards,

Stoggers.

 

[RICHINMINN]

What type of hardware is this system running on? One option that may be worth considering is the use of RAID drives, which can significantly increase data throughput, along with the capability of adding fault tolerance. There are a number of ways to implement RAID, and the correct scheme would have to be tailored to your specifics needs for throughput speed and data security. A RAID drive subsystem is obviously more expensive than one or two drives in a system, but, considering the growth that you're anticipating, it may be worth the cost.

Rich (in Minn.)

 

[philhickling]

Thanks everyone, especially Dimandja. It is all helpful and interesting - please keep the responses coming in.

Rich - to answer your questions: we already use raided disks, Raid 5 I think. The server and workstations are all NT4, and I am being to think, from comments in comp.lang.cobol that one great performance enhancement could be to switch to Unix.

 

[hansie]

Hi Phil,

In my opinion, a move from NT to UNIX would be VERY WISE.
We are running RM/COBOL on a UNIX SVR4 (NCR) system, currently, with 110 - 130 users.

Some specs :
Hardware : SCSI Raid (AMI) striped and mirrored (10 disks total), 2GB Ram, Pentium XEON 4*700MHz
Software : 170 indexed files per company (total 6 companies). 2GB data, largest 650MB
A lot (and I mean a lot) of hits are comming from the internet (ie. order-entry, management info etc.).

System-idle : never under 95%, performance is incredible.

In the coming year we are making the move too 200-250 users, in a WAN-environment, I feel confident with Cobol Indexed files in a UNIX environment....

Hans.

 

[gregsimpson]

I have used both Indexed Files and RDBMS systems. What everyone above seems to have failed to mention in favour of the SQL approach is

Programs can be much simpler as the SQL can be written to only select those records your interested in.

SQL is fairly intutive and users can write adhoc queries themselves. All RDBMS provide an interface for this. Same interface can usually easily be used for creating test data.

Tables(FIles in the old world) can be joined together to save having to write file match type programs.

In Theory ANSI standard SQL means you can change database vendor without to many problems.

Using an RDBMS the amount of data crunching your application has to do is much less. Therefore simplifying your programs.

Better security features built into RDBMS.


All in all, its my choice every time. It is fair to say however, that the whole business/system needs to be looked at when selecting whichever. Horse for courses as they say.

Greg

A basic page below

http://www.jegsworks.com/Lessons/lesson1-2/lesson2-4database.htm

 

[k5tm]

Greg,

Your post lists advantages which are fair enough.

However, it is not reasonable to ignore the value of the software which would be discarded by the considerable rewrite implicit in your simplifications.

One must balance whether that value may be recovered by a switch of persistent storage mechanisms. Given the facts presented by Phil in his initial and subsequent postings (he also posted in comp.lang.cobol), I would tend to think that his application will scale with only minor performance issues. Absent any other requirements, Phil would be taking on far too much risk to change from indexed files to a proprietary database. In fact, he could use a product which supplies ODBC SQL access to his indexed files to obtain some or many of the benefits you list. Tom Morrison