Robelle Tech: Try a new language: PHP

November 2001

Get the most out of your Robelle tools

Selects for Oracle SQL-Loader

By Mike Shumko

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 one with which Jim Dunn, IS 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 allow its logistics people in the warehouse to analyze shipping manifests and 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 Dunn '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." he says.

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

He 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 20-character field, but which is in fact a series of a dozen independent flags and fields. Dunn 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 Dunn uses is the ability to select data using generic dates. Dunn'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)

Dunn uses STExport to prepare the data in a format 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, Dunn also exports 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 exports country data to provide a cross-reference between numeric country codes and the corresponding country names.