RE: SQL with connections to multiple databases

Activation groups can be weird. I've had similar issues before although it's been a few years.

If both programs run in the same activation group, Program B can mess with connections possibly needed by Program A.

Could re-compile Program A or B to run in a new activation group possibly. Actgrp(*NEW)

I'm also guessing programs are created with CRTSQLRPG or CRTSQLRPGI.

These two compiler parms could possibly have some effect:

Close SQL cursor . . . . . . . .CLOSQLCSR
RDB connect method . . . . . . .RDBCNNMTH (This one looks potentially suspect)

Keep in mind this is an educated guess without seeing your programs and without using RPG and embedded SQL daily 😊

Anyone else here using RPG and embedded SQL daily might be able to back up or debunk my response.......

Regards,


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

message: 1
date: Sun, 26 Nov 2017 15:51:29 -0500
from: "John R. Smith, Jr." <smith5646midrange@xxxxxxxxx>
subject: RE: SQL with connections to multiple databases

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 database.

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Richard Schoen
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 ?

Regards,


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 list.
-- Immediately before the SQL update I added a "EXEC SQL CONNECT".
According to %subst(SQLERRMC:1:18), the database connection is the local machine.
-- I added "EXEC SQL ROLLBACK", "EXEC SQL CONNECT RESET", and 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.