Re: SQL problem with DDL and record id constraint

On 25-Apr-2014 13:15 -0500, Dale Janus wrote:

I am trying to add a field to a file defined in DDL using system I
nav (opsnav) V7R1m0.

Seems the topic is unrelated to RPG.

I navigate to the database and create the field. I uncheck NULLABLE
which changes default to data type. This adds the field. But when I
click ok on the table, I get an error, SQL State: 58004.

I reviewed the job log in opsnav and it looks like this:

Cause . . . . . : An access path was found that had the same
attributes and key fields as the member being created. Member PROFREC
file Q_AT000000 in library TEXASF shares the access path logically
owned by member PROFREC file Q_AT000000 in library TEXASF. Because
the access path was shared <<SNIP>>

The above message [though the message identifier was omitted] is purely *informational* and generally can be ignored.

Further tracing shows when we created the file in opsnav, we added a
record id field as record identity column with unique value (which
we are doing for all new files created with DDL) and go back and add
it as a key contraint. But we did not realize the system generated a
constraint name if we do not give it a name. Geez, we give it a
column name and a system name,

"... and a *file* name" was meant? The "it" as constraint should not required a "system name" to be specified anywhere. Surely naming the column against which the constraint is applied makes sense.

now we have to also give it a constraint name?

Naming [conceptual] objects such that they can be referred to in the future by the chosen name should not be much of a shocker; pretty mundane actually.

The CONSTRAINT, irrespective of PRIMARY, UNIQUE, or CHECK does *not* require a name to be specified. As already noted, the system [i.e. the integrated DB2 for i, or the DB2 for i SQL] will generate a name when name is not provided.

I have looked at some of the other new files we created and they also
have a system generated constraint name.

This is the first time we have tried to add a field to a DDL defined
file. We can add the field if we either use native SQL, or if we change
the new field to nullable, then change it back. But changing to null and
back gives us other problems.

I am pretty sure my response in the archives clarified the scenario:
http://archive.midrange.com/midrange-l/201404/msg00843.html

Can someone explain the need for a unique constraint name and why that
prevents me from adding a field?

Is that a name for a UNIQUE CONSTRAINT, or a unique name for a CONSTRAINT? No matter, an existing constraint name is not germane for the ADD COLUMN of an ALTER TABLE. If there is an issue that prevents "adding a field", there is likely something other than the [system generated] constraint name that is the origin for the issue.