Re: SQLConnect - Connection to Relational Database Already Exists

On May 6, 2016, at 3:54 PM, Glenn Gundermann <glenn.gundermann@xxxxxxxxx> wrote:

If there is a problem due to a previous SQL CLI, then you might want to try
this to close things before attempting the next activity:

CALL PGM(QBRM/Q1AOLD) PARM('CLSSQLENV ')

We weren't performing what you are so this may be totally different. When
we do some BRMS activities and then call a program that tries to use
CPYTOIMPF, it failes. IBM says it is because both use SQL CLI. We are
supposed to sign off after performing the BRMS activities or call the above
mentioned program.



Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144


On 6 May 2016 at 15:33, CRPence <crpbottle@xxxxxxxxx> wrote:

On 06-May-2016 08:03 -0500, Ken Schwass wrote:

I am running the following command successfully.

Exec Sql
Select
SYSTOOLS.HTTPGETCLOB
(:Website, '')
INTO :vCLOB
From SYSIBM.SYSDUMMY1;

Later anytime I am using the SQLConnect API’s I get the following
error:

Connection to relational database D00D9B0R already exists.
SQLERROR. Checking Database handle...
SQLSTATE = 08002 Native Error = -842
Message = "Connection to relational database D00D9B0R already
exists.".
SQLERROR. Checking Database handle...
SQLSTATE = 08003 Native Error = -843
Message = "Connection to relational database ■■D00D9B0R does not
exist.”.

In a local connection, a resolution or preventive to problems similar to
those errors msg SQL0842 and msg SQL0843, likely would come from having
issued the following two consecutive SQL statements\requests:
DISCONNECT ALL
CONNECT RESET

If I don’t submit the HTTPGETCLOB my SQLConnect works fine.

I suspect the issue has something to do with a collision between the use
of the SQLCLI by both the prior work and the failing work. This is
something generally resolved by ensuring both features use the SQL CLI
Server Mode vs local; i.e. use the SQLSetEnvAttr() API to request the CLI
to operate in server-mode, thus redirecting the work to perform in a
QSQSRVR server job, rather than being run locally.

Is there something I need to do after my HTTPGETCLOB command?

Although defined as a Java External [to SQL] scalar function, the
system-supplied UDF HTTPGETCLOB [implemented with
SYSTOOLS.DB2RESTUDF:com.ibm.db2.rest.DB2UDFWrapper.httpGetClob] does
initiate some Activation Group (ACTGRP) [e.g. QJVADFTTS, QJVAJDBC,
QSQCLI]. Given the code for those was written to allow restarting without
errors after reclaiming the activation, thn the request to Reclaim
Activation Group (RCLACTGRP) of one or more of the ACTGRPs could
potentially assist. However, I have had little luck with doing so; the
system-code seemed always to fail miserably when re-invoked later, because
the code for the activations /assume/ some things will remain, despite the
reclaim having destroyed whatever those things are. So probably better to
look instead, to:

Here are three articles\documents that may assist, even if not seemingly
directly equivalent\identical to the failure scenario presented by the OP:

[http://www.ibm.com/support/docview.wss?uid=nas8N1015038]
Reference #: N1015038
Java Routines Containing JDBC Not Allowed in CLI Local Mode
"... the SQLConnect API call issued by the JDBC driver fails with message
SQL0842 - Connection to relational database already exists, which is an
expected condition due to the CLI restriction of one connection per RDB,
per job. ..."


[http://archive.midrange.com/midrange-l/200805/msg00124.html]
SQL CLI HELP! I've got 2 SQLConnect calls fighting with each other
"... if you already have one active connection, and try to create another
one, the second one will fail with SQL0842. Which sounds like it matches
your description of the problem [...]

The solution is to switch SQL CLI to "Server Mode". In server mode, the
SQL CLI submits your SQL statements to an SQL server job instead of running
them locally, and that makes it possible to have multiple connections.

There's an attribute SQL_ATTR_SERVER_MODE that you use to set this.
..."


[
http://developer.developer-works.com/article/32257527/SQL0842+if+connect+then+reconnect+to+remote+database
]
SQL0842 if connect then reconnect to remote database
"I have a CLI application that connects to a database, does a query, then
disconnects, then some time later may need to reconnect for another query.
I don't keep the DB connection around after I'm done with it. It works
fine if the database is *LOCAL, but if it's a remote database, the
reconnect fails with SQL0842 (-842) ..."

--
Regards, Chuck

--
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: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
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: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.