I can only answer 2 of those 3 questions.

Program B always works. It is when control is returned back to program A
and program A tries to update a record that we have a problem.

I don't know if program B closes the connections or now. We do not own
program B, the customer does and we can't force all of our customers to do
proper cleanup.

How would the activation affect this? I'm sure there is a ton that I do not
understand on how programs play within groups.

***Also, one other important piece of info that I omitted is that when
program A calls other "program B"s that do not do a remote connection,
everything works correctly...program A is able to update the row in the

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Richard
Sent: Friday, November 24, 2017 1:42 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: SQL with connections to multiple databases

Perhaps an activation group problem ?

Does program B work if called standalone ?

Are you sure is B closing all his connections ?


Richard Schoen
Director of Document Management
e. richard.schoen@xxxxxxxxxxxxxxx
p. 952.486.6802
w. helpsystems.com


message: 1
date: Wed, 22 Nov 2017 16:15:06 -0500
from: "John R. Smith, Jr." <smith5646midrange@xxxxxxxxx>
subject: RE: SQL with connections to multiple databases

I originally thought my problem was a remote connection not getting
closed/disconnected but apparently that is not the problem. I am very
confused on what is causing it or how to find it so I am reaching out for
ideas. Here is what I know.

-- I am consulting for a company that sells a product and one of their
customer is having a problem with it.
-- I do not have access to the customer's machine so I can't debug this.
-- The product's program (call it Program A) is calling a customer's program
(call it program B) which is doing a remote database connection.
-- When program B ends, program A calls a function in a service program that
attempts to update a record in the database on the local machine but fails
with SQL0204 - &1 in &2 type *&3 not found.
-- I have made several "patch versions" of both program A and the service
program and have found the following (via DSPJOB and other messages manually
and systematically sent to the joblog).
-- The library containing the file to be updated is in the library
-- Immediately before the SQL update I added a "EXEC SQL CONNECT".
According to %subst(SQLERRMC:1:18), the database connection is the local
another "EXEC SQL CONNECT". I added the rollback because I found on the web
that you can't do connection reset with pending updates so I added it just
in case that was correct. The additional "EXEC SQL CONNECT" and checking
the value again after the "EXEC SQL CONNECT RESET" was a "I'm getting
desperate so I'll try anything".

Can someone provide other debugging ideas, commands, or variables in program
A or the service program that I can look at to see what is going on? I am
completely lost.

This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].