Keeping DDL's in sync

[BarryS]

Hi All:

We're rolling out 3 DB2 (8.2 on LUW) platforms for branch offices, each
of these are going to have the same schema. These are all going to be
non-federated systems.

I need some advice / pointers on keeping the DDL's in sync with each other.

I'm going to have a development machine in my office that I first apply
any schema / stored-procedure changes to, then would like to roll out
these changes to the sites - probably on CD.

What methods or tools are available for keeping DDL's in sync - I'm an
old *nix hand, so I'm familar with shell scripting, but I don't quite
know the best way for applying just the diffs from one DDL version to
another.

Additionally like I said, I will need to keep the stored procedures in sync.

Any pointers would be greatly appreciated....
Thanks,
Barry

[Serge Rielau]

BarryS wrote:

Hi All:

We're rolling out 3 DB2 (8.2 on LUW) platforms for branch offices, each
of these are going to have the same schema. These are all going to be
non-federated systems.

I need some advice / pointers on keeping the DDL's in sync with each other.

I'm going to have a development machine in my office that I first apply
any schema / stored-procedure changes to, then would like to roll out
these changes to the sites - probably on CD.

What methods or tools are available for keeping DDL's in sync - I'm an
old *nix hand, so I'm familar with shell scripting, but I don't quite
know the best way for applying just the diffs from one DDL version to
another.

Additionally like I said, I will need to keep the stored procedures in
sync.

Any pointers would be greatly appreciated....
Thanks,
Barry

Barry,

Helpful tools are db2look and the ALTOBJ() in GENERATE mode.
ALTOBJ employs the services of DB2LK_DEP_OF() (not documented, but it's
a regular SQL Function stored in SYSCAT.ROUTINES).
None of them are exactly what you need, but they will prove helpful.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[kenfar]

You could manage this like any other set of distributed systems:
- using some version control software (cvs, etc)
- creating formal releases
- adding code to handle the migrations - the hard part
- create new tables
- copy & convert data from old tables to new tables
- drop old tables
- rename new table names to old names
- add all constraints, views, etc

Additionally, you could use tools like Embarcadero ErStudio, Sybase
Powerdesigner, or CA's ErWin: these tools allow easy forward & reverse
engineering and change management:
- generate a complete model from an existing schema
- populate a schema based upon a model
- determine differences between two models, two schemas, or a
schema and a model
- generate conversion code between two versions
So, these tools could help generate your migration code.

In generally, I'd see the use of tools like Embarcadero as an
enhancement to the manual method - not a replacement to it. This is
because it would still be great to have official releases, something
like shell scripts to run the db2 sql, etc, etc. These vendors also
have metadata-management capabilities that can be used for versioning
your model - but it can be a significant increase in model management
vs periodic snapshots to cvs (at least in my experience).

However, if you checked with these vendors (and others such as
compuware, bmc, etc), you might find that they have a complete change
migration solution that works for you. Your situation is a little
different though - most of these tools seem oriented towards a single
database. So at the end of the day it wouldn't surprise me if you end
up with a solution at least partially custom using shell scripts.

good luck,

ken

[Pierre Saint-Jacques]

You might want to look at the Satellite Administration Center function on
the Control Center:
Do a Google search on: db2 and satellite administration center
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"BarryS" <no****@really.thx> a écrit dans le message de
news:G5********************@giganews.com...

Hi All:

We're rolling out 3 DB2 (8.2 on LUW) platforms for branch offices, each
of these are going to have the same schema. These are all going to be
non-federated systems.

I need some advice / pointers on keeping the DDL's in sync with each other.
I'm going to have a development machine in my office that I first apply
any schema / stored-procedure changes to, then would like to roll out
these changes to the sites - probably on CD.

What methods or tools are available for keeping DDL's in sync - I'm an
old *nix hand, so I'm familar with shell scripting, but I don't quite
know the best way for applying just the diffs from one DDL version to
another.

Additionally like I said, I will need to keep the stored procedures in sync.
Any pointers would be greatly appreciated....
Thanks,
Barry

[Philip Nelson]

Serge Rielau wrote:

BarryS wrote:

Hi All:

We're rolling out 3 DB2 (8.2 on LUW) platforms for branch offices, each
of these are going to have the same schema. These are all going to be
non-federated systems.

I need some advice / pointers on keeping the DDL's in sync with each
other.

I'm going to have a development machine in my office that I first apply
any schema / stored-procedure changes to, then would like to roll out
these changes to the sites - probably on CD.

What methods or tools are available for keeping DDL's in sync - I'm an
old *nix hand, so I'm familar with shell scripting, but I don't quite
know the best way for applying just the diffs from one DDL version to
another.

Additionally like I said, I will need to keep the stored procedures in
sync.

Any pointers would be greatly appreciated....
Thanks,
Barry

Barry,

Helpful tools are db2look and the ALTOBJ() in GENERATE mode.
ALTOBJ employs the services of DB2LK_DEP_OF() (not documented, but it's
a regular SQL Function stored in SYSCAT.ROUTINES).
None of them are exactly what you need, but they will prove helpful.

Cheers
Serge

Serge,

Thanks for the pointer to DB2LK_DEP_OF, which could prove useful to me.

One of the problems with db2look is that it supplies the DDL in exact
creation timestamp order. This is an easy way of guaranteeing that it can
be run through without errors due to RI creation etc being out of order.
However it makes db2look output as it stands virtually impossible to use
for finding what has changed between invocations.

Hence I've been working on a Perl script called lookplus.pl which will sort
the objects into alphabetical name order. It's not yet finished, but is
getting there. This means that I can then use diff (or similar tools) as
the first stage of automating changes.

Phil

[EDWARD LIPSON]

If you get that to work, the pl script, I'm sure there are lots of
people who would like to look at it. More easy to use tools are also
welcome.
We are a big BMC shop. We use the Patrol Change Manager. Works somewhat
like it does on zOS and can tell you what needs to change in the target
database to make it look like the source. Their SmartDBA Cockpit tool
also has a text compare DDL option between two databases. SmartDBA
Cockpit used to be free.

PN> Hence I've been working on a Perl script called lookplus.pl which will sort
PN> the objects into alphabetical name order. It's not yet finished, but is
PN> getting there. This means that I can then use diff (or similar tools) as
PN> the first stage of automating changes.

Edward Lipson via Relaynet.org Moondog
ed***********@moondog.com el*****@bankofny.com
---
þ MM 1.1 #0361 þ

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[Serge Rielau]

And DeveloperWorks/DB2 will pay decent $$ for a good article. :-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bob [IBM]]

But But But would that not mean we are acknowledging that Phil may know what he is doing?
:) :) :) (Just kidding of course) ...

--

Bob
Engagement Specialist - DB2 Information Management Software - IBM Software Group
IBM Toronto Lab
[My comments are solely my own and are not meant to represent an official IBM position -
ask my cat!]
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3a*************@individual.net...

And DeveloperWorks/DB2 will pay decent $$ for a good article. :-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab