In 1995, I started working with Oracle, with the front end being Uniface from Compuware. I worked with Uniface versions 5 and 6 from 1995 until early 1999. The screens were GUI, and worked with a mouse, but were character based. Something like Wordperfect for DOS at the time.
These Uniface versions we ran on Windows 3.1 machines with only 16 megs of ram! So, I learned a lot about tuning, having worked with Uniface!
Uniface was a client server product. It had an interesting concept. Create your schema using entities in Uniface. Uniface would generate scripts for table creation in Oracle. Then deploy it to: dbase on a LAN, Oracle on Unix, Sybase on a VAX, etc.
Then, you would create your screens in Uniface to access and modify the data. The screens were executable files that were stored on the LAN.
The idea was that Uniface would handle everything, in spite of the differences between systems. In theory, Uniface could work with two different products (dbase and Oracle) at the same time. One Face.
Some forms could be created really quickly. In the forms, you created areas on the screen that associated with the Uniface entities, that associated with Oracle tables. Drop in some fields.
The way you the form ran was interesting. After the form appeared on screen, you could retrieve all the data. Or, you could enter a value in a field and then retrieve. The field value acted as a filter.
One thing Uniface did do well was some simple control break reports based on 1:M relationships. Create an entity frame, for the One table and enter some fields. Then, inside that, create another entity frame for the Many table, and add some fields. A retrieve statement in the read trigger of each.
A control break example could be: Salerep (1), and Orders (M). When you ran this, it would show the salesrep’s name (1), followed by all their sales (M). Repeat for the next rep.
The really cool thing is that this form could be created within a few minutes! Once you knew how to use the product of course.
Too bad that I didn’t have a picture. But that would have violated my non disclosure agreement.
Unfortunately, Uniface had some serious weaknesses.
Schemas and Modelling Tools:
One issue was that it had its own schema. So did Oracle. If we were changing the database design, which schema should take priority and be the standard? Which one do we change first?
I wanted a data modelling tool that would interface with Uniface. Some tools did. I seem to remember some that worked were: Silverrun, and Sybase Power Designer. But most tools would only push the design forward into Uniface’s schema file format. Most would not reverse engineer the schema. So, I ended up testing most every DB Design tool available. And if we did get a DB modelling tool, then which schema is the master? We ended never using a DB modelling tool.
Work Was Done In The Client!:
A very serious issue with Uniface was that the work was not done on the database server. It dragged all data into the Windows 3.1 client with 16 megs of RAM, and then did its work. So, if you had a big data set, it didn’t work very well. Retrieve and go for lunch.
Restrictive Proprietary Language:
Another major downfall of Uniface was that it only used its own proprietary language. That by itself was not so bad, or so surprising. But even though it was for databases, the language also did not do any Group By equivilent aggregate functions like Sum, or Avg, or Count.
The first report I was given was a complex report, summing up data over a number of different time periods. A Group By report!
If I did this using strict Uniface code, it would have had to drag every record into the 16 meg RAM Windows client. Then, I would have had to write loops to tally everything into sums! The whole machine would be useless for anything until it finished. I estimated that a simple report would take 10 hours, if it finished.
I struggled for weeks on this one, trying many things, reading everything in the books, and talking a lot to tech support.
The Trick To Making Uniface Work:
Uniface did have a SQL function. But it was only good for a single aggregate value. Say, Select count(*) from table1. It would return the single value result into a return variable. And the statement return code into another. Not very useful for working with big data sets.
What the SQL statement was useful for was to create objects in Oracle.
After a lot of struggle, where most would have given up, I figured out the secret. Put the work back on the database server. Take user input. Dynamically create a number of views in the Oracle database. Have one final entity in Uniface that synced with one final view. Retrieve.
Working with tools that didn’t work well, on old hardware, really taught me a LOT about tuning! You can see more of this in the database tuning presentation I gave at the Oracle User’s Group.
Poor Support for WWW:
Toward the end of the 1990’s, the WWW was moving into full swing. Uniface just didn’t work with it very well. After all, it was a client server model. We didn’t even go there.
From my questions and anwers on the newsgroups, I was known in the Uniface community. Compuware and I spoke about my going to work for them. Uniface skills took me to my first big contract. After that, there were a few and far between Uniface job offers, with very long drives. But I decided to stick with Oracle; there was a lot more jobs available.
In another post, I had a picture of the 34 inches of technical binders I had created. I later scanned them into PDF files. I still have the information, but not the bulk and weight of the paper. The very cool thing is, my Canon scanner does Optical Character Recognition (OCR), so I can actually copy and paste the characters.
Today I was doing some more scanning and came across some old Uniface notes I’d scanned. It brought back some memories.
Here are some of the earliest entries I made. Mainly for detailing the tricky syntax that worked.
Getting Dates Out of Oracle, and Into Uniface:
This Uniface statement creates a view. I was concerned with how to do it with date ranges. In this case, the SQL statement acts similar to how you use a string in DBMS_SQL.
sql “create or replace view v_rtesting as select prd_id, prd_from_date, prd_to_date from prd where prd_to_date >= to_date(‘%%$to_date$’, ‘dd-mon-yyyy’ ) and prd_from_date <= to_date (‘%%$from_date$’, ‘dd-mon-yyyy’ ) “, “def’
Be careful with the syntax.
– %% for the macro conversions.
– $memvar$ dollar signs around the memory variables
– dd-mm-yyyy , the connect date format.
– no return lines in the SQL statement. Just one long string.
A Note on the Using Views In Oracle Technique:
Problem was: the entity was not defined correctly in the database.
For views that have aggregate functions, the entity must be:
In the database,
Have Null values in the minimum and maximum occurences.
UNIFACE – DEALING WTH TIME
– EXTRACTING THE NUMBER OF SECONDS, MINUTES, AND HOURS
– EXTRACTING THE TOTAL NUMBER OF SECONDS
– the $start_time1$, $end_time1$, $total_time1$ variables are time variables.
– the $seconds$, $minutes$, and $hours$ variables are numeric variables.
$start_time1$ = $time retrieve setocc "table1" -1 ; this retrieved all records into the client and went to the last one setocc "table1" 1 ; this navigates back to the first record, ; whole process takes seconds or minutes. $end_time1$ = $time $total_time1$ = $end_time1$ - $start_time1$ $seconds$ = $total_time1$ [S] $minutes$ = $total_time1$ [n] ; YOU MUST USE N, NOT M FOR MINUTES $hours$ = $total_time1$[H] message "seconds: %%$seconds$, Minutes; %%$minutes$, Hours: %%$hours$" ;YOU MUST USE SOME LOCAL VARIABLES, AND ADD EVERYTHING UP. $seconds$ = $seconds$ + ($minutes$ * 60) + ($hours$ * 3600) message "Total Time to retrieve %%$hits records: %%$total_time1$ %%$seconds$."
Lessons From Uniface:
It’s hard to make a technology do what it not designed to do.
No matter how good a tool jockey, Uniface and certain technologies can only do so much.
Lots of tuning lessons, such as put the heavy lifting on the servers, not the client. See the tuning presentation.
The technology you use has a direct effect on the design, and analysis. Ie. While we used Uniface, we were not concerned with Cobol. Nor we were concerned with Java.
More to follow another time.