RE: Object Allocation Problem - or Something Else?

Jerry,

Does OLE DB use a separate SQL INSERT statement for each row added to DTOORDERS? I believe SQL will only lock a table while a statement is running. If each row is a separate SQL statement your ALCOBJ can lock the file in the middle of an order upload.

Paul Morgan

Principal Programmer Analyst
IT Supply Chain/Replenishment


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry C. Adams
Sent: Tuesday, February 21, 2012 10:37 AM
To: Midrange-L
Subject: Object Allocation Problem - or Something Else?

Our company has 20+ retail outlets. Until recently, they were sending
replenishment orders to us in a rather convoluted manner (which I'd rather
not get into).



The company that provides our Point-of-Sales applications and I teamed up so
that the stores could send an order directly to the System i. I don't know
much about the POS side, but the programmer there said he used OLE DB to
connect, which we both thought was great because I could create an SQL DDL
file on the System i into which the orders would be dumped.



On my end I wrote a process that runs continuously from about 0700 - 1600
and checks for new orders. The actual order processing takes only about a
minute or less, and then goes to sleep for 10 minutes.



Before my process starts processing the order bucket file, it ALCOBJ the
file. I know that, if I allocate the file *before* a store tries to send me
an order, the POS errors out because I once had the program in debug and
forgot to hold the process on the job queue. The actual command is:



ALCOBJ OBJ((DTOORDERS *FILE *EXCL)) WAIT(60) SCOPE(*JOB)



The problem that we have been experiencing is that, occasionally, I will get
only part of the order, say 10 lines out of 100 (it varies). I have a check
at my end (the store's purchase order number) to be sure that the store
managers (not the sharpest knives in the drawer) don't try to send the same
order a second (or more) time. But on the next cycle the rest of the order
is there and, naturally, my program rejects it as being a duplicate.



What I don't know, and have been unable to test, is whether or not the
ALCOBJ actually works if the store has already made the connection to my
bucket file when I allocate the file and start processing. This seems to be
what is happening, but is it possible? I do know that the orders are not
being submitted twice.



Thanks.



Jerry C. Adams

IBM i Programmer/Analyst

Brooks Robinson belongs in a higher league. -Pete Rose

--

A&K Wholesale

Murfreesboro, TN

615-867-5070