FEATURE STORY
JANUARY/FEBRUARY 1996
Oracle System Provides Smooth Sailing
for Busy New York/New Jersey Seaport
The rationale for conversion at NYSA will be familiar to many companies who have moved off their mainframes to minicomputers and PC-based systems. "Our two mainframes are more than ten years old," says Philip Krey, manager of database administration and applications development at NYSA. "Management decided they were too costly to maintain and not very efficient, so we decided to downsize. On the mainframe, for example, it could take us two to three weeks to develop a program. Relational-database technology would let us do the same task in about a week and enable us to respond to simple ad hoc requests in a matter of hours instead of days. So we looked at the various options and decided that Oracle best suited our needs."
NYSA's pilot project was an Oracle version of the company's mainframe tonnage system, a complex suite of 75 interlinked programs that track the vessels moving through the port as well as tracking the various commodities on the ships and their assessment rates. The system also makes it possible for management to perform statistical analyses on the ships and the cargo for financial reports. The tonnage system is critical, because it's how NYSA gets its revenue.
"We picked this as our pilot, because we knew if the system could do that, it could do anything else we could throw at it," says Krey. The pilot was entirely successful, and by November 1993, NYSA implemented an associationwide production version of the system.
All told, the system has many crucial components. For example, the Prior Day Ordering/Telephonic Hiring System (PDO/THS) lets hiring agents---sitting at an ASCII-character-based terminal or PC at central headquarters or anywhere up and down the docks---enter a request for longshoremen who match certain skill sets and presents the agents with a pool of candidates. Agents then order the number of dock workers needed to their pier for the next day at a specific start time. There are various daily sessions during which different types of hiring take place, such as same-day hiring for absentees, volunteer hiring to fill extra labor needs, and next-day hiring of lists and gangs (groups of people with related skills, ordered as a unit). All this has to interface with NYSA's Availability System, which indicates when a worker is on vacation, sick, or otherwise unable to work. A longshoreman can also be extended to work additional days, in which case the hiring agents have to update the system so that the individual does not appear in the pool of available candidates. Onscreen forms, developed in Oracle Forms (now Developer/2000), present the lists to the users, where they can check off the names of workers with the proper skill sets for hiring. These hiring sessions are controlled by a procedure incorporated into each program that checks a master runtime table for valid times of program execution.
The longshoremen call in to the Telephonic Hiring Center to obtain job information for the next day or to obtain shape credit for the day if they do not have a job. Shape credit is used in other application systems to determine eligibility for various fringe benefits, such as guaranteed-annual-income payments or a holiday payment. The PDO/THS Telephonic Call-In System eliminated the need for the old-fashioned shape-up centers, where the longshoremen would report each morning to obtain a job for the day. It also increased productivity, because the longshoremen know their job information the night before and report directly to the pier.
All ordering programs query the Availability System to make sure that individuals will not appear in the pool of candidates if they are unavailable for any reason. NYSA's Vacation Scheduling System also has to pass information to the Availability System so that the PDO/THS system does not allow a vacationing individual to appear in the pool of candidates to be hired. Additionally, an extensive logging procedure in all programs creates an audit trail for all online adds and updates---essential for tracking changes made to the data. The system maintains audit trails as stored procedures, which are called by programs as needed. Another procedure allows users to view the audit trail by simply pressing a function key.
"You begin to see the complexity of our systems as well as the unique requirements that are incorporated in them," says Krey. "We also have to keep the mainframe and Oracle versions in total sync. Because some of the related applications are still on the mainframe and need PDO data, we have to reverse-bridge the day's transactions to the mainframe each night. Since most of our applications talk to one another and are dependent on shared information, this bridging and reverse-bridging will continue until we're done with the conversion. Keeping all the data in sync on both platforms gives a whole new definition to the term smoke and mirrors!"
oday the system sits on two Model R24 RS/6000s. There are 72 gigabytes of 933 Direct Access Storage Device (DASD), two 5-gigabyte tape drives, sixteen 3480 tape drives, and two 2,200-line-per-minute printers attached to both RS/6000s. Expansion plans include upgrading the DASD capacity to 96 gigabytes. The network consists primarily of ASCII-character-based terminals, with certain users accessing the Oracle applications via terminal emulation over NYSA's Novell local-area network (LAN). All applications are logically separated into separate instances, providing easier maintenance and a smooth expansion path in case NYSA decides to distribute any more services in the future.
"We used Oracle's RDBMS, SQL*Forms and SQL*ReportWriter [now components of Developer/2000], and SQL*Plus to build our applications, and we used SQL*Menu [now also a component of Developer/2000], with its role facilities, to control access to the system," says Fagone. "After logging into AIX and supplying a password, users are presented with a master menu, which contains only those applications they have authorization to access. When users select an application, the system takes a path through SQL*Net to the database instance that contains that particular application."
nce there, users are presented with a main menu that provides access to the individual programs within that application. Users remain within the main-menu-to-program environment and return to the master menu only when they are required to move onto another application. When users require data from other application instances, the system accesses the data via Oracle's database-link facilities. "This access methodology provides us with a great deal of security and prevents users from getting anywhere near the $-prompt," Fagone says. "It also allows us to bring up a mission-critical instance by itself if the need arises and thus facilitates maintenance work on all of the instances. We've implemented a distributed model on a centralized platform in order to maintain the integrity, recoverability, and change-management benefits associated with centralized control and at the same time retain the flexibility to distribute processes and/or applications if and when that is appropriate."
Converting to Oracle meant a steep learning curve initially for in-house programmers, although computer-based training in the Oracle toolset helped get them up to speed in relational-database technology.
Even so, Krey reports better than a 50 percent increase in development productivity, faster online processing, and a significant improvement in user-friendliness as a consequence of the menu-driven and zoom capabilities of the system. Users now have pick lists from which to choose workers, with record-locking capability and faster transaction time---even with as many as 200 simultaneous users on the system. "Management also benefits from the new system, since we can now supply reports to executives in a matter of hours rather than weeks," says Krey. "We can more easily translate data into Lotus spreadsheets, for example, for efficient financial management, where executives can do all the slicing and dicing they want."
NYSA also has implemented an extensive hands-on training program, complete with a user manual and a keyboard template, to educate the user community, and has developed a demonstration system to use as both a training tool and a way to show off the new system.
"The migration is in full swing, with multiple applications being converted simultaneously," says Krey. "If all goes as scheduled, the majority of the applications will be migrated by the first quarter of 1996 and the conversion will be concluded with a mainframe garage sale at the end of the year."
Michael Miley is a freelance writer based in San Francisco, California. You can reach him via e-mail at mikemiley@aol.com.
Copyright © 1994, 1995 & 1996 Oracle Corporation. All Rights Reserved.