Creating a database and filling data using scripts

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

serge - 31 Mar 2005 02:08 GMT

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

Reply to this Message

Mark A - 31 Mar 2005 04:16 GMT

> I have all the scrips to create a database. I have a few questions:
>
[quoted text clipped - 25 lines]
>
> 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

Reply to this Message

Kenneth Downs - 31 Mar 2005 14:29 GMT

>> I have all the scrips to create a database. I have a few questions:
>>
[quoted text clipped - 46 lines]
> 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?

Signature

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

Reply to this Message

Mark A - 31 Mar 2005 15:37 GMT

> PMFJI, two questions:
>
[quoted text clipped - 9 lines]
> I am curious because we have always loaded data first using other tools on
> other platforms, does db2's tool offer some advantage here?

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.

Reply to this Message