Re: Changing an SQL table (Best practices)

rob@xxxxxxxxx wrote:
<snip>

Ok, so maybe an ALTER TABLE does do something to the sequence number, 
notice the jump from 21 to 41.  But it sure didn't set it back to one and 
give me dup key message.

(Just to confuse and demoralize, I had this file in a different library. 
When I did the CRTDUPOBJ with data the sequence number jumped from 8 to 
21.  Maybe SQL uses sequence number jumps as a flag for file structure 
change?)

You question maybe as much about the "identity" generated value.  DB2
manual says the internal counter can be incremented for a variety of
reasons. Not guaranteed to be consecutive.  That it changed for your
"existing" rows suggests they were really re-inserted.

Interesting problem.  Probably a good reason to be careful with this
stuff.  It could trash your keys.

What are your "best practices" for storing SQL DDL source and modifying it 
in an environment without a change management package?

I've got views and stored procedures in source for RUNSQLSTM.  I usually
put a delete before the create and use ERRLVL(30) on the RUNSQLSTM.  Not
 the best and doesn't deal with tables as you are trying to address.
Without generated columns, an insert with subselect should work.


Keith