RE: Changing an SQL table (Best practices)

Rob,

We keep our SQL statements in a source file just like RPG or CL.  We
also have Aldon so it handles the promotions.

Like Charles, I add a drop table at the beginning but that's mostly for
development purposes as Aldon does that for me.

Rick

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

The other night I presented at my LUG on DDL.  One of the
questions was that if I do a

CREATE TABLE TESTTROY3
(MYKEY INT NOT NULL WITH DEFAULT as identity,  MYDATA CHAR (5
) NOT NULL WITH DEFAULT, PRIMARY KEY (MYKEY))

and store this in a source member, I can then create it with
RUNSQLSTM or some such animal.
All well and good so far.
Now let's say I want to change it to:
CREATE TABLE TESTTROY3
(MYKEY INT NOT NULL WITH DEFAULT as identity,  MYDATA CHAR (5
) NOT NULL WITH DEFAULT,  FOOBAR CHAR (8) NOT NULL WITH
DEFAULT, PRIMARY KEY (MYKEY))

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.
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.

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.

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?
INSERT INTO ROB/TESTTROY3 (MYDATA, FOOBAR) VALUES('x', 'y')
ALTER TABLE ROB/TESTTROY3 ADD COLUMN BUBBA CHAR (1 ) NOT NULL
WITH DEFAULT INSERT INTO ROB/TESTTROY3 (MYDATA, FOOBAR)
VALUES('9', '9') INSERT INTO ROB/TESTTROY3 (MYDATA, FOOBAR)
VALUES('8', '8') select * from rob/testtroy3
MYKEY   MYDATA  FOOBAR    BUBBA
    1   X
...
    8   ww      ee
   21   x       y
   41   9       9
   42   8       8
  End of data  **
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?)

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

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

--
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.

Privileged and Confidential.  This e-mail, and any attachments there to, is 
intended only for use by the addressee(s) named herein and may contain 
privileged or confidential information.  If you have received this e-mail in 
error, please notify me immediately by a return e-mail and delete this e-mail.  
You are hereby notified that any dissemination, distribution or copying of this 
e-mail and/or any attachments thereto, is strictly prohibited.