One could replace the prestart job entries in QUSRWRK that specify
QZDASOINIT with another little CL program that is compiled with
ACTGRP(ANAME). That little CL program would call QZDASOINIT. You might
have to fiddle with names a little. That'd put the whole thing in
non-default activation groups.
Remember that a named activation group is not shared among jobs.
Activation group is within the job space. RCLACTGRP ACTGRP(ANAME) will
only clean up the activation group within the job where it is run.
There is not necessarily a one-to-one correspondence between QZDASOINIT
jobs and connections in the shared pool. The connection manager on the
host will ATTEMPT to use the same QZDASOINIT job, but if it is busy,
it'll pick another. Also, QZDASOINIT jobs regularly die when their max
usage is exceded and are replaced by new ones within the same
connection.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark S. Waterbury
Sent: Thursday, May 14, 2009 3:39 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL stored procedures and activation groups via JDBC
Hi, Larry:
Answers below, in-line...
Thanks,
Mark
lloen@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx wrote:
...(snip)...
They use JDBC to access DB2 database tables and views on i5/OS from
client/server programs running on an application server. The database
requests are done via JDBC, sometimes select, update, insert, or delete
SQL statements and sometimes calls to stored procedures. Database access
is via a pool of QZDASOINIT jobs.
The stored procedures are ILE RPG programs created with CRTRPGMOD and
CRTPGM with ACTGRP(*CALLER) and they always set *INLR=*OFF, for
"performance" reasons.
Commitment control is being used throughout this application. At
present, all jobs run with CMTSCOPE(*JOB).
Could you explain more about this? This could be anywhere from an
irritant to a severe problem (as you have so far described it).
The software on the PC GUI (interactive) side is server
platform-independent and does not use the IBM Toolkit for Java. There
are no direct calls to programs on the server from interactive GUI jobs.
Each GUI screen (form) is treated as a "transaction" as is typical of
many client/server applications. That is, first the display is filled
with a select statement and then the connection is let go, then when the
user has made changes, a new connection is obtained from the connection
pool, the database changes are made, a commit is performed, and the
connection is released back to the pool. Sometimes the transaction
consists of writing records to a file and then calling a stored
procedure to do further processing. The application makes heavy use of
triggers that update other files. That is, an insert or update usually
"triggers" other data changes cascading down the line as trigger
programs are processed and the actual volume of data changed can be
quite high.
How many of these programs do you have? Do you know what the total
space, per QZDASOINIT job, is?
It is a vendor package. The i5/OS customer I am dealing with is an
end-user of this package. So, I do not have all of the details.
Are the QZDASOINIT jobs being reused over time, or are they growing in
numbers without bound?
As mentioned above, the QZDASOINIT jobs are managed via a "connection
pool" so there are a fixed number of these jobs, and they are re-used
over and over again.
Since the connection pool jobs are all in the default activation group,
the trigger programs and SQL cursors are never ended and the QZDASOINIT
jobs grow and grow. Also, one is never sure which records may still be
locked for whatever reason after stored procedure processing ends. Worst
of all is that the software company can not move to named activation
groups because a lot of the programs that are run from the interactive
jobs as a result of calls to stored procedures, are also part of batch
processes. If the modules were changed to use named activation groups,
calling them from interactive jobs would open new activation groups and
possibly mess up service program usage (service programs are all done
with ACTGRP(*CALLER)). Also, there are the implications of using
commitment control to consider.
What are the symptoms? Long term slowdown, increased paging, or
simply more and more disk consumed?
What I would predict is that you would see the storage growth for each
of these jobs take place to a certain point over time and then simply
"top off" and not grow any more.
Whether this did more than simply waste a bit of disk space would
depend on other factors.
Larry Loen
www.applicationperformancegroup.com
The crux of the issue seems to be, is there a way to configure
QZDADSOINIT jbos to run in an ILE activation group? How can they do
this?
For example, should they just put ALL of the i5/OS *PGMs and *SRVPGMs
into a single large named activation group, by specifying e.g.
ACTGRP(QILE) on every *PGM and *SRVPGM? At least, that way, presumably
they could then invoke a program to issue:
RCLACTGRP ACTGRP(QILE)
periodically, as needed...
Or, is there a way to force the QZDASOINIT jobs to terminate, and the
replace them with a new instance, in the connection pool, periodically?
Or any other recommendations?
Thanks.
Mark S .Waterbury
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
