Thanks Chuck AND everyone else that responded
In this respect, DDS has an advantage
I will need to rethink my logic
Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, June 15, 2011 2:18 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
