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