Re: Possible bug in SQL 'create or replace' wrt primary keys?

[Glenn Gundermann <glenn.gundermann@xxxxxxxxx>]

Hi David,

I'm wondering if it would work if you specified a name for your primary key
constraint, e.g. PK1_PRIMARY_KEY.

After running the first statement, my joblog has the following messages:

Constraint was added.
Table PK1 in GLENNLIB created but was not journaled.

After running the second statement, I see several messages in my joblog:

Constraint was removed.
1 constraint(s) removed from file Q_AT000000.
Constraint was added.
1 constraint(s) changed for file PK1.
Constraint cannot be changed for file PK1.
0 constraint(s) changed for file PK1.
1 constraint(s) changed for file PK1.
Constraint cannot be changed for file PK1.
0 constraint(s) changed for file PK1.
Table PK1 in GLENNLIB created but was not journaled.

Checking at the second level messages, it refers to the primary key name of
*GEN.


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (905) 486-1162 x 239
Cell: (416) 317-3144


On 19 July 2017 at 11:58, David Gibbs <david@xxxxxxxxxxxx> wrote:

> Folks:
>
> I observed some odd behavior and just want to confirm its a bug before we
> open a PMR.
>
> We ran the following SQL statement ...
>
> create or replace table yourlib/pk1 (f1 char (10), f2 char (10), f3 char
> (20), primary key (f1,f2));
>
> and did a DSPFD on it ... the key fields for the file showed up correctly.
>
> We then ran this statement ...
>
> create or replace table yourlib/pk1 (f1 char (10), f2 char (10), f3 char
> (20), primary key (f2,f1));
>
> (just reversing the order of the primary keys)
>
> When we did a DSPFD on the file, the key fields for the file showed up as
> F1 & F2 (in that order).
>
> Can someone run that same test and confirm the behavior?
>
> Thanks!
>
> david
>
> --
> IBM i on Power Systems: For when you can't afford to be out of business!
>
> I'm riding a metric century (100 km / 65 miles) in the American Diabetes
> Association's Tour de Cure to raise money for diabetes research, education,
> advocacy, and awareness.  You can make a tax deductible donation to my ride
> by visiting https://mideml.diabetessucks.net.  My goal is $6000 but any
> amount is appreciated.
>
> You can see where my donations come from by visiting my interactive
> donation map ... https://mideml.diabetessucks.net/map (it's a geeky
> thing).
>
> I may have diabetes, but diabetes doesn't have me!
> --
> 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.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD