Re: Oracle*Case and code generation
Date: Thu, 18 Mar 93 06:43:38 MET
Message-ID: <7324334188-315915_at_billroth.wu-wien.ac.at>
In article <16B92A642.M22367_at_mwvm.mitre.org> from [Mon, 15 Mar 1993 16:49:21 GMT] you wrote:
|> As promised, I am posting a summary of responses I have received:
|>
|> Summary: "Make sure you post whatever you find out - I'm interested, too!"
|>
|> :-)
|>
|> I have not received responses from anybody who has generated code from Oracle
|> CASE. At this point, I'd like to hear from anybody who is using Oracle*CASE
|> and *considering* generating code from it.
|>
|> Perhaps no-one has done it?? Maybe they have and just don't read this
|> newsgroup ;-)
|>
|> - Mike Waters
Hello Mike!
The following is a summary of the experiences with a programming class based on ORACLE *Case here at the Vienna University of Economics and Business Administration which was held the first time in winter 1992/93. Although it was not a real life commercial project you might find some of our findings useful for your own decisions.
The first part of this summary is from Rony Flatscher and gives an overview of our motivation and our goals. At the end I try to share some of our experiences concerning the actual hardware and software configuration and the generator modules.
Andreas
CAUTION: THIS POSTING IS VERY LONG (but you have asked for it twice).
A Short Report on Replacing COBOL with Oracle*CASE at the Vienna University of Economics and Business Administration
The Task
At the Vienna University of Economics and Business Administration the Department for Management Information Systems decided a year ago to substitute the education of the programming language COBOL by Oracle*CASE. Although COBOL still is a driving language in business related application development and as such often chosen as a target language for CASE-tools we decided to replace it with a CASE-system altogether.
The reasons which led to this rather dramatic step for an MIS-department were basically twofold:
Firstly, COBOL is a third generation language and was not perceived as being suitable to successfully target on producing information systems fast enough in order to tackle the problem of backlogged development projects for information systems.
CASE-tools have been spreading in order to allow for a faster production cycle of information systems. Many CASE-tools employ modelling techniques e.g. entity relationship and dataflow diagramming, some even communication and event modelling which are almost unknown in MIS-departments of larger companies. Because of this it was our feeling that an effective deployment of CASE-tools which add up to their promises was not possible as long as the applicability of the basic techniques are not really known and hence the need to teach students all those techniques additionally with personal online-experiences. In two to three years the first of these students would enter the labor market and hopefully should be able to employ their acquired working knowledge on real-life problems to their own and their employer's benefits.
Choosing a CASE-System for Teaching
Throughout 1992 a group of assistant professors and lecturers at our department (Brigitte Eichler, Rony G. Flatscher, Christian Marent, Andreas Pinzenoehler) started out to evaluate different CASE-tools and CASE-systems which attempt to cover the entire production cycle. Due to the fact that two of these people had working experience with Oracle and the accompanying 4th generation Oracle tools (SQL*Menu, SQL*Forms, SQL*Reportwriter) we concentrated on evaluating the Oracle*CASE system. This experience stemmed from developing an office-automation system for the administration of the Vienna University for Economics and Business Administration.
While evaluating the features of Oracle*CASE it became clear that almost 90 % of all produced WUFIS-applications could have been produced with this system automatically, if all the descriptional information were entered thru it thereby shortening the development time drastically.
Still, we attempted to get information on other CASE-tools and CASE-systems and took the oppurtinity to attend a three-day meeting of the Austrian Working Group for Information Systems (ADV) at the end of March 1992 which featured presentations of all important companies in the area of CASE among which the most important were: Andersen Consulting, Computer Associates, DEC, Ernst & Young, IBM, James Martin Associates, Ploenzke, Siemens Nixdorf Informationssysteme, Softlab and some local vendors. We took the opportunity to discuss the principles and obstacles of the respective tools and systems with both company representatives and customers utilizing those tools. This approach was taken because it became clear, that to personally acquire a deep knowledge on all those different available tools and systems was impossible to our group in the given timeframe in which we had to decide which CASE-system we wished to use to teach our students. From our discussions with all of these people and from our own experiences with Oracle, we decided to go with Oracle.
With Oracle*CASE it seemed to be possible to allow for teaching at least the usage of ER- and function-modelling and derive working applications from them automatically.
We think, that due to the abilities of the Oracle*CASE generators approximately 90 % of the standard business applications can be produced without altering the resulting code. The reason for this, we think, lies in the fact that Oracle-generators target their own 4th generation-languages, mainly SQL*Forms, which contain very powerful functions which relief the generators of producing repetive atomistic code e.g. for evaluating values entered by users into forms or generating lookup-applications.
On the downside it must be clear that this also means that the users are totally dependent on Oracle-products, if they wish to take advantage of the features of Oracle*CASE. This problem has to be taken seriously into account at a University, which should not try to lead its students into the arms of a sole vendor. At present, on the other hand there seems to be no CASE-tool or CASE-system which would not lead the users into some kind of dependency.
The only solution to overcome this problematic situation lies in the formulating of a curriculum which teaches basic concepts found in most CASE-tools, especially ER- and function-modelling, having students apply that knowledge on little case-studies for analysis and modelling of information systems. This part should be formulated and presented such that all acquired knowledge is easily transportable to other CASE-environments. In order for the students to experience the consequences of utilizing a CASE-system working applications have to be derived with Oracle tools. It has to be made explicit that from that point on they are moving in a proprietary environment.
The Curriculum for the Introductionary Class of CASE
In order to teach the students the broadest applicable knowledge possible in respect to CASE-environments the emphasis was laid on modelling techniques and their application to formulating solutions to business oriented problems. We attempted to have 2/3 of our introductionary class concentrated on transportable working knowledge which was comprised of the following themes:
General about CASE: Base techniques, route for designing and developping applications, Upper CASE vs. Lower CASE, generating vs. programming, reengeneering
Modelling of Data: Entity Relationship Modelling (ERM) according to Navathe and ElMasri, mapping to relational database structures of entities and relationships, introducing Oracle's version of Entity Relationship Modelling, (semantic) differences in comparison to Navathe and ElMasri, restrictions of Oracle's ERM and its implications, applying the modelling technique to a small case relating to the classique order entry problem by using Oracle's ER-diagrammer tool.
Modelling of Functions: Function Hierarchies, identifying reusable functions, data-flow-diagramming, applying the modelling techniques to a small case relating to the classique order entry problem by using Oracle's tools.
Mapping ERM-models to relational database structures by utilizing the definitions already entered into Oracle's CASE-dictionary and by supplementing needed information.
Mapping of functions into modules by utilizing the definitions already entered into Oracle's CASE-dictionary and supplementing additional needed information.
Generating tables and indices in an Oracle database.
Generating SQL*Forms applications to work on the defined tables and indices, discussing the benefits of defined relationships with foreign- and primary-keys which are being taken advantage for by Oracle*CASE generators e.g. in respect to automatically generating checks and lookup-tables via these established definitions.
Generating Reports with SQL*Reportwriter by utilizing the information stored in the Oracle*CASE-dictionary.
The First Semester of Teaching an Intorductionary Class on CASE (Starting Fall Semester 1992)
The class was held weekly as an optional class lasting for four hours each week starting in October 1992 and ending in the first week of February 1993. This accounted for 14 weeks of education in the winter semester.
We used one Workstation-Laboratory with 26 DEC Ultrix workstations. Initially 42 students attended the class but almost one fourth of them dropped out during the semester. At the end there were 32 students still attending the class.
In the summer semester which will start in March there will be two mandatory classes in which students have to work in groups on small cases and who have to utilize their acquired knowledge with Oracle*CASE in order to generate applications for these cases.
Experiences
The preparation for this introductionary class was extremely labor intensive. Unlike a classique programming language like COBOL where the basic syntax can be learned sufficiently well within four weeks a beginner with Oracle*CASE has a lot more to do. This is due to the amount of features embedded into it. Still, at the end of the fall semester there were still issues to be learned by ourselves! In addition the entire system is setup such that a beginner hardly can get an overview of the functions and menu items. This is because on the one hand Oracle*CASE offers a graphical front-end in respect to entity relationship diagramming, functional hierarchy diagramming, dataflow diagramming and matrix diagramming, on the other hand all these functions and many more are available in the form of character oriented SQL*Forms applications too for direct "data entry" into the dictionary.
Unfortunately for the beginners there are many redundancies in the menus. This helps the power user but irritates the beginners.
To truly make full usage of Oracle*CASE one needs to learn SQL*Forms, SQL*Menu and SQL*Reportwriter in addition in order to be able to adjust those applications which cannot be automatically generated with this system. Still, Oracle*CASE allows for students who have no working knowledge of any of Oracle's products to start and develop real-world applications at the end of the semester.
Rony Flatscher
Experiences with the hardware and software configuration
As mentioned above we used a workstation laboratory which 26 DECstations running under ULTRIX V4.2. Three of these machines are configured as servers to hold most of the binaries and all of the user data. The 23 students machines are configured as so called "data less" workstations, i.e. local swap space, local configuration files and some of the most important binaries are kept locally, all the rest (also all ORACLE binaries) is loaded on demand over the network. Network software consists of ethernet with all the usual Internet protocolls (TCP/IP, telnet, ftp, NFS ...). As GUI we use X11 R5 in the MIT distribution.
ORACLE Software:
RDBMS V6.0.34.2.1, transaction processing option
SQL*Plus: Version 3.0.11.1.1
PL/SQL V1.0.34.0.1
SQL*Forms Design: Version 3.0.16.9.1
SQL*Menu Version 05.00.11.08.01
CASE*Dictionary: Version 5.0.22.1.1
CASE Designer 1.1.21.0.1
CASE*Generator for SQL*Forms: Version 2.0.7.1.1 CASE*Generator for SQL*ReportWriter: Version 1.0.8.1.1 CASE*Generator for SQL*Plus: Version 1.0.8.1.1
(I think these are the most important pieces)
In retrospect I can say that the whole setup worked out surprisingly well, especially if you take into account that it was a first for us in many aspects.
There have been problems. Some of these problems can be definitly related to ORACLE and ORACLE Software, some seem to have been caused by some exotic combination of DEC hardware and OS, ORACLE software and public domain software and many of them can be contributed to our own learning curve. Since this is an ORACLE newsgroup I am going to concentrate on ORACLE specific experiences (i.e. problems) and I feel obliged to make the following statement before telling about the interesting things:
We are very satisfied with the quality of the used ORACLE software and we experienced very valuable support of the local Austrian ORACLE technicians (special thanks to Heinrich Balatka and Edgar Raab)
ORACLE bugs (features):
- huge amount of required disk space:
This almost killed the project before being born. A full installation of all tools (binaries, read me files, installation files, configuration files examples etc.) needed something between 400 and 600 MB of disk space (I forgot the exact number but I remember that skipping ORACLE*Mail saved about 60 MB). If you are considering a full ORACLE developing environment on RISC platforms care for big disks.
2) interactive installation scripts - no possibility (at least no easy one)
to start again somewhere in the middle.
As being under discussion recently in this group the installation
scripts very definitely possess potential for further development.
In the ULTRIX distribution there have been no outright errors in the
scripts, but our system administrator needed at least three times
before making all the right decisions up to the end.
Our system administrator is a true Unix Wizard and I really think it
was not his fault.
If any responsible ORACLE employee is reading this part I have following
suggestions:
a) REVISE the whole installation procedure under assistance of
experienced psychologists and TEST it in a lab with some normal
system administrators (persons who at this moment very often
have no ORACLE experience and not enough product-insight to make
the necessary educated guesses but have the reputation and
self-confidence to complain in public and in front of the
management). If you don't have the budget for doing this, take it
from the Public Relation people. Such an improvment yields probably
more positive impact on the image of your company than 20 fullpage
adds in Computerworld.
b) When implementing suggestion (a) make one and only one setup part
at the beginning where ALL the necessary parameters are entered.
Give the system administrator a fair chance to let the installation
run unattendend to a successfull end.
3) Marketing claims to be dedicated to open systems but the diagrammer needs
DECwindows
ORACLE PR claimes to be portable between many many platforms. This is true.
But the CASE*Designer modules for ER-diagramming, function hierarchy
diagramming and data flow diagramming die seconds after being
called whenever one uses a straightforward MIT installation (X11 R5).
Our Unix wizards found out that there are some hardcoded DECwindow
calls in the diagramming software. I don't think one can really blame
ORACLE for this bug/feature in our current version (because the
available diagrammer software is rather old and the new versions
with the new toolkit are comming up in the near future) but what
really upset me was the statement of an local ORACLE employee
which in short was this:
"Good costumers use their Unix workstations with the Windowing
systems from their hardware vendor. Using a DECstation with the
original MIT-X11 software makes a costumer to a bad costumer
and bad costumers can't expect to have their software working
without problems." We feel here rather strong about this
issue and we think that it is a really bad idea for an
"open software company" to hardcode DECwindows calls in a
X-Windows based frontend.
4) Strang behaviour of CASE-Dictionary sessions.
a) If one starts a cdict session the processtree looks like this:
andreas 13558 p6 0:00 /bin/sh /usr/local/bin/cdict
andreas 13565 p6 74:29 casedict -x cdrundict
andreas 13569 p6 0:00 /bin/sh -c cdrundict CDCMAN casetest/password
andreas 13570 p6 0:00 /bin/sh -c cdrundict CDCMAN casetest/password
andreas 13571 p6 0:14 runmenu50 CDCDICT
When you look very close at this example (pasted results of a
"pt"-call) you will notice that one of the processes uses a lot
of CPU-Power (casedict -x cdrundict with 74 CPU-minutes after
being approximately 90 real minutes in the system).
One possible explanation is that one of the ORACLE-Toolprogrammers
has an CP/M or MS-DOS past and is accustomed to the "busy-wait"
programming technique (this is not our favourite assumption).
Another explanation could be that there are some special
circumstances at our present combination of hardware, OS, GUI
and ORACLE software.
Since we think the second reason holds and each of our students
has a strong workstation for himself we didn't do much against it
except waiting for future upgrades. (In fact we already have some
patches - Version 6.0.36 of the RDMBS - but to install these patches
we have to upgrade to Version 4.3 of ULTRIX which we
will do sometime in the next future on one test-machine).
If you look very close at line 3 and line 4 of the example you
might notice another interesting feature which is dealt with
in section (5) below.
b) Zombies after closing cdict-session with mouse events
When a cdict user closes a cdict session with the mouse there
very often remains a zombie process (number of the parent
process: 1) which very often is in "a busy wait" state too.
Our workaround: we conditioned our students
to always use a function key to exit a cdict-session.
These two problems have probably then same cause as the
one in section (3) and may be specific to our configuration.
As said before we just wait for future releases.
The next one is rather bad.
5) DATABASE-PASSWORDS CAN BE SEEN BY EVERYBODY when using
the "ps" command
In the above example the third and fourth line include both this string:
"/bin/sh -c cdrundict CDCMAN casetest/password"
"casetest" is the name of the ORACLE user running the cdict-session
"password" contains in the original xterm output the REAL password
I am using to connect to the database (I substituted the string
"password" in the editor because I think it is no good idea to publish
a password via internet, even if it is a very harmless test account).
Dear folks from ORACLE, read and think carefully!
It is no problem in the environment of our workstation lab.
Our students are educated to cooperate and to work together
in teams. It is also no problem within our department. I know a
lot of software companies and IS-groups at private companies
where this will pose no problem at all. BUT there are also shops
where such a behaviour will put ORACLE out of business.
So imho ORACLE really should address this bug
on ALL platforms.
6) Inconsistent and irritating menu structure in case dicitionary
As already mentioned in the part of Rony the layout of
dictionary menues is made for experienced power-users. We
have had rather big problems teaching our students to navigate
efficently in the cdict menues. A simplified menu-strucuture for
beginners or a hypertextlike userinterface could be very
helpful. We are looking forward to toolkit 2 and Forms 4.
7) Care for a strong database server
If you really want to use the CASE*generator modules take
care for a really very strong database server to hold the
dictionary.
Reason: When discussing the issue of generating within
ORACLE*Case even ORACLE employees thought that
generating needs strong clients in the first place. THAT IS
NOT TRUE!
Almost all generation sequences follow the following pattern:
1) Look in the active database account whether there are any
temporary tables.
2) If there are temporary tables drop them.
3) Create the temporary tables which the current
generator needs
4) Select the relevant dictionary data (for the module, table design,
DDL-generator etc.) and insert it into the temporary tables.
5) Process the data of the temporary tables (I think this also done in
the database, but I may be wrong with this) and build the module.
If your module happens to be for SQL*Forms or SQL*Reportwriter
the database activity continues (shown for SQL*Forms):
6) Look in the SQL*Forms repository whether the module exists.
7) If the module exists drop it from the SQL*Forms repository.
8) Insert the new definition of the modul in the SQL*Forms
repository (for SQL*Forms developers: that compares to the
"save" command of the SQL*Forms Designer)
9) Read the SQL*Forms Repository and build an
inp-file for the module.
10) Compile the inp-file and make an frm-file (again for
SQL*Forms developers: step 9 and 10 compare to the
"generate" command of the SQL*Forms Designer)
Most of the above happens in the database. The client has
only to deal with a small proportion of the processing load
(that might be the reason for the busy wait - sorry only
kidding).
To make this even worse the CASE*Dictionary and as it seems also
the Generator modules make extensive usage of views. I have the
impression that using views is not a really good idea
concerning performance. But I have not done any benchmarks
so far, so this statement has to be taken with care (btw is
anybody out there who has done benchmarks concerning the
performance of using base tables versus views in ORACLE?).
A rather drastic example for slight performance problems when
generating consists of following setup:
Generating a module takes about 40 seconds of real time
done by one user (it was me preparing the example for the lecture,
one tries to minimize surprises in front of the audience).
Knowing that most of the processing happens in the
database and also taking into account that there is only one
database I calculated 10 to 12 minutes for 12 groups doing
this generation step concurrently (for ORACLE*Case insiders:
there have been 12 seperate Case dictionary table owner
accounts, one for each group).
At the end it turned out that whole process in the class
took not 12 minutes but 50 minutes. Our continously adapted
forecasts of the duration were the cause of great hilarity
and entertainment for our students, so at least they had
a good time.
The workaround for subsequent generation runs was that one
of the lecturers played operating system and scheduled the
generation jobs of the individual groups. This also caused much
hilarity, but we came somehow near the expected 12 minutes.
It was a great experience!
Since I have the strong suspicion of our configuration to
be the cause of these effects I ask you to take the last two
paragraphs again with care. Any ideas or hints concering
probable or possible reasons of this behaviour are very
welcome (By the way: memory was increased two weeks
ago and the last tests show that it is definitly not
the paging of the operating system - which was
my favourite scapegoat during the semester).
8) Known deficiencies of Case*Generators
We also have generated some SQL*Forms, SQL*Reportwriter and
SQL*Plus modules with CASE*Generator and it alse worked out
quite good. Quite a big proportion of the audience made remarks
like "Ah yes, that makes sense. It was a good idea to attend
this lecture when we can make programs so fast at the end".
Nevertheless we found some shortcomings.
If you have e.g. a master-detail screen it is very often necessary to
show some summary information of the details at the master block
(e.g. the value of an order is calculated as the sum of the values
of all order items). When want to specify a sum of the detail rows
at the master block it is just wonderful. You define a SUM
summary function at the Module Data Usage in CASE*Dictionary
and the Generator for SQL*Forms makes wonderful code out
of it (whoever at ORACLE has done this, I appreciate it very much,
it is beautiful). But when you want the number of the details
instead of the sum you have a problem. The current version
of CASE*Generator for SQL*Forms does not support
COUNT as a summary funktion (whoever at ORACLE has
not done this piece of coding: Why for heavens sake did
you miss this???? IMHO it is exactly the same pattern
of required triggers and functions with a few modifications!).
Another fine feature is the possibility to define derivation
expression for columns in the Module Data Usage. At least
it is fine as long as the input for the derivation comes
from another column of the same base table. If you
want to get data from another table (e.g the purchase
price from the product table at the time of the
order processing) and use in the current block you are
in trouble again. That means you have to add the
missing trigger (in SQL*Forms of course) manually.
So far I have reported mostly factual information. Wherever there were assumptions from our side I tried to make this clear.
In the last part I want to make a few statements which include a lot of personal opinions of my side. So before making these statement I think I should give you some information about my personal background and my prejudices as far as I am able to perceive them.
I am in dataprocessing since 1980, starting with micros (COMMODORE PETS, IBM 5100), small mainframes (IBM 4331) coming over PCs, PC-Lans, some more mainframes, DEC-VAX to Unix-Boxes (first 1988). Programming experience includes C, PASCAL, several 4GL Tool with emphasis on ORACLE and SQL*Forms since 1989. I was taking part in this office-automation project which Rony mentioned in the beginning. There we made quite a lot of small to medium size ORACLE Applications with about 300 distinct SQL*Forms Apps. In this project we implemented our own CASE environment which consisted of quite a big repositiory and several generator programs (of course to make DDL-Statements which is very simple, generator programms for UNIX/PC server-client applications and PRO*C Programms which were mostly used for reports, but also SQL*Loader Scripts - I still miss such a thing in ORACLE*CASE).
This should make clear that I have a very strong inclination towards CASE*Tools (especially the so called LOWER CASE Part). I also have a, as I think correct, prejudice towards ORACLE as a stable platform for missioncritical applications. (Of course there have been problems in the past, there are problems in the present and there will be problems in the future but in all cases there has been some workable workaround so far).
You should take this into account when you read the following lines.
Is it a good idea to use ORACLE*Case?
Yes. It is stable. It allows real multiuser development by teams and not only by single persons. The supported methods are appropriate for the development of big database applications. It supports the full life cycle of an application.
Richard Barker and his team in Great Britain seem to be very able.
If your organisation has decided to rely upon ORACLE as vendor of database software and application development tools, then ORACLE*CASE is first choice. If you hesitate to fully embrace ORACLE (which I understand perfectly well, our client-server experiments were motivated by such considerations) I think you should shop around a bit more.
You should invest in ORACLE*CASE only if you and your organisation are prepared to use the supported methodology. This is true for every CASE-Tool. If your organisation has invested heavily in methods which are not supported by the CASE-Tool in question buy another CASE-Tool.
Is it a good idea to generate code from ORACLE*Case?
Yes! The current generator modules are really very satisfiying.
BUT: You have to use ORACLE tools for your applications. At the moment there are no generator programms for other target platforms (as far as I know). This raises the question of licensing schemes.
ANOTHER BUT: I think that the portion of 90% of the code which can be generated automatically holds in general for typical database applications with data-entry functions, simple validations against defined domains and common datatypes and conventional reports. You have to be careful when you set up a team which is supposed to use CASE*Generator for application development. Ten percents have to be done manually, and imho this 10% have to be done by staff who REALLY understand what SQL*Forms, SQL*Reportwriter and SQL*Menu do internally and where to make the changes and extensions. If you can't cater for this knowledge I think you should not use CASE*Generator.
Sorry for making this essay rather long, I hope some of you can benefit from it.
Regards
Andreas
-- Andreas Pinzenoehler andreas_at_wu-wien.ac.at University of Economics and Business Administration Augasse 2-6, A-1090 Vienna, Austria Tel: +43 (222) 313 36 x4443 (9-18 CET) Fax 34 75 55Received on Thu Mar 18 1993 - 06:43:38 CET