Ship Shape

Oracle System Provides Smooth Sailing
for Busy New York/New Jersey Seaport

The Port of New York/New Jersey is the busiest one on the eastern seaboard, with more than 4,500 ships calling last year. It attracts every conceivable type of vessel, from modern container ships, car carriers, and tankers to the old-fashioned bulk-cargo ships and passenger liners. Managing the flow of traffic through the port, which involves more than 75 shipping companies, is extremely complex. Assigning longshoremen to load and unload the ships each day also requires extensive coordination, since the workers provide services across companies and their hiring is governed by elaborate rules for seniority and skill sets, fixed by the labor contract companies have with the International Longshoremen's Union. Managing the deployment of the longshoremen and the administration of fringe benefits is the responsibility of the New York Shipping Association (NYSA). With a main office in the World Trade Center in New York City, NYSA represents all the companies that call at the port. Member companies look to NYSA as their central processing and record-keeping bureau for administering the benefits and responsibilities of their labor contracts.


Over the past three years, NYSA set itself a monumental task: to migrate its entire mainframe applications portfolio, consisting of 40 application systems and more than 2,000 online and batch programs developed over the past 27 years in MVS/CICS/VSAM/COBOL, to the Oracle RDBMS running under AIX on IBM RS/6000s. The goal is to convert operations from legacy systems that are expensive and cumbersome to maintain, and to reap the benefits of moving to relational-database technology for superior data management, shortened development time, and ease of use. To date, NYSA's two most mission-critical applications have made the move to Oracle. The first tracks the vessels moving in and out of the port, and the second deploys the longshoremen to the various piers to load and unload the ships.

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.


Next in line for conversion was NYSA's most mission-critical application: a contention system that deploys some 3,000 longshoremen with the appropriate skill sets to the various piers in the port, based on the types of vessels that require loading and unloading each day. "This system consists of more than 100 very complex programs that have to be sensitive to the hiring rules of the labor contract, includingseniority and skill sets," says Krey. "It also provides the Waterfront Commission, a bistate policing agency, with the ability to monitor the hiring practices throughout the port."

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.

Ship Yard PhotoAll 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!"


It took NYSA some time to develop a methodology for the conversion and to build a foundation for it. This meant ensuring a consistent look and feel across all applications, developing naming conventions for tables and programs, and remapping the function keys on the terminals to perform specific operations. NYSA developed stored procedures to eliminate redundant code, to reduce development time, and to ensure consistent results across all programs. Based on their applications portfolio, their business requirements, and the makeup of their approximately 250 users (they are spread out over the World Trade Center, the New York/New Jersey waterfronts, and a variety of other locations in the bistate area), NYSA decided to implement a centralized strategy for the new Oracle system. Reliability requirements were high. "The system had to be available seven days a week, from 6 a.m. to midnight, and we could allow for no more than a 30-minute outage during a system-component failure," explains Blase Fagone, manager of technical services. "AIX RS/6000 facilities fit in with Oracle's recovery components quite well, and we configured the system with redundant hardware and switching capabilities to handle just about any type of failure."

Today 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."

Once 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.


At press time, NYSA was in the process of implementing Payroll Reporting and nearing completion of converting its Guaranteed Annual Income and Pension Payments systems to Oracle. The Payroll Reporting System, consisting of 100 programs, enables NYSA to consolidate payroll information across the various companies for each worker. The Guaranteed Annual Income System provides individuals with their guaranteed pay if there was not enough work for them on a given day. And the new version of Pension Payments will include an electronic-funds-transfer system to enable NYSA to make deposits directly to a retiree's bank account.

"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.

[Top | Back | Home ]