Home Contact Us Products About Us Fine Print Staff Partners
Suprtool Qedit Qedit for Windows Robelle
Daily Support Issues Disaster Top Ten Software Version Information Change Notices/Installation Information Submit Problem Report Change Notices/Installation Information How Do I? Business Hours Suprtool Code Generation
E-Mail: support@robelle.com Tel: 289.480.1060
Change Contact at your Site Other Updates to Your Account Dealing with CPU Upgrades New Customer Information Sign up for Direct Support Licensing Policies Request Suprtool demo/info Request Qedit demo/info Request Qedit for Windows Demo/Info
Business Hours
Fiserv/Spectrum New for Fiserv/Spectrum Users Amisys New for Amisys Users Ecometry New for Ecometry Users Education Applications
Historical Newsletter HP3000 Evolution Book Homesteading on the HP3000 Links and Info about MPE/iX HP 3000 Migration Links/Info Links and Info about HP-UX Product Manuals Suprtool Training Technical Papers Tutorials Technical Articles HP Encyclopedia Consultants Corner

Suprtool

Suprtool: Synchronize Oracle/HP-UX with IMAGE/MPE

Suprtool on MPE and HP-UX

Alliant Foodservice, Inc. is one of the nation's largest foodservice distributors, operating a coast-to-coast network of 45 distribution centers, serving restaurant chains and institutions.

Alan Wyman is the Programming Manager of the Minneapolis MN distribution center, or "district". Most of the 45 districts use the company's standard Tandem mainframe applications. Three districts are AS 400 based, while Alan's and another district are HP e3000 based. Alan's district wrote its own homegrown applications, which are also used by the other HP e3000 district.

The company maintains a website, AlliantLink.com, which is an e-commerce system used by its customers. It is the front-end for an Oracle data warehouse running on a HP 9000 UNIX system.

Synchronizing IMAGE and Oracle Databases

Suprtool is used on both the HP e3000 and on the HP 9000 systems to move data back and forth.

Wyman's team has written jobs that use Suprtool to send data stored in HP e3000 IMAGE databases to corporate headquarters for loading into the Oracle database on the HP 9000. Data about sales, customers, vendors, and descriptions are prepared into self-describing (SD) files on the HP e3000. As the files are prepared, the data is massaged into the format that the Oracle database requires, and the product item numbers are translated to match corporate's number scheme. 

!SUPRTOOL
BASE      AFSDB
GET       ITEM-MASTER

DEFINE    MFR_PROD_NBR,1,15,BYTE     {15 bytes in oracle db}
EXTRACT   MFR_PROD_NBR = MANUF-CODE  {16 bytes in image db}

DEFINE    UNIT_COST,1,6,PACKED       {11 packed digits in oracle}
ITEM      UNIT_COST,DECIMAL,2        {two decimal places}
EXTRACT   UNIT_COST = LIC-CUR-COST   {14 digits in image db}

Because Suprtool is used on both platforms, Alan can take advantage of Suprtool's proprietary self-describing file format to transfer the data descriptions along with the data. In this way there is no need to define the layout of the file when it is received by Suprtool on the HP 9000. Information such as data types and the number of decimal places in numeric fields is transferred from the HP e3000 to the HP 9000 via the self-describing format. On the HP e3000 the self-describing metadata is stored in the same file as the data, when the file is created using the LINK option in Suprtool. 

OUTPUT    EXTRFILE,LINK

On the HP 9000 the self-describing metadata is stored separately from the data, in a second file. The Robelle SDUNIX utility program on the HP 3000 creates the second metadata-only file from the LINK file. !SDUNIX;INFO="EXTRFILE EXSDFILE NOLF"

These two files are sent by FTP to the HP 9000 where the Oracle database resides. 

!FTP UNIXSYSNAME
USER USERNAME PASSWORD
BINARY
PUT EXTRFILE /var/opt/afs/load/extrfile
PUT EXSDFILE /var/opt/afs/load/extrfile.sd
EXIT

On the HP 9000 UNIX machine, Suprtool is used to load the data file into the Oracle database. The extrfile.sd file is automatically referenced by Suprtool/UX when the extrfile data file is accessed. 

$/opt/robelle/bin/suprtool
open  oracle scott tiger
input /var/opt/afs/load/extrfile
add   tablename
exit

There, and Back Again

Similarly, data flows from the HP 9000 back to the HP e3000, updating vendor tables and product descriptions in the IMAGE databases. Alliant uses Suprtool on the HP 9000 to prepare the Oracle data into SD files, and Suprtool on the HP e3000 to load the data into IMAGE. FTP is used to move the files.

The process works so well for the corporate data center that they changed a manual extract and manipulate process that was sending data to the district into a daily automated Suprtool extract and FTP script.

Alliant had tried a comprehensive data replication product to keep the IMAGE and Oracle databases synchronized, but found that it consumed too many system resources. The solution using Suprtool and FTP is extremely efficient and inexpensive.

Creating Download Files for Clients

Another major use of Suprtool at Alliant Foodservice is to prepare customized files for clients. These clients include restaurant chains, schools, prisons, and hospitals, each with varying data requirements. These files, which include data such as product usage histories and invoice details, are prepared weekly or monthly using the STExport component of Suprtool. STExport is a specialized tool whose purpose is to produce industry-standard delimited files which the clients can load into a variety of applications, including spreadsheets such as Excel.

In this example, after using Suprtool and Suprlink to combine data from multiple datasets into a single file, Alliant uses STExport to format the data for Excel. The task adds a record to the start of the file with the names of all the fields contained in the file, and converts dates from their yymmdd format in the IMAGE database to an Excel-compatible mm/dd/yyyy format: 

!STEXPORT
input   file
heading fieldnames
date    mmddyyyy "/"
output  newfile

The resulting file is a standard quotes-and-commas delimited file, with the trailing spaces removed from the fixed-length IMAGE fields.

The files are sent to the clients via e-mail, using the free sendmail component available for the HP 3000 from the Hewlett-Packard website. Month-end is a busy time, with hundreds of files and e-mails being sent to clients.

The ability to produce these custom files is a huge competitive advantage for Alliant. While many companies can provide food and related services, Alliant has received government contracts based on its ability to produce the report files that the client needed.

Suprtool Is Easy To Use

Alan likes to point out that you don't have to be a programmer to use Suprtool effectively. Ninety percent of Alliant's Suprtool jobs are created by one of Alan's colleagues, whose formal training is not as a programmer, but as a mortician. He learned Suprtool while working as an operator, and picked up additional performance tips at a Suprtool class.

Efficient and Cost-Effective

Using Suprtool/iX on the HP 3000 and Suprtool/UX on the HP 9000, Alliant has created an inexpensive system for keeping its HP 3000 districts integrated and synchronized with the corporate Oracle database. And using STExport, Alliant easily creates customized data files for sending to clients by e-mail.

These high-efficiency tools have enabled Alliant to provide valuable services to its clients and stay ahead of its competitors.

For more articles on transforming data, visit Robelle's Migration Web Center. There you will find in-depth technical articles on Oracle, Excel, Eloquence, and many other related topics.