Re: ODBC performance with UPS Worldship

[Douglas Handy <dhandy1@xxxxxxxxxxxxx>]

Roger,

>Our FedEx Ship Manager software has customized SQL statements which go
>straight to the correct files and fields.

Well, I guess ours does too but the customized SQL statement is a single INSERT
into the same table UPS Worldship is using.  This lets me process them both
identically from the AS/400 side.  If I need to make any changes, Fed Ex does
not need to get involved -- they simply pass all the fields to my table and the
triggers take it from there.

>UPS Worldship has a field mapping
>utility that (apparently) caused our performance problems. The AS/400 DB2
>fields that export and import data are all character fields set to be the
>same size as the corresponding UPS Worldship field definitions (available
>from the Worldship help).

The export can in fact handle numeric fields, at least in theory.  Initially, it
was only one numeric field which caused an error.  Then in trying to resolve
that, other field(s) would get errors, including character fields.

I also tried exporting to character fields, then parsing it to a number.  No big
deal, I've had one of those functions in my ILE service programs for years.

But that didn't solve it either.  In hindsight, in part it may have been related
to Client Access trying to keep a SQLPKG based on previous field definitions and
it not detecting I had change field sizes/types while trying to resolve it.  But
that doesn't explain the initial failure, nor the failures when I would try new
maps with a different subset of fields to isolate the fields in error.

I spent a couple of days fighting the issue and working with UPS level 1 and
level 2.  When a third-party ODBC works from the first attempt, it doesn't take
too much to justify just licensing it rather than continuing to fight it,
especialy when you're within a couple of days of it scheduled to go live.

Doug