How to automate creating a database, tables, index, procedures...?

[serge]

What is the best way to run one command and have a database be created

and sql scripts run on it to create the tables, indexes, triggers,
procedures, etc.?

Is there an existing tool free or commercial to automate this?

Thank you

[Rhino]

"serge" <se****@nospam. ehmail.com> wrote in message
news:DH******** ************@ne ws20.bellglobal .com...

What is the best way to run one command and have a database be created

and sql scripts run on it to create the tables, indexes, triggers,
procedures, etc.?
Think about it: how could such a tool possibly work? How is any tool
supposed to know what columns you want in your tables, what primary keys
those tables should have, what constraints are necessary, what procedures
you want?

While mind-reading software may be possible some day, it isn't here yet. In
the meantime, development of databases will continue to be a combination of
humans who figure out the design and content of the database and software
that builds each piece.
Is there an existing tool free or commercial to automate this?

The tools within DB2 do a pretty good job of generating the parts of a
database once the humans have figured out what they want to build. The
various commands that have to be executed to create the database can all be
combined into scripts which the human can execute to build the database.
Putting the various commands into scripts also enables you to easily create
duplicate versions of databases for different test environments. But nothing
is going to eliminate the need for you to first decide what ingredients go
into your database; no tool can figure out for itself what columns, keys,
constraints, triggers, etc. you need without significant human intervention.

Rhino

[serge]

> Think about it: how could such a tool possibly work? How is any tool

supposed to know what columns you want in your tables, what primary keys
those tables should have, what constraints are necessary, what procedures
you want?

I already have the scripts. I need to figure out if there are existing tools
that
can help me or give me some cool features to do what i want.

So far what i've learned is to create one dos batch file and paste lines
something
like:

db2 -td@ filename.sql

I might be wrong with "-td@" but it was something like that.

[Mark A]

> I already have the scripts. I need to figure out if there are existing
tools

that
can help me or give me some cool features to do what i want.

So far what i've learned is to create one dos batch file and paste lines
something
like:

db2 -td@ filename.sql

I might be wrong with "-td@" but it was something like that.

Use:
db2 -tvf filename.sql
this assumes that your SQL statement delimiters are ';' (semicolons)

If you are creating a procedure which has embedded semicolons for each line,
you need:
db2 -td@ -f filename.sql
and put a '@' at the end of each procedure.

[Kenneth Downs]

Rhino wrote:

"serge" <se****@nospam. ehmail.com> wrote in message
news:DH******** ************@ne ws20.bellglobal .com...

What is the best way to run one command and have a database be created

and sql scripts run on it to create the tables, indexes, triggers,
procedures, etc.?
Think about it: how could such a tool possibly work? How is any tool
supposed to know what columns you want in your tables, what primary keys
those tables should have, what constraints are necessary, what procedures
you want?

Its rather simple. A specification contains the description of the
database, including columns, tables, keys, etc. A builder loads that
specification into tables, pulls the current state from the server into
another set of tables, diffs them, and generates and executes DDL to
reconcile the differences.

A very very big design decision comes in handling stored procedures and
other code fragments. Bringing outside code into the picture turns your
builder into a version control system. I've done this in a former life and
it is no fun. A better system is to provide dictionary components that
allow for arbitrary complexity to be built in without needing outside code,
but here the barriers are psychological rather than technical, and they are
quite formidable.

While mind-reading software may be possible some day, it isn't here yet.
In the meantime, development of databases will continue to be a
combination of humans who figure out the design and content of the
database and software that builds each piece.

I would zoom in on the word "build" and replace it with "assemble." Once an
analyst has determined the shape of a database and application, there are
many cases where the entire thing is composed of patterns everyone has seen
before. Therefore the process of generating the finished product should be
automatic, untouched by human hands. The spec goes into the black box, the
app comes out. The black box pieces together code snippets required for
elements in the spec.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec )ure(Dat)a(.com )

[Larry]

Can anything in the Rational portfolio assist here?

http://www-306.ibm.com/software/rati...ory/index.html

Kenneth Downs wrote:

Rhino wrote:

"serge" <se****@nospam. ehmail.com> wrote in message
news:DH****** **************@ news20.bellglob al.com...

What is the best way to run one command and have a database be created

and sql scripts run on it to create the tables, indexes, triggers,
procedures , etc.?

Think about it: how could such a tool possibly work? How is any tool
supposed to know what columns you want in your tables, what primary keys
those tables should have, what constraints are necessary, what procedures
you want?

Its rather simple. A specification contains the description of the
database, including columns, tables, keys, etc. A builder loads that
specification into tables, pulls the current state from the server into
another set of tables, diffs them, and generates and executes DDL to
reconcile the differences.

A very very big design decision comes in handling stored procedures and
other code fragments. Bringing outside code into the picture turns your
builder into a version control system. I've done this in a former life and
it is no fun. A better system is to provide dictionary components that
allow for arbitrary complexity to be built in without needing outside code,
but here the barriers are psychological rather than technical, and they are
quite formidable.

While mind-reading software may be possible some day, it isn't here yet.
In the meantime, development of databases will continue to be a
combination of humans who figure out the design and content of the
database and software that builds each piece.

I would zoom in on the word "build" and replace it with "assemble." Once an
analyst has determined the shape of a database and application, there are
many cases where the entire thing is composed of patterns everyone has seen
before. Therefore the process of generating the finished product should be
automatic, untouched by human hands. The spec goes into the black box, the
app comes out. The black box pieces together code snippets required for
elements in the spec.