On 10/19/13 8:02 AM, Richard Schoen wrote:
I'm testing the SEQUENCE operation on a V5R4 system <<SNIP>>
Here's what I created:
CREATE SEQUENCE QGPL/RJSGLNEXT as decimal(7, 0) start with 27000
I run this from STRSQL and it works fine:
SELECT NEXT VALUE FOR RJSGLNEXT FROM SYSIBM/SYSDUMMY1
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
It seems that it's not resolving in my QZDASOINIT database job even
though the library list is set correctly.
Does "set correctly" imply something as-specified for the connection,
or as reported-by DSPJOB requested of the specific server job in which
the SQL executes and the error occurs?
Also it appears I can't qualify RJSGLNEXT with the library prefix.
System naming is irritating that way sometimes. Although I recalled
that issue being for unqualified names that use the PATH, vs the
unqualified names for something that uses the CURRENT SCHEMA. Doc links
added at end of reply.
The issue almost surely can be circumvented by using the SQL naming,
and issuing the request instead, as:
SELECT NEXT VALUE FOR QGPL.RJSGLNEXT FROM SYSIBM.SYSDUMMY1
IIRC on v7r1, due to an enhancement, the following /probably/ could
also circumvent, even while using System naming; someone would have to
test, as I have no access:
SELECT NEXT VALUE FOR QGPL.RJSGLNEXT FROM SYSIBM/SYSDUMMY1
Anyone seen this type of issue with SEQUENCE before ?
Only when I have made a mistake, one that was not easily noticed;
e.g. misspelling the sequence name, or incorrectly validating in what
library or what libraries are in the library list, or incorrect naming
option.
Kind of makes SEQUENCE unusable if it doesn't resolve correctly :)
The /default SCHEMA/ for the connection is presumably *LIBL, as was
implied by the error, if indeed *LIBL was part of the error data. And
the naming option is apparently System Naming both because of that, and
that the specification SYSIBM/SYSDUMMY1 was allowed; it would be a
SQL5016 error given SQL naming was in effect. I would suggest
requesting SELECT CURRENT SCHEMA FROM SYSIBM/SYSDUMMY1 to verify what
the SQL thinks is established for the schema, and if not *LIBL.
Given the description it would seem unlikely, but if not *LBL, then
issue the request to SET CURRENT SCHEMA DEFAULT to reset the current
schema to *LIBL. Then investigate what about your connection [string
etc.] that might have effected a CURRENT SCHEMA other than the DEFAULT
for *SYS naming, or a naming option other than expected.
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstch2nam.htm
_i Naming conventions i_
"...
_sequence-name_
A qualified or unqualified name that designates a sequence. The
qualified form of a sequence-name depends upon the naming option. For
SQL naming, the qualified form is a schema-name followed by a period (.)
and an SQL identifier. For system naming, the qualified form is a
schema-name followed by a slash (/) followed by an SQL identifier. 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).
The unqualified form of a sequence-name is an SQL identifier. The
unqualified form is implicitly qualified based on the rules specified in
_Qualification of unqualified object names_ <ed: link\snippet below>
A sequence-name can specify either the name of the sequence or the
system object name of the sequence.
..."
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstch2nam.htm#qualun
_i Qualification of unqualified object names i_
"Unqualified object names are implicitly qualified. The rules for
qualifying a name differ depending on the type of object that the name
identifies.
...
Unqualified alias, constraint, external program, index, nodegroup,
package, sequence, table, trigger, and view names are implicitly
qualified by the _default schema_ The default schema is specified as
follows:
...
# For dynamic SQL statements the default schema depends on whether or
not a default schema has been explicitly specified. The mechanism for
explicitly specifying this depends on the interface used to dynamically
prepare and execute SQL statements.
* If a default schema is not explicitly specified:
o For SQL naming, the default schema is the run-time
authorization identifier.
o For system naming, the default schema is the job library
list (*LIBL).
* The default schema is explicitly specified through the following
interfaces:
Table 3. Default Schema Interfaces
SQL Interface Specification
...
JDBC on a client using SQL Default Library in JDBC Setup ...
the IBM Toolbox for Java JDBC... info about IBM Toolbox for Java
..."
midrange.com
