Re: Intermittent delay on a table insert.

Could you possibly have contention issues with the data queue?

>>> Howard.Weatherly@dla.mil 02/05/03 07:30AM >>>
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 

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com 
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@midrange.com 
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

THIS MESSAGE IS INTENDED ONLY FOR THE USE OF THE INDIVIDUAL OR ENTITY TO WHICH 
IT IS ADDRESSED AND MAY CONTAIN INFORMATION THAT IS PRIVILEGED, CONFIDENTIAL 
AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW.  If the reader of this message 
is not the intended recipient, or the employee or agent responsible for 
delivering the message to the intended recipient, you are hereby notified that 
any dissemination, distribution, copying, downloading, storing or forwarding of 
this communication is prohibited.  If you have received this communication in 
error, please notify us immediately via email and delete the message from your 
computer files and/or data base.  Thank you.