Re: Problem with adding primary key to a table

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