Re: (SQL) Identity Crisis

On 05 Apr 2013 13:08, Anderson, Kurt wrote:

Unfortunately it seems like what happened to me is that when the
CPYF ran (which carried over the originating identity values

Correct. The CPYF copies the exact values from the same name field when using FMTOPT(*NONE) or FMTOPT(*MAP) or FMTOPT(*MAP *DROP). Only when there is no matching field name in the FROMFILE(), to the IDENTITY column in the TOFILE(), will the values be GENERATED for that IDENTITY column in the TOFILE().

- must be using OVERRIDING SYSTEM VALUE under the covers), the
destination table did not recognize the inserted identity values in
regard to establishing what the next identity should be.

In its effect, the CPYF could be described as having used OVERRIDING SYSTEM VALUE. However the CPYF utility is not implemented using the SQL, so the actual means by which that override happens is different than that clause for the INSERT statement of the SQL.

<<SNIP>>

Currently we only have 1 file in production with an identity column,
and thankfully this files only exists in one location. I'm actually
working on a project to redesign some of our files and I was adding
identity columns to them (these are files that would go into 50+
libraries). The problem is that I can't envision (currently) how
we'd work ALTER TABLE into our file conversion process. I've been
to numerous sessions on using SQL to create tables/indexes at
conferences, and unless it went over my head, I don't ever recall
being told "Don't recreate your files, only alter them."

I think that depends on the means to deliver the changes. Internally with a specific application, the the original DDL source is updated with the changes, but the actual change is implemented via ALTER TABLE. When the changes are being delivered as part of a software package, then determining how best to deliver those changes is more complicated; only part of an install, as part of the run-time, as part of a post-install upgrade callable feature, or other means. In conjunction with delivery of changes to the file must be compatible with how the changes to the associated code are delivered, to ensure any dependencies are met. A lot to think about.

What I'm gathering from the use of Identity is that going forward we
can't ever recreate the file without manual intervention. I'm not
making any decisions right now, I need time to think this over, but
I'm leaning toward "it's easy enough to set the next available
number on the fly programmatically."

Whatever method is currently being used to update the file is already doing the work, perhaps generically with some process like move, create, copy, grant, etc. As such, a special case can be added to generically handle any old file with an IDENTITY, to effect the ALTER ... RESTART WITH. Of course changing to use ALTER TABLE instead of the programmed process to achieve the same starts to look better... plus the /alter/ interface has special handling for especially large files to help prevent possibly hitting storage limits that could occur at the moment when two copies of all of the data resides on the system when using something like move+create+copy.

In our DDS, when we'd modify a source we'd simply mod-mark it and
add a maintenance comment detailing the change (like we do for any
other changed program). That's what I was doing in SQL, if I added a
field, I'd add it to the CREATE TABLE (and to the LABEL ON COLUMN)
statements. That I'm aware of, this has had no adverse effect on our
other SQL tables which do not use identity columns. How do people
generally go about tracking changes to a SQL table in their source? I
can't really fathom having a separate source for each change (given
the 10 character member name limitation).

I would update the creation source with comments for additions, both with DDL and DDS; just as already being done. That up-to-date source would still be used for /new/ creations, but updates to existing versions of the file could be updated with CHGPF SRCFILE(named) or ALTER TABLE instead of using another program that attempts to effect what those features accomplish [quite conveniently replacing the need for coding it yourself]. For that alter activity, either a program [or a script] could implement the ALTER, which in most cases is effectively just the /additions/ to the source member that holds the CREATE.

<<SNIP>>