Re: Possible SQL Activation Group Issues

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 10/14/2016
02:05:07 PM:

. . .
. . .
. . .

We did not want to be qualifying our table and view names in
programs. We wanted the library list to control what is accessed. They
said that under the conditions specified that dynamic SQL will tend to

use

the library list where static SQL will tend to use only the current

schema

-- which defaults to the name of the current user. We also did not want
to be setting schema names in programs. Again, we wanted the library list

to control what is being accessed.

Sorry, but that's just plain bonkers. (That's using a real technical term,
eh?) So to keep things clear in the archives:

I've coded static SQL for years and never had a problem with it following
the library list. It all comes down to a couple parameters on the
CRTSQLxxxx command (the options are the same for COBOL and RPG). The
precompiler OPTION parameter defaults to *SYS. This says use system naming
conventions. Then there's the SQLPATH parameter. The default for this is
*NAMING for which the help text says:

The path used depends on the naming convention specified for the
Precompiler options (OPTION) parameter.

For *SYS naming, the path used is *LIBL, the current library list at
runtime.

It goes on to describe what happens for *SQL naming. But that should be
irrelevant to this discussion. The issue is what you discovered. You had
set the CLOSQLCSR parameter to *ENDACTGRP (actually, that's the default
value). This means: "SQL cursors are closed and SQL prepared statements
are implicitly discarded, and LOCK TABLE locks are released when the
activation group ends." Since your DAO code was running in the same
activation group, the prepared statement, etc. wasn't discarded.

I'm glad you got the problem figured out, but I'm stunned that IBM would
provide such weak and inaccurate advice--especially when you're getting it
from then in paid training.

Michael Quigley