On 10/20/13 1:52 PM, Richard Schoen wrote:
On 10/20/13 9:17 AM, CRPence wrote:
On 10/19/13 8:02 AM, Richard Schoen wrote:
Here's the exact text:
When I run the same SELECT statement via JT400, I get an error
stating that the data area RJSGLNEXT is not found in library
list, even though it exists and QGPL is in the library list.
The specific error text and error message data\details would be
helpful, to be entirely clear about what transpired. Presumably?:
SQL0204 "RJSGLNEXT in *LIBL type *DTAARA not found." sqlcode -204
[SQL0204] RJSGLNEXT in WEBDOCS type *DTAARA not found.
OK... So nothing to do with the Library List.
The /default Schema/ in SQL naming is the Authorization Identifier,
and that value is used to qualified any unqualified SEQUENCE references
[per the doc link I included in my prior reply; but snipped, so included
again here for ease from archives:].
It appears that it's looking for the data area in a non-existent
library which would probably be true based on what you wrote in your
In this case WEBDOCS is the user name and it does not have a
The User Profile name is the default Authorization Id for the SQL.
Thus the unqualified reference to the SEQUENCE was implicitly qualified
as WEBDOCS.RJSGLNEXT as the correct effect for use of the SQL naming option.
Interestingly I was testing with *SYS naming in STRSQL and *SQL
naming in the DB driver and had tried qualifying QGPL/RJSGLNEXT like
this in STRSQL:
SELECT NEXT VALUE FOR QGPL/RJSGLNEXT FROM SYSIBM/SYSDUMMY1
And it STRSQL didn't like the syntax qualifying RJSGLNEXT.
Yes. :-( That was the /irritating/ effect for *SYS vs *SQL naming;
i.e. library-qualification of references is not allowed for everything,
as I had alluded in my prior reply. However I had, apparently
incorrectly, recalled that effect only being associated with unqualified
references that are located via the PATH rather than those located via
the CURRENT SCHEMA.
The NEXT VALUE FOR clause, I suppose is quite similar to unqualified
PATH-located references, so whatever is the origin for the system-naming
qualifier [the slash] being restricted in that context, is presumably
the same issue for the SQL in that context.? For example, only the
latter of the two is allowed in their respective naming-option [as
inferred from the qualified name specified on the FROM-clause]:
select qsys2/hex('A') from qsys2/qsqptabl
select qsys2.hex('A') from qsys2.qsqptabl
I hadn't tried *SQL naming in STRSQL so I changed STRSQL to *SQL
naming and actually was able to re-create the exact error without the
qualification on RJSGLNEXT, but the qualification does work now in
*SQL naming mode when I do:
SELECT NEXT VALUE FOR QGPL.RJSGLNEXT FROM SYSIBM.SYSDUMMY1
I guess I'll go with that
Another option is to SET CURRENT SCHEMA QGPL or the equivalent effect
established via the database connection [string]; i.e. the unqualified
reference to the SEQUENCE would be implicitly qualified with QGPL
instead of the authorization-id.
But... If using *LIBL support is generally desirable for unqualified
references, then better to use the System Naming option... and do *not*
also set a current schema. Or if ever setting a current schema, being
sure that, prior to the need, issue the SET CURRENT SCHEMA DEFAULT to
ensure the value is reset to *LIBL.
but you would think *SYS and *SQL naming would work consistently the
Those naming options effect very different results for very important
reasons. But... Specifically with regard to the following restriction
[quoted snippet directly from the docs], I agree that the two naming
options should "work consistently"; i.e. IMO both qualified forms should
be allowed, rather than sometimes [inconsistently] only allowing the SQL
naming form of a qualified reference. However, for whatever is the
reason that the SQL does not support it, at least the restriction is
"For system naming, a sequence-name cannot be qualified when used
in a NEXT VALUE or PREVIOUS VALUE expression (the qualified form is only
allowed in SQL schema statements)."