By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,291 Members | 3,096 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,291 IT Pros & Developers. It's quick & easy.

Creating a database and filling data using scripts

P: n/a
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
4- Indexes
5- Constraints
6- Triggers
7- Functions
8- Procedures

Is this order of running the scripts good?

2- We have a SQL 2000 "model" database that contains the objects
and the tables have the data. First, is the term "model" the right term?
Is this a database term?

What is the best way to "export" the data from these tables and
when creating the DB2 8.2 database, insert the data in the tables?

Is there a way to export, create BCP files and easily import them to
the DB2 tables?

Thank you

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"serge" <se****@nospam.ehmail.com> wrote in message
news:c4********************@news20.bellglobal.com. ..
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
4- Indexes
5- Constraints
6- Triggers
7- Functions
8- Procedures

Is this order of running the scripts good?

2- We have a SQL 2000 "model" database that contains the objects
and the tables have the data. First, is the term "model" the right term?
Is this a database term?

What is the best way to "export" the data from these tables and
when creating the DB2 8.2 database, insert the data in the tables?

Is there a way to export, create BCP files and easily import them to
the DB2 tables?

Thank you

Only use db2 -td@ -f filename.sql -z output.txt
if your SQL statement separator is something other than semicolon (;).
Normally you would use:
db2 -tvf filename.sql -z output.txt

The only exception is for triggers, functions, and procedures because they
probably use the semicolon within the syntax of a single object, so you put
a @ at the very end of each object to change the separator character:
db2 -td@ -f filename.sql -z output.txt

I would normally use the following order:

1- Create the database, bufferpools, and tablespaces
2- Tables
3- Constraints
4- Indexes
5- Triggers
6- Functions
7- Procedures
8- Insert the data

Nov 12 '05 #2

P: n/a
Mark A wrote:
"serge" <se****@nospam.ehmail.com> wrote in message
news:c4********************@news20.bellglobal.com. ..
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
4- Indexes
5- Constraints
6- Triggers
7- Functions
8- Procedures

Is this order of running the scripts good?

2- We have a SQL 2000 "model" database that contains the objects
and the tables have the data. First, is the term "model" the right term?
Is this a database term?

What is the best way to "export" the data from these tables and
when creating the DB2 8.2 database, insert the data in the tables?

Is there a way to export, create BCP files and easily import them to
the DB2 tables?

Thank you

Only use db2 -td@ -f filename.sql -z output.txt
if your SQL statement separator is something other than semicolon (;).
Normally you would use:
db2 -tvf filename.sql -z output.txt

The only exception is for triggers, functions, and procedures because they
probably use the semicolon within the syntax of a single object, so you
put a @ at the very end of each object to change the separator character:
db2 -td@ -f filename.sql -z output.txt

I would normally use the following order:

1- Create the database, bufferpools, and tablespaces
2- Tables
3- Constraints
4- Indexes
5- Triggers
6- Functions
7- Procedures
8- Insert the data


PMFJI, two questions:

1. Is the data known to be valid before loading?
2. Does the db2 tool check constraints while loading?

If answer to both is yes, the sequence in the OP will be much faster than
the sequence above. The sequence above causes indexes to be built as the
data loads and constraints to be checked for data that is known to be good.
The OP's sequence gets the data in and then performs those actions in
single discreet shots.

I am curious because we have always loaded data first using other tools on
other platforms, does db2's tool offer some advantage here?

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)
Nov 12 '05 #3

P: n/a
> PMFJI, two questions:

1. Is the data known to be valid before loading?
2. Does the db2 tool check constraints while loading?

If answer to both is yes, the sequence in the OP will be much faster than
the sequence above. The sequence above causes indexes to be built as the
data loads and constraints to be checked for data that is known to be good. The OP's sequence gets the data in and then performs those actions in
single discreet shots.

I am curious because we have always loaded data first using other tools on
other platforms, does db2's tool offer some advantage here?

--
Kenneth Downs


If you have a large data warehouse, adding the indexes afterward probably
would be a lot faster. But it partly depends on whether the SP's, functions,
and triggers need any of indexes to perform well. Some of these could be
invoked during the insert.

I would never assume that the data is 100% valid or that all constraints,
triggers, etc were accurately converted. Doing the inserts last would be a
little easier to pinpoint which rows had errors.

A reorg and runstants is in order after the final step.
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.