473,583 Members | 2,797 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
Nov 12 '05 #1
5 1824

"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

Nov 12 '05 #2
> 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.
Nov 12 '05 #3
> 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.
Nov 12 '05 #4
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 )
Nov 12 '05 #5
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.

Nov 12 '05 #6

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

Similar topics

6
6853
by: Dim St Thomas | last post by:
I am a developer working on a database client program. I am testing this program on a Windows XP machine (1.5 GHz AMD chip, 480 Mb RAM, 60 Gb disk) This machine has Oracle 9.2.0.1.0 and RedBrick database software installed. I am testing the software by creating small test databases. If I create an Oracle database using the Database...
3
2772
by: Robin Tucker | last post by:
Hi there, I have a database on my test machine that will need to be installed on users machines. I would like to create the database with the given schema on the users machine and also with some suitable default values in the tables. I note that although I can script the schema so that re-creating the structure of the database is simple...
10
2149
by: John A Fotheringham | last post by:
I'm trying to write a procedure that having created a new database, will then create within that new database all the tables and procedures that go with it. In doing this I'm hitting the problem that you can't issue a USE command within a procedure. So my question is either - how do I get around this?
2
28557
by: Kums | last post by:
What is the maximum permissible size of a database? Is there any limitation. What is the maximum # of tablespace's allowed in a database? Thanks for your response.
3
2387
by: serge | last post by:
I have all the scrips to create a database. I have a few questions: 1- I am creating a batch file that it will call many lines like: db2 -td@ -f filename.sql -z output.txt The order i am using is: 1- Create the database 2- Tables 3- Insert the data
8
4315
by: Brian S. Smith | last post by:
Hi gang, Please help. I've been through the Access help, searched the Web, and I can't seem to get a straight answer. As the Subject line suggests, I want to run a fairly simple VB/Access Sub Function/Module that creates relationships for my tables. The problem is that I need to provide for some tables that may have > 32 relationships...
1
6314
by: Shelby | last post by:
Problem: My company generates its own data export from a propietary database. These (free) tables can be read in C#.NET using a Visual FoxPro driver (vfpoledb). I can read each of the six tables into its own datatable, modify them, and add them to a dataset. It take approximately 15 minutes to pass that dataset to Crystal Reports (45...
9
9301
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger, with the exception of the table name. I could manually change the table name in the trigger and create it, over and over, but I'd like to automate...
1
1209
by: Grey Alien | last post by:
I have created a database (*.df) file for use with my website. I have files containing DDL and DDM for creating the db schema and stored procedures respectively. However, I have not (as yet) founs out how to "import" the SQL statements into the datbase, to create the required database objects. I would appreciate any help/info that will help me...
0
7829
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8330
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8198
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5705
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5379
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3824
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3850
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1438
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1162
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.