Re: Using SQL SEQUENCE for Next Number Operations -- MIDRANGE-L

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:

<<SNIP>>

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
<<SNIP>>

Here's the exact text:

[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:].
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstch2nam.htm

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

In this case WEBDOCS is the user name and it does not have a
library.

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

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 expressly documented:
"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)."