AW: Problem with adding primary key to a table

Primary Key Constraints cannot be defined for tables generated within the
QTEMP.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Alan Shore
Gesendet: Wednesday, 15. June 2011 18:25
An: 'midrange-l@xxxxxxxxxxxx'
Betreff: Problem with adding primary key to a table

Good day all
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?

I have the following script in a member called QSQLSRC(CR8VACOMP)

create table qtemp/vacomp as
(with vacatp as
(SELECT a.pcat FROM prodfa/pricep a, prodfa/nopickp b
where
a.psuffx = b.suffix and
a.pskuno = b.skuno
GROUP BY a.pcat
ORDER BY a.pcat)
select c.Lcmpy from prodfa/srcmstp c, vacatp d
where c.Lcatcd = d.Pcat
group by c.Lcmpy
order by c.Lcmpy)
with data

That I run using the following command

RUNSQLSTM SRCFILE(QSQLSRC) SRCMBR(CR8VACOMP) COMMIT(*NONE)

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
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:
10 -- 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

As always, all responses MUCH appreciated





That I run with the following command


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