Database and network news & products: maximising Oracle 8i and Designer 6.

Following Oracles OpenWorld conference in 1998, plans were drawn for iWorld, a completely re-engineered and web enabled product. Several objectives were defined from the outset in order to determine the technology stack for iworld. These objectives are as follows:

* Repository based

* Web-enabled

* Rapid Application Development

* Low maintenance

* Consistent look-and-feel

* Future proof

These high-level objectives led to Oracle's Interact products incorporating Oracle 8i--the Internet database, Oracle Designer, Oracle Developer Server and Oracle Application Server. requirements.

Oracle Designer 6 (6i) Oracle's flagship modelling and design tool provided First with a solid foundation for re-engineering their existing product set.

Initially, it was decided that the existing server model would be recaptured producing a pure logical design, which would accurately reflect the data requirements of the products.

In addition, all business processes needed to be captured, as a few key individuals in the organisation largely held detailed knowledge of functional requirements. This was obviously a risk and hindered the support and maintenance of the products, and as such, that knowledge needed to be reclaimed and stored in a central repository.

These two requirements have been fully satisfied by the logical modelling capabilities of Designer 6.

Teams of analysts steadily decompose the current server model and application code to produce a new set of Entity Relationship Diagrams and Function Hierarchy Diagrams (FHD) ensuring that no functionality of the products is lost. The functional decomposition is performed with constant input and review from the product consultants who have a detailed knowledge of both the functionality of the applications and how customers make use of them.

Data Modelling 

The analysts adhere to strict standards and conventions whilst decomposing the logical data model to ensure no ambiguity in the final design.

There are certain highly complex areas of the existing server model which are "ring-fenced", that is the underlying structure of a table cannot be altered as it would require too much re-work to functionality, and the impact to customers would be too great. The flexibility of Designer 6 allows the analysts to take this approach rather than forcing a totally purist design.

The resulting Entity Relationship Diagrams will then feed into the next phase of development, that of Database and Application Design.

Function Modelling

Once again a clearly defined set of standards and conventions are adopted by the analysts when decomposing the current forms back to business processes. Much of the functional analysis takes the form of a "CRUD" analysis on entities, so that data usage's by functions are captured and modelled as part of the Function Hierarchy Diagram.

Detailed and unambiguous descriptions for each function are supplied by the analyst, including references to existing application code, in order that developers can clearly see from where functionality is derived.

Through a series of workshops, the fully qualified definitions of these functions will be ratified and stored permanently in the Designer repository. The benefits of this are manifold, as these definitions will assist greatly during application design, development and also future support and enhancements.

Design & Build

First Software has defined a set of module templates, which provide the consistent look-and-feel for the entire application. These templates are used by Designer 6 to provide 100% generation of Oracle Forms; which will run in both traditional client/server environments and, more importantly, via Java-enabled web browsers.

As for database design, First make extensive use of the Design Editor tool to produce server models, which will be secure, performant and flexible. As Designer 6 is fully database aware, complex server-side features, such as user-defined object types, collections and partitioning, can be successfully modelled in the Designer environment. Given a completed set of ERD's and FHD's for a particular product area, a team of Designers will then transpose these to a Logical Database Design and Module Designs.

Logical Database Design

Once again a set of design conventions, developed internally, will assist the database designer to make the right decision when transposing entities to tables, and relationships to constraints. The aim of the iworld database is to be fully-constrained, by the use of Oracle 8i's rich referential integrity features along with server-side triggers which will process more complex data relationships and dependencies. Thus the power of the applications will be moved from a fat client into the robust database server.

Application Design

Following an application design workshop, the designer will also colour-code the FHD in Designer, to signify how business functions will be implemented as modules in the application.

Finally a "Build Spreadsheet" is produced containing detailed design specifications for all the modules required, along with references to the business functions that they implement. The spreadsheet is then released to development team leaders for the modules to be built.

Module Build

By looking at various applications that are on the market we have been able to use designer to produce a 'best of breed' user interface. This results inmodules being categorised into one of 6 module types, which correspond to templates. These templates are as follows:

1. Workspace. Query-only module containing numerous related blocks. Each block contains basic information about critical tables in the particular product area. This is the first module that the user will be presented with after choosing a product area from the main menu.

2. Dataview. A Dataview provides further detail information about selected records from the Workspace. A user will select one, or more records from the Workspace, and these will be passed to a Dataview. The Dataview module consists of a Master block, and several detail blocks, all pertaining to the selected records.

3. Wizard A Wizard is typically used to create or update complex data. The Wizard module consists of several "pages", usually equating to blocks, which are logically structured to represent the business process in a simple interface.

4. Query. A Query dialogue is used to allow the entry of complex query criteria for Workspaces, Dataviews and Lists-of-Values. Rather than constrain the user to the traditional "Enter-Query" and "Execute-Query" functionality, these modules allow much greater selectivity of data.

5. Collector. A read-only module consisting of multiple master blocks presented on a tabbed canvas. These master blocks will be implicitly related, and the modules are typically used for set-up and administration, such as managing users and security.

6. Dialog, iworld contains 3 types of dialog Insert, Update and Function. These are simple modules, used for the creation and amendment of data, usually on a single table. Function dialogs are also used to implement business functions, which are not necessarily DML based.

The templates themselves are designed to be simple interfaces, supporting atomic transactions. By keeping modules simple, the overall number of modules increases, but web-deployment is greatly benefited.

The reason for this is that network bandwidth is at a premium, so simple and compact modules reduce the impact on the network, hence improving the performance of the entire application.

The templates contain standard events, which implement Forms trigger code. This code is handled, not in the form itself, but in a hierarchy of code libraries. Using this event-driven method, there is no actual coding required in Designer.

In Designer however, there are template module definitions, contained in the repository which correspond to the template forms used to manage code. Each new module defined by a developer is copied from one of these repository-based templates, and then modified to include the correct data usage's.

Also held in Designer are preference sets for each module type. These preference sets define various look-and-feel parameters for the Forms Generator so that each module of a particular type conforms to these parameters and is consistent with all other modules.

As the server model is held entirely in the repository, and has been fully constrained, dynamic lists-of-values and lookup usage's are completely automated by Designer. This saves an enormous amount of coding by the developer. Once detail and lookup usage's are created in the module definition, the developer will make use of standard components held in Oracle Developer Object Libraries to ensure a consistent look-and-feel. For example, the object library contains reusable objects such as standard blocks, graphical items and complete components such as a fully functional calendar.

The contents of the object libraries are then used by Designer's Forms Generator to create the final module. All of the components of the resulting form are sub-classed from the object libraries. This means that if a standard component changes, only the object library needs to be modified, and then modules can be re-generated from Designer. No coding is required.

Code Hierarchy

Each module has a corresponding code library. This code library is also copied from an existing template and then modified. The "MODLIB", as it is known, contains code that is specific to the module.

Attached to the MODLIB is another code library containing event code, specific to the template type of the module.

Finally, attached to the template library is a common generic code library containing handlers for all the standard events used by the application. This 3-tier code hierarchy allows infinite flexibility with the minimum amount of development effort.

The client-side code hierarchy also has a server-side equivalent. In order to avoid network traffic, any DML or SOL is performed in the server via a series of packages. In the first instance, a module is likely to have a server-side package of the same name, which performs all the required database operations for that module.

Further, most tables in iworld have an API package, which contains procedures and functions for validation, DML and other operations. For example, the PROPERTIES table has an associated package called S-PROPERTIES.

Designer 6i

Following First's strategic decision to employ Designer 6 as the primary design and development tool for iworld, First have also been heavily involved in the Designer 6i Beta programme, and will be one of the first UK companies to make full use of Designer 6i for configuration management. The Designer 6i repository has been re-designed to support much more fine-grained control of versioning repository elements, and configuration management. In addition, the new repository structure allows fighter control of user privileges. This means that we have a sound platform from which we can deliver our production software in strictly defined manner that can be easily supported.

Oracle 8i--the Internet Database

Prior to iworld, First's products had always made use of the Oracle database, but with the advent of 8i, the opportunity arose to maximise the use of the server and the array of new features that 8i has to offer.

Probably the most significant change in the way in which First Software's products now use the 8i database is the management of data relationships.

Previous versions of First products managed data integrity in the application, with little or no referential integrity in the server iworld now implements a fully-constraint database by using referential integrity constraints and database triggers which first appeared in Oracle 7, but also deferred constraints introduced in Oracle 8. iworld makes use of many of the new features available in Oracle 8i as the following list illustrates.

* Advanced Queuing.

Used for all message handling All messages in iworld are stored and maintained in the database. No messages are hard-coded in the application. Each message has a type, which is one of the following:

1. Error. A fatal error in processing, invokes a rollback of transactions.

2. Information. Usually confirmation messages that a process has been successful.

3. Question. A question that requires user input, followed by server-side processing

4. Validation. During record-level validation, many of these messages can be produced, requiring the user to correct potential errors in their data.

5. Warning. A message to inform the user that their data may not conform to usual standards, though it will not prevent a transaction from happening.

An API has been built which the developers invoke to put a particular message onto one of the server-side message queues.

The default queue is used to process all of the above message types, except questions. There is also a P/SOL queue, which is used as an asynchronous scheduler for running any server-side PISQL batch process. The question-type messages are managed by a dedicated queue which handshakes with the client application.

* PL/SQL Bulk operations. Used extensively to avoid expensive context switching during bulk DML processing. Several iworld processes require the manipulation of large numbers of rows. For example, a collection of properties may be reorganised into a different administration unit using our Estates Management product. This necessary business process will incur many thousands of DML operations.

To improve the performance of these operations, and also to make better use of Oracle's object-relational model and native dynamic SQL, PL/SQL Bulk binds and collects are used extensively.

These operations remove the cost of context switching between PUSOL and SQL engines, thus allowing many thousands of rows to be processed via a single context switch.

* Object Owes and collections. Used by the message handling system, complex remote procedure calls from Developer and collection unnesting for rapid SQL operations on in-memory tables.

* Partitioning. Used for several potentially large and highly active tables in iworld.

By partitioning large tables, query and bulk-DML performance can be dramatically improved. Oracle's internal query optimiser determines either at parse or execute time, which partitions are required to satisfy a query. The optimiser will then only read from those partitions, dramatically reducing I/O overheads.

Several core tables in iworld will contain potentially many millions of rows. These tables are also likely to be required in many business processes, and as such they may be partitioned to improve the overall performance.

For example, iworld uses a generic parameters model to manage user-defined other fields. These allow the user to create pseudo-columns on tables of their choice. All of the data in these pseudo-columns is held in one table, PARAMETER VALUES. This table will potentially be very large, and contain highly volatile data, and as such is an ideal candidate for range-based partitioning.

* Summary Management. Used by First QueryView and TargetView, management information reporting tools. iWorld is predominantly an OLTP application. However, First have developed decision-support products which derive their data from the iworld database. These products make use of Oracle 8i's summary management features. Summary management allows base transaction data to be rolled-up by particular dimensions, creating a hierarchy of aggregated data. By defining these dimensions and hierarchies in the database, Oracle is able to selectively re-write queries on-the-fly so that they find the optimum summary table to supply the correct data.

* Locally managed tablespaces. Zero fragmentation, and dramatically improves dynamic extent allocation A locally-managed tablespace manages its extents via a small bitmap contained in the header of each data file. This avoids the need for the database to recursively scan its data dictionary every time a new extent is required in the tablespace. It simply reads the data file bitmap and looks for the next available slot.

Combining this feature with uniform extents completely eliminates data fragmentation and simplifies storage management for DBA'S.

* Index-organised tables. Used for reference tables which are always accessed via a primary key, and are subject to little DML. An IOT negates the need for a separate table and index segment. All columns of the table are contained in index blocks. This has a two-fold benefit. Firstly it reduces storage requirements, and secondly it improves performance as a single I/O operation can retrieve all of the columns from the table.

DML performance can be reduced however, due to the constant maintenance of the IOT's B-Tree structure, therefore the use of IOT's is limited to low-DML tables.

* Intermedia Text. Used for on-line documentation searching.

* Internet File System. Used for First DIP & Workflow products

* Oracle JServer. Use of Java Stored Procedures and Enterprise JavaBeans for both iworld and DIP1 Workflow. www.firstsoftware.com

Free Pocket References Guide To Acronyms

67% of 1T managers admit that when it comes to the latest IT acronyms, they'd prefer to pretend they know what they mean, rather than admit ignorance.

To remedy this problem, Apogee Networks has launched a pocket guide, enabling users to decipher acronyms quickly and efficiently.

The guide is the size of a small diary and contains definitions of over 200 IP Billing and Telephony or telecoms-related acronyms. These range from the everyday ISP, LAN, ADSL and CRM to less 'mainstream' acronyms, such as PVC (Permanent VirtualCircuit) and RIP (Routing Information Protocol).

"The number of acronyms being used is becoming too much for anyone to digest," said Andrew Burroughs, chief marketing officer at Apogee Networks. "This is why we compiled the guide, and feedback has shown that there is a real demand for this kind of document across the entire IT industry, including IP billing and wireless."

For a copy of the guide, contact Louisa Griffith at Apogee Networks. Tel: [phone]