Prev: db2look and trusted context
Next: 6 Find Your Shoe Size - Step by Step Guide to Measuring Your Feet
From: Damir on 7 Apr 2010 09:51
Hi all,
is there a way to copy/move a table (one or more) between two (federated)
DB2 databases, but without having to build the indexes on the target
database (to avoid table scans and the log space consumption)?
The db2move tool builds indexes during import/load, as well as simple
export/import/load operations (as well as LOAD FROM CURSOR + SET INTEGRITY),
so all these are not eligible.
I was thinking of something like the new ADMIN_MOVE_TABLE UDF, together with
the COPY_WITH_INDEXES option, that could work over a federation server.
For example, that something could be told to copy/move a table into a
nickname (a remote table, already existing, or not).
It would be perfectly OK if the source table was completely inaccessible
during the copy/move operation (and the target table completely overwritten,
or created).

Could there be such a way, or am I talking nonsense?

Regards,
Damir



From: Serge Rielau on 7 Apr 2010 12:41
On 4/7/2010 9:51 AM, Damir wrote:
> Hi all,
> is there a way to copy/move a table (one or more) between two (federated)
> DB2 databases, but without having to build the indexes on the target
> database (to avoid table scans and the log space consumption)?
> The db2move tool builds indexes during import/load, as well as simple
> export/import/load operations (as well as LOAD FROM CURSOR + SET INTEGRITY),
> so all these are not eligible.
> I was thinking of something like the new ADMIN_MOVE_TABLE UDF, together with
> the COPY_WITH_INDEXES option, that could work over a federation server.
> For example, that something could be told to copy/move a table into a
> nickname (a remote table, already existing, or not).
> It would be perfectly OK if the source table was completely inaccessible
> during the copy/move operation (and the target table completely overwritten,
> or created).
>
> Could there be such a way, or am I talking nonsense?
Aren't you just asking for a combination of CREATE TABLE LIKE
and a LOAD FROM CUSROR or a remote LOAD?

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

From: Damir on 8 Apr 2010 06:05
> Aren't you just asking for a combination of CREATE TABLE LIKE
> and a LOAD FROM CUSROR or a remote LOAD?

Yes I am, that is exactly what I do now (CREATE TABLE LIKE..., LOAD FROM
CURSOR..., SET INTEGRITY...)
But I would like to avoid the buildup of indexes (SET INTEGRITY), because
that consumes a lot of log space, temp space and time.
Even with local indexes now available (V9.7), I still don't see how I can
detach a table partition and then move it to another (archive) database,
without having to recreate the indexes in the target database.

Regards,
Damir



From: Serge Rielau on 8 Apr 2010 11:10
On 4/8/2010 6:05 AM, Damir wrote:
>> Aren't you just asking for a combination of CREATE TABLE LIKE
>> and a LOAD FROM CUSROR or a remote LOAD?
>
> Yes I am, that is exactly what I do now (CREATE TABLE LIKE..., LOAD FROM
> CURSOR..., SET INTEGRITY...)
> But I would like to avoid the buildup of indexes (SET INTEGRITY), because
> that consumes a lot of log space, temp space and time.
> Even with local indexes now available (V9.7), I still don't see how I can
> detach a table partition and then move it to another (archive) database,
> without having to recreate the indexes in the target database.
What about dropping the index(es) after CREATE TABLE LIKE and before you
load?
I must be missing something obvious.....

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

From: Damir on 9 Apr 2010 08:49
> What about dropping the index(es) after CREATE TABLE LIKE and before you
> load?
> I must be missing something obvious.....

Sorry, I didn't explain the whole situation, so here it is:
We have two (federated) databases: the production and the archive database.
There are a couple of (large and critical) tables, partitioned by range,
that have a "live part" of the table in the prod database and an "archive
part" of the table in the archive database.
These "parts" are completely separate and independent objects (no DPF).
There are union all views in the prod database that unite the live and the
archived data (thru fed server/nicknames).
The application that operates on the prod database usually (99,9% of the
time) needs to (very quickly) access only the live data, so for the
performance purposes (and also to reduce the prod DB footprint, i.e.
backup/restore) once a year we detach the oldest partition from the live
table and move it over to the archive DB where it is attached to the archive
table.
By doing so, we also keep all the (historical) data directly available to
the application, when needed (accessible through the union all views).
Since there are queries, albeit rare, that access the archived data, the
archive tables must have the same indexes as the live tables, otherwise
these queries would run forever.

What we have been doing until now (prior to V9.7) is:
1. detach the oldest partition from the live table
2. "CREATE TABLE LIKE..." on the archive DB, without indexes (to speed up
LOAD)
3. "LOAD FROM CURSOR..." into the new arch. partition
4. "ATTACH..." the new arch. partition to the arch. table
5. "SET INTEGRITY..." on the arch. table (this step takes a lot of time to
complete and also consumes a lot of TEMP and LOG space)

What we intend to do from now on, after converting the indexes to
partitioned, is:
1. detach the oldest partition from the live table
2. "CREATE TABLE LIKE..." on the archive DB, with or without indexes
(probably better with indexes?)
3. "LOAD FROM CURSOR..." into the new arch. partition (fast if no indexes)
3.a. CREATE INDEXES, if not created in step 2. (slow?? space consumption??)
4. "ATTACH..." the new arch. partition to the arch. table
5. "SET INTEGRITY..." on the arch. table (this should be quickly done as no
index rebuild is needed, right?)

The "problem" as we see it is that we cannot avoid the buildup (or creation)
of indexes on the archive side.

Does all this make more sense now?
Or am I completely missing something that is too obvious (to others but not
to me)?

Greetings,
Damir



 |  Next  |  Last
Pages: 1 2
Prev: db2look and trusted context
Next: 6 Find Your Shoe Size - Step by Step Guide to Measuring Your Feet