RE: SQL with connections to multiple databases

I was waiting for a little more info before posting again. Sorry for the
delay.

What I have found is the customer's Program B is doing an ODBC connection to
the DB2. It is not to a different database as was originally thought. The
customer said it was a remote database connection and I didn't question it.
I suppose it is also possible that it is a JDBC connection not an ODBC.

I know I can write a program on Windows or Unix that connects to DB2 via
ODBC but is there an ODBC driver that runs on the iSeries to allow an
iSeries program to connect to the DB2 that it is running on?

There is also a QSQSRVR job in the middle of this mess and the library
containing File1 is not in its library list.

The JOB_START function executes its update statement which completes
normally. There is no reference to the QSQSRVR job.
Program B opens a connection with the QSQSRVR job and uses it for its stuff.
The JOB_END function executes it's update statement but it uses the QSQSRVR
job for its connection and since the library containing File1 is not in its
library list, it fails.

If we replace the call to Program B with a DLYJOB command, everything works
correctly because the job is not opening the connection to the QSQSRVR job.

If I hardcode the library in the update statement, it bypasses the QSQSRVR
job. I don't like hardcoding so I'm pushing against this as a solution.

I'm guessing at this point that Program B is not cleaning up after itself
and is keeping the QSQSRVR connection open but as I said a while back, we
have to be able to handle "bad coding/lack of cleanup" by our customers
(that is not intended to be a slam because I often let the system clean up
after me instead of adding code to do it myself manually).

How do I get the job to disconnect from the QSQSRVR job before the update in
JOB_END?



-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Darryl
Freinkel
Sent: Wednesday, November 29, 2017 4:04 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxx>
Subject: Re: SQL with connections to multiple databases

Is it possible that you have a busy system and each call made will be
handled by a different QZDASOINIT job. This could be a reason for the second
and subsequent jobs failing. The system may not have a persistent connection
and so you will need to do a new connect each call.

Darryl.

On Wed, Nov 29, 2017 at 12:15 PM, <dlclark@xxxxxxxxxxxxxxxx> wrote:

"RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx> wrote on 11/29/2017
11:16:41
AM:

Are you guys saying that I am fighting with a known problem and it
isn't just something that I'm doing wrong?

No. Our situation is different from yours. Our situation is
that there is an off-platform call from MS SQL Server to a stored
procedure on the IBM i. If only one of these calls are made and then
the session is disconnected then everything works fine. However, if
there are two of these calls back-to-back in the same session then the
second call was failing due to an IBM i system message which stated
that an object was no longer available to be referenced.

At that point my SQL standard error service procedure would
try to log the error but would fail with another IBM i system message
which stated that there was no database connected. If I would try to
connect the local database (via CONNECT RESET) that would also fail
with an IBM i system message stating that the database was in an

unconnectable state.

Thus Dieter's suggestion to use a separate activation group
for my SQL standard error service procedure seemed to be a good one --
because it should always have the local database connected by default.
But it still failed. IBM stated that the server job for such
connections (QZDASOINIT) would have some kind of a reset performed on
it between calls from MS SQL Server. It appeared to be that this
reset is what was messing me up -- but they had no suggestions on how to

resolve the problem.

I did have your situation under another circumstance, though.
In that situation I had to add the CONNECT RESET to my SQL standard
error service procedure and that took care of it. When I moved my SQL
standard error service procedure to its own activation group then I
was able to remove the CONNECT RESET and everything continued working
just fine in this situation that was similar to yours.

In short... Your situation should be resolved by either using
CONNECT RESET or by moving the failing program to its own activation
group. If neither of those suggestions resolve your situation, then
it seems to me that it is PMR time.


Sincerely,

Dave Clark
--
int.ext: 91078
direct: (937) 531-6378
home: (937) 751-3300

Winsupply Group Services
3110 Kettering Boulevard
Dayton, Ohio 45439 USA
(937) 294-5331




************************************************************
*********************************
This email message and any attachments is for use only by the named
addressee(s) and may contain confidential, privileged and/or
proprietary information. If you have received this message in error,
please immediately notify the sender and delete and destroy the
message and all copies. All unauthorized direct or indirect use or
disclosure of this message is strictly prohibited. No right to
confidentiality or privilege is waived or lost by any error in

transmission.

************************************************************
*********************************
--
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
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
Darryl Freinkel
--
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
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD