472,325 Members | 1,544 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,325 software developers and data experts.

Keeping DDL's in sync

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
Nov 12 '05 #1
7 1950
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
Nov 12 '05 #2
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

Nov 12 '05 #3
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


Nov 12 '05 #4
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
Nov 12 '05 #5
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 =----
Nov 12 '05 #6
And DeveloperWorks/DB2 will pay decent $$ for a good article. :-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #7
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

Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Mike Jansen | last post by:
We're doing .NET development for PocketPC, BizTalk, and miscellaneous other things. One of the difficulties has been keeping references to...
1
by: kartik | last post by:
I open an fstream in read-only mode, read till the end, then try to sync() before seeking to position 0 & reading again. But the sync fails. I...
2
by: swoozie | last post by:
This may not be a MSSQL-specific question, but I wanted to ask it here first, in case there's a MSSQL and/or SourceSafe solution that will help. ...
9
by: Alfred Taylor | last post by:
I'm testing the waters of n-tier development and I ran into a scenario that I'm not sure what the best solution would be. I have a Company object...
5
by: Cindy H | last post by:
Hi I have a ddl that I have populated with a dataset. I have 2 items in the ddl. The first one is '3D' and the second one is 'Spot'. I have to...
4
by: mantrid | last post by:
Im using arrays generated from my records displayed in a table on my site to update the corresponding records in a mysql database ie on the web...
8
by: Michael.Guppenberger | last post by:
Hello everyone, I am currently trying to create a materialized query table which should be in-sync all the time. So my first attempt was to use...
0
by: Rune Jacobsen | last post by:
Hi all, In my project I have a control (described in an earlier post) which basically consists of three "subcontrols": One big area where the...
3
by: laredotornado | last post by:
Hi, I'm using PHP 4.4.4. I have two domains -- www.mydomain1.com and www.mydomain2.com. Both point to the same IP address. I have two pages...
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.