On 15-Jun-2011 09:25 , Alan Shore wrote:

Before I do my usual thing and forget, we are on release V5r4
Why can't I add a primary key to a file in QTEMP?
<<SNIP>>

The table is created no problem, however, for reasons TOO involved
I need to make the one field LCMPY into the primary key

When I run the following command in STRSQL
ALTER TABLE QTEMP/VACOMP add primary key (LCMPY)
I get the following error <ed: SQL7008>
VACOMP in QTEMP not valid for operation.
Using F1 on this error, the following is displayed
Message . . . . : VACOMP in QTEMP not valid for operation.
Cause . . . . . : The reason code is 10. Reason codes are:
rc10 -- A constraint or trigger is being added to an invalid type of
table, or the maximum number of triggers has been reached, or all
nodes of the distributed table are not at the same release level.

The file is NOT journalled
However, when I change everything so that the file is created in
another library, the add Primary key works


The constraint objects are not an external object type, and they are tracked only in the *DBXREF, not as an entry in the index object that implements the *LIB object; i.e. existence by name is tracked only in the system database cross-reference, as reflected in the SQL catalogs. Because the name QTEMP is not unique across the system, constraint names and relationships are not tracked for that library name, and thus they are prevented from being created in a library with that name. To be clear, that reply is not intended to suggest that there could not be a means implemented to allow constraints in QTEMP, e.g. as is done for SQL long name support in QTEMP, just that the easiest and clearest implementation simply precludes them.

In my experience the noted sqlcode 7008 is preceded by a clarifying diagnostic message CPD32B0 which in this case would presumably state as cause rc15 "15 - The file cannot be in the QTEMP library." with recovery "15 - Use a library other than the QTEMP library" which seems to sync with the experience described.

The following two doc links include text stating that [referential] constraints cannot be added "to files in the QTEMP library."
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/dbp/rbaforzahfrcf.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/dbp/rbaforzahfcod.htm

Regards, Chuck

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].