Re: Triggers, Activation groups, lock levels, and QZDASOINIT -- MIDRANGE-L

Scott,

I have run into problems using triggers in a database server job. I found 
that the database server job does a rclrsc and clears qtemp between 
calls.  It does not run a rclactgrp, which caused my problems.  With SQL, 
you will get the default activation group with *caller.  That caused us 
problems in triggers run as a result of SQL updates in regular jobs, but 
would have been OK in this case for database server jobs.

Another problem we ran into (in a case that sounds very similar to yours), 
was the inordinate amount of overhead involved in record-by-record updates 
for a large set of records.  It turned out that it was about 50 times faster to 
restructure the application and reduce the reliance on ODBC to a single 
stored procedure call. All of this pointed out very clearly that ODBC is not 
good at record-by-record updates.

David Morris

>>> Scott.Lindstrom@zenith.com 03/15/01 01:04PM >>>

I am trying to get a handle on how exactly commitment control will operate
on trigger programs initiated by inserts to AS/400 tables via an ODBC
connection.  The remote system (Powerbuilder apps on a PC, I believe)
connect to the AS/400 and cause QZDASOINIT jobs to start.  I see their
commitment control status is:

Job:           QZDASOINIT          User:           QUSER

Commitment definition . . . . . . :      *DFTACTGRP
Activation group  . . . . . . . . :       2
Default lock level  . . . . . . . :      *CS

We have database triggers defined on some of the files that these jobs will
insert records to.  If I understand correctly, the trigger programs will
run inside these jobs.  Based on all the research I have done, we have set
the RPGLE programs to run DFTACTGRP(*NO) ACTGRP(*CALLER).

Based on how these QZDASOINIT jobs run, should we be starting commitment
control using CMTSCOPE(*JOB) or (*ACTGRP)?  I'm not really looking for
'our' work to run any different than the work done by the program on the
'other side'.

It seems I'd like to run our triggers as lock level *CHG, but the
QZDASOINIT job runs *CS.  Is this a problem?

The remote task is also inserting records into an Oracle database on a Unix
platform in addition to inserting records into the AS/400 tables.  Am I
correct that an AS/400 trigger program abnormally terminating, or issuing a
ROLLBACK, or a communication failure will cause an automatic rollback to
occur on *both* systems back to their last commit point?  Therefore, the
programmers on the 'other side' need to be very careful on what they commit
and when to their own database in case we have a failure on our side.

Any experiences/insight (either thru the list or privately) would be
greatly appreciated.

Scott Lindstrom
Zenith Electronics
scott.lindstrom@zenith.com 


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---