Intermittent delay on a table insert. -- MIDRANGE-L

Folks,

We have an intermittent bottleneck that I am trying to identify. The problem
appears with no unusual activity happening and no pattern as to time. The
scenario is this:

1. GUI application processes changes against DB2 tables, things are swift in
all cases up to the last item which is to insert a row (length 34) into a
table which has an insert/ after trigger.
        a. Intermittently this last step takes for-ever! and about 5 or more
minutes.
2. Trigger Program does two things, a. RTVJOBNBR and b. QSNDDTAQ then ends.
3. Back End process involves a daemon type program which monitors it's data
queue (there are three, Prod, Qual and Test) and has a sleep time of 4. 
        a. When an entry is found, the daemon does (Call "DISPATCHPGM" using
Trigger Data then loops back looking for another queue entry. 
-- History --  This scheme came about because we could not have the GUI
userid and password accessing the DB, they simply have no access! (govt'
thing!) SO this allowed us to set up the next part of the back end process
with the userid and passwords that we needed.
4. Dispatch program looks at the trigger data and determines what action to
take.
        a. All actions are initiated by a QCMDEXC which is a SBMJOB to the
(Prod, Qual or Test) environment, each with its own set of priorities.

We can look at the back end and see that it moves quite nicely into it's own
subsystem. We use the MAXJOBS value as a throttle to allow up to 4 instances
of any of the dispatched jobs.
Also we can see that the GUI moves nicely up to the point where the row is
added to the Trigger table (using ODBC) most time this is quick.

Now I have been asked if there could be a commit cycle that is causing the
problem, but from my point of view there is no DB activity until after the
jobs are dispatched. So my question is there any need (or does it even care)
in the QSNDDTAQ part about commits? Is there any other suggestions anyone
has as to this intermittent delay?

Oh, I should mention that the GUI is in contention for tables but not the
trigger table, the only contention on the Trigger table would be between the
users of the GUI and the dispatched programs. The dispatched programs are
DRDA cross platform 2 phase commit between the AS/400 and the Amdahl. The
Trigger Table is the only table that is updated on the AS/400, all other
tables are simply accessed to gather data to update the mainframe and in
those cases where multiple rows would result, we used data queues instead of
cursor processing to keep things as fast as possible. 

____________________________________________________________________________
_
Howard  Weatherly
        Systems Advisor
Computer Task Group, Inc.

howard.weatherly@dla.mil
howard_weatherly@ameritch.net
Howard.Weatherly@ctg.com