Suprtool Selects For Oracle SQL-Loader

Hammacher Schlemmer is a leading mail-order retailer of unique gifts and innovative products. Its Information System processes are as leading edge as the products it offers.

Hammacher Schlemmer uses Ecometry on an HP e3000 for its mail-order application. Robelle's Suprtool is bundled with Ecometry, and is used extensively within the Ecometry application to speed it up. Hammacher Schlemmer uses Suprtool on its own, to process mail-order data and provide functionality beyond what is provided with Ecometry. This is a common tactic for Ecometry users, and is one with which Jim Dunn, an I.S. Operations Manager at Hammacher Schlemmer, is very familiar.

The company has a Forecasting Data Mart from Direct Tech Inc., using Oracle on Windows NT. Analysts use Brio Insight to look at the data mart. Hammacher Schlemmer has augmented the Oracle data mart with new tables. These include data that allows its logistics people in the warehouse to analyze shipping manifests, and to manage logistics as a profit center.

Jim Dunn has written jobs that use the Suprtool suite of "database handyman" tools. Suprtool and Suprlink provide ultra-high-speed selecting and merging of IMAGE data, while STExport prepares files for use on other platforms and applications. In Jim's jobs, the Ecometry data is extracted by Suprtool/Suprlink and reformatted for Oracle's SQL-Loader by STExport. STExport "has been the real life saver here as far as formatting for feeds into Oracle tables", says Jim.

Most of the data is manifest and airborne data, loaded into new Oracle tables created for this purpose. The resulting reports and charts from the data mart "provide more info than the standard Ecometry Manifest provides", according to Jim.

Jim takes advantage of Suprtool's ability to describe data structures that IMAGE has no knowledge of. For example, the Ecometry database has a field called Manifest-Flags, which the IMAGE database knows only as a single twenty-character field, but which is in fact a series of a dozen independent flags and fields. Jim uses Suprtool to define and extract only the sub-fields he is interested in for his data mart.

DEFINE OSDMFLG,MANIFEST-FLAGS:2,1
DEFINE AHFLG,MANIFEST-FLAGS:7,1
DEFINE RDFLG,MANIFEST-FLAGS:0,1
EXTRACT OSDMFLG,AHFLG,RDFLG

Another smart Suprtool feature that Jim uses is the ability to select data using generic dates. Jim's Suprtool job runs weekly, selecting manifest records for shipments in the last seven days, without requiring him to hard-code any specific dates. This allows the job to run without needing an operator to insert dates every time the job is launched.

ITEM SHIP-DATE,DATE,YYYYMMDD
IF SHIP-DATE>=$TODAY(-7) AND SHIP-DATE <=$TODAY(-1)

Jim uses STExport to prepare the data in a format that the SQL-Loader will accept. STExport lets him define the format of numeric data, including leading zeros and the position of the sign.

!COMMENT *** PREPARE DATA FOR ORACLE SQL-LOADER ***
!RUN STEXPORT.PUB.ROBELLE
IN DMRTABHM
ZERO LEADING
QUOTE NONE
COLUMNS FIXED
SIGN TRAILING
OUTPUT ABHMDATA
XEQ
EXIT
E

The resulting files are sent using the HP e3000's FTP client to the NT computer where the data mart resides.

!COMMENT *** FTP OUTPUT FILES TO DATAMART ***
!RUN FTP.ARPA.SYS
open 123.456.789.012
user <<login string and password>>
ascii
exitOnError
cd /isdmdata
cd macsdata_in
put ABHMDATA.pub.hsmacs ABHMDATA.txt
dir
quit

In addition to shipping data, Jim is also exporting order data to provide a cross reference between the Hammacher Schlemmer order numbers and the order numbers used by Hammacher Schlemmer's affiliates. Similarly, he is exporting country data to provide a cross reference between numeric country codes and the corresponding country names.

You now know how one user exported to Oracle. If you are migrating an application from MPE, consult our Migration Resource Page.

To learn more about exporting, including MS Access and Excel, read our tutorial, Sharing HP e3000 Data to the World.

You might also be interested in two Suprtool articles on exporting 3000 data to mySQL and exporting to Excel.

Or another article on exporting to XML, with an introduction to XML for newcovers.