Copy data from one database to another on the same server

Database
Enthusiast

Copy data from one database to another on the same server

using del from tbl; insert select from tbl;  for copy data from one DB region to another take a lot of time.(same DB server)

copy is done just between two mirror databases.

Is there any better ways to do it make copy faster.

9 REPLIES
Enthusiast

Re: Copy data from one database to another on the same server

adding some addnl info..

the purpose is to isolate two process working on the datasets, where one process just stops to allow copy and then takes over updating/inserting data to the source. The target is used for another process to run without intervening the source. this is repeated few times a day..

Teradata Employee

Re: Copy data from one database to another on the same server

The two statements, delete and insert select, should be executed as separate statements with commit after each one. If they are executed in the same multi statement request or in the same transaction then the database cannot execute them in fast path mode because it has to be prepared to rollback. 

Enthusiast

Re: Copy data from one database to another on the same server

Thanks Todd. We tried the same but couldn't see much difference in copy times.

As per my understanding on the approach you said is below.

begin tran;

del from tbl;

end tran;

begin tran;

insert into tbl; sel from tbl;

end tran;

we do have this in a stored proc to dynamically prepare the stmts and execute it using 'exec immediate strSQl';

Is the approach understood sounds correct?

Thanks for your time & help.

Senior Apprentice

Re: Copy data from one database to another on the same server

No, it's wrong.

If you run a Teradata mode session every SQL request is automatically commited, no need for BEGIN/END TRANS.

A DELETE can only use FastPath if the optimizer knows it will be commited. If you submit

begin tran;

del from tbl;

end tran;

in seperate requests this is not known while deleting and will result in Transient Journal.

The recommended way is:

-- 1st request, commited, fastpath, no journal
EXECUTE IMMEDIATE 'DELETE FROM tab;'

-- 2nd request, commited, fastpath, no journal
EXECUTE IMMEDIATE 'INSERT INTO tab SELECT * FROM tab2;'

For an ANSI session:

-- 1st request, commited, fastpath, no journal
EXECUTE IMMEDIATE 'DELETE FROM tab;COMMIT;'

-- 2nd request, commited, fastpath, no journal
EXECUTE IMMEDIATE 'INSERT INTO tab SELECT * FROM tab2;COMMIT;'
Enthusiast

Re: Copy data from one database to another on the same server

Thanks Dieter. 

Currently we do follow the 1st approach you mentioned. This approach was working fine until we moved to a different infrastructure where the copy time doubled.

Are there any other better options. Please provide your inputs.

Senior Apprentice

Re: Copy data from one database to another on the same server

What do you mean by "moved to a different infrastructure"?

If both tables share exactly the same DDL there's no faster way (should be a direct MERGE without spool in Explain), otherwise MERGE might avoid the spool.

Enthusiast

Re: Copy data from one database to another on the same server

I meant that DB servers were changed from one server configuration to another.
Senior Apprentice

Re: Copy data from one database to another on the same server

Did only the copy time double or other operations, too?

Maybe the new server is less performant?

Enthusiast

Re: Copy data from one database to another on the same server

Copy time seems to have doubled, as we have been observing it for almost few months now.

The only thing i know is new server include compression but the earlier one was not.