RE: Changing an SQL table (Best practices)

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, January 11, 2007 3:59 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Changing an SQL table (Best practices)


If I run this again with a simple RUNSQLSTM it will abort 
because the file 
already exists.  Best comparison to DDS is using CHGPF versus CRTPF.

Outside of a CMS, I always add a DROP TABLE statement to the beginning
of the source.

Other questions involved what to do if someone ran a ALTER 
TABLE statement 
to add the field FOOBAR?  We pondered doing a DSPFD and 
copying pasting 
that SQL statement.  Using iNav to retrieve the sql statement 
used, etc. 
Another alternative suggested was to have the CREATE TABLE 
followed by the 
ALTER TABLE all in the same source. 

Outside of a CMS...it's a headache :-)

I just modified my source from the original to the modified.  
And ran the 
whole process through my Change Management software (SoftLanding's 
Turnover).  Worked fine.  The data that was in the file 
stayed there, etc. 
 The only anomaly was that the sequence number got reset.  (I need to 
report that to Turnover.)  But, other than the sequence number, I was 
quite impressed.

I ran into the same anomaly using Aldon.

Reply I got from Aldon/IBM was "sorry that's a limitation of CPYF".

In my case, most of my identities are GENERATE ALWAYS.  But CPYF doesn't
regenerate an identity column value, which is good I can't think of a
place where I'd really want it to.  Unfortunately, CPYF also doesn't
update the "last used" identity value.

I put together a RESETTBLID command that updates the "last used" value
based upon the current max value.  I run it after promotion.  (Manually
right now, I keep meaning to tie it into Aldon so it will get run
automatically ;-)

Now, I don't know how Turnover did it underneath the covers.  
I suspect 
that the did NOT do a comparison and do an ALTER TABLE 
because if they had 
done so I suspect that the sequence number would not have 
gotten reset. Or 
would it? 

Actually I know Aldon's added a feature where it does in fact do the
Alter Table/CHGPF instead of a recreate when possible.

But I've never look at the effect of Alter Table on an identity column.

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

Don't do it outside the CMS.  <grin>

Actually, I end up not storing it at all.

I use iNav to generate the current source, make my change, add the drop
table line and recreate it.

Of course, this wipes out the data in the table.  So if that's a
problem, I'll either use alter table or manually save off and reload the
data.

While you had to keep DDS source around to recreate/change a PF.  You
don't need to keep the original DDL around. So if I don't have a CMS to
manage it for me, I don't keep it around.

HTH,

Charles