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

DB2 Load into multiple tables using control centre

P: n/a

I am trying to use DB2 Control Centre (version 8.2) to load one flat
file into multiple tables. However, I don't see the options in Control
Centre that allows that.

Anyone knows how to do this?

DB2 Novice

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


P: n/a
DB2 Novice wrote:
I am trying to use DB2 Control Centre (version 8.2) to load one flat
file into multiple tables. However, I don't see the options in Control
Centre that allows that.

Anyone knows how to do this?

DB2 Novice

In one shot using load.. No.
How do you distribute the data. Do you parition vertically (column 1 - n
to T1, rest to T2) or horizontally (some rows to T1 some rows to T2).
There are multiple options:
For vertical partitioning the simplest approach would be to just use two
LOAD commands
For horizontal partitioning I woudl load the fiel into a staging table
and then either run two loads from cursor or, if you want only one scan:
BEGIN ATOMIC
FOR row AS SELECT * FROM staging DO
INSERT INTO T1 SELECT * FROM TABLE(VALUES(row.c1, ...)) AS S WHERE
<cond1>;
INSERT INTO T2 SELECT * FROM .... <cond2>;
END
%

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a

Thanks serge.
One more question on this Load or import. I believe the only that
really separate these tools is the speed. Either way, I believe my
accounting department people can still read the tables when I load or
import the data? I am just wondering
why load doesn't support one load into multiple tables like what DB2
import can do?

DB2 Novice

Nov 12 '05 #3

P: n/a
DB2 Novice wrote:
Thanks serge.
One more question on this Load or import. I believe the only that
really separate these tools is the speed. Either way, I believe my
accounting department people can still read the tables when I load or
import the data? I am just wondering
why load doesn't support one load into multiple tables like what DB2
import can do?

DB2 Novice

Uhm.. IMPORT does NOT support multiple targets in one shot. What I
posted was regular SQL.
Anyway the difference betwen IMPORT and LOAD is that IMPORT uses an SQL
interface ethat is it will fire trigger. And yes. It will be slower
There is an online LOAD capability and I think (?) similar options are
available for IMPORT. The way it works is that the loaded data remains
invisible until LOAD is completed and integrity of the data has been
verified by the SET INTEGRITY statement (to maintain e.g. RI, MQTs (aka
materialized/indexed views)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

P: n/a
Load data is invisible til it's issued with set integrity. Waht about
the existing data? AFAIK, table lock is acquired for Load operation.

Nov 12 '05 #5

P: n/a
Ora Dummy wrote:
Load data is invisible til it's issued with set integrity. Waht about
the existing data? AFAIK, table lock is acquired for Load operation.


Quick lookup for Online Load in teh Information center:
"Online table load

When loading data into a table in Version 8, the table space in which
the table resides will no longer be locked. Users have full read and
write access to all the tables in the table space, except for the table
being loaded. For the table being loaded, the existing data in the table
will be available for read access if the load is appending data to the
table.

These new load features significantly improve the availability of the
data and help customers deal with the maintenance of large data volumes
and shrinking maintenance windows. "
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

P: n/a
You cannot load into multiple tables using one command.
load into table one by one, specify the position in the file.
"DB2 Novice" <db*******@yahoo.com> wrote in message news:<11*********************@o13g2000cwo.googlegr oups.com>...
I am trying to use DB2 Control Centre (version 8.2) to load one flat
file into multiple tables. However, I don't see the options in Control
Centre that allows that.

Anyone knows how to do this?

DB2 Novice

Nov 12 '05 #7

P: n/a

Serge,

Thanks. That is for table lock for DB2 Load. However, those users who
are using
import utility is still not so fortunate to still read let alone write
while the table is being imported.
DB2 Novice

Nov 12 '05 #8

P: n/a
DB2 Novice wrote:
Serge,

Thanks. That is for table lock for DB2 Load. However, those users who
are using
import utility is still not so fortunate to still read let alone write
while the table is being imported.

Is that so? Check out online import:
http://publib.boulder.ibm.com/infoce...6f%72%74%22%20

Noe that all I do to find these thing sis type in the obvious search
patterns "online load" and "online import" into teh information center.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.