Re: ODBC performance with UPS Worldship

Dave,

>I am working on connecting a PC with UPS Worldship software via ODBC to the
>AS/400 for a client.  The goal is to update the customer order freight
>charges when the UPS label is printed.

I just went throught this process at a client around the first of the year.
They only use a single machine for UPS Worldship because most of their shipping
is via Fed Ex Ship Manager.

>From the time the "Process Shipment" button is clicked, it
>takes 30+ seconds to complete a single shipping transaction.  If I edit an
>existing transaction, the entire process can take 45 seconds after the
>"Process Shipment" button is pressed.  I am not too excited about this type
>of response.

When they scan a label, does the import mapping occur fast?  IOW, does it
quickly display the customer name/address, shipment method, referecne numbers,
etc.?  You only mention a delay on the export side, when "Process Shipment" is
pressed.

If the import happens fast, then the SELECT is making use of an index to satisfy
the condition of the WHERE clause.  If the import isn't immediate, make sure the
talbe you are importing from has a logical built over the scan ID field.

On the export side, do you have Worldship set to export each package immediately
as it is weighed, or to wait and send all packages at once when the "Process
Shipment" is performed?

Does the Worldship export directly update the customer order freight charges, or
does it export to an intermediate table which you use to update the order?

In my case, I have it set to export to a simple table used exclusively to
collect package data from the scale systems.  I have both Worldship and Ship
Manager only performing INSERTs, never an UPDATE even for Voids etc.  The scale
simply does an INSERT to a physical file with the transaction date.  The table
has a trigger on it which is reposible for adjusting the order charges and doing
the rest of the business rules processing.

Are you also using a trigger in a similar fashion?  If not, what are you doing?

If you are using a trigger, what happens to performance when you remove the
trigger and just let ODBC insert/update the data file but you don't do the
business logic processing in the trigger?

If performance is fast, then the problem is in the trigger and business rules
processing side not the ODBC layer.  If performance is still bad with the
triggers removed, than you need to focus on the ODBC.

>The client is on V5R1, but I do not know what version of client
>access they are using (they are using the client access ODBC driver).

My client is still on V4R5, and tried the V4R4, V4R5, and V5R1 versions of CA
Express ODBC drivers, with service packs.  In each case, the import performed
flawlessly, but the export gave us lots of grief with mapping some of the
numeric amount and weight fields.  We'd get data truncation and/or mapping
errors.  We tried all kinds of permutations on using over sized fields,
exporting to a character field and interpreting the string data, etc.  The
result was always inconsistent.

So I installed a demo copy of a third-party ODBC driver, and it worked
flawlessly from the very first test, even with putting all numeric fields in
their original design specs.  OTOH, the Fed Ex Ship Manager software never had a
problem using the Client Access ODBC driver, even though it inserts into the
exact same table and populates almost exactly the same set of fields (they send
me more than UPS does).

The customer needed to go live, so just bought the thrid-party ODBC driver to
use on the UPS scale.  The Fed Ex side continues to use the Client Access ODBC
driver.

But performance was never the problem -- data truncation and mapping errors were
what we encountered using Worldship and the CA Express driver.

Doug