Copying teradata table into another table

[ds_teg]

I am working in datastage and teradata . I have table for which datatype is varchar(20) .

I have nearly 4 billion records present in the table . There is a requirement to change the datatype from varchar20 to varchar40 .

we can use alter table even though there is data in the table . But for some unknown reason that is rejected .

Now we are planning to create another table ( table2) and copy the data from table1 and drop table1 .

I guess this can be done through multiple options


1) insert into table2 ..select * from table1 .But i am not sure of performance

2) Create a datastage job with source as table1 and target as table2 .Use Multiload to load the data . I think this will need lots of temp space and scratch space to do .


Can anyone please suggest which one approach is better and why ?

Thanks

[asorrell]

Unless your DataStage machine is significantly larger than your Teradata machine, you should do this in Teradata. The overhead of transferring that much data over the network is immense. Teradata's are very efficient in stuff like this as well.

Let the database do its thing!

[ds_teg]

Andy ,Thanks for your quick response .

Any idea how the performance will be to copy 4 billion records with 350 columns through an insert into select * statement . Please let me know what are the considerations I need to take before doing this . I believe I cannot use an MLOAD or Fastload to populate an empty table from another table .

I thought of using datastage first as it will use MLOAD and performance will be good . But temp & scractch space are big concerns in this approach

[anbu]

I copied around 170 million rows from one table to other in 30 minutes

[ds_teg]

Anbu ,

which method you have used ? how many columns are there ?

[anbu]

I used

Code: Select all

insert into select * statement

Table had 30 or 40 columns

[ds_teg]

That explains ...Here I have nearly 4 billion with 300 columns ..So its way big ...I wondering if any other approah is there to load the data

[ds_teg]

I have fired the below query to check the space occupied in the table :

select databasename, tablename, sum(currentperm) , sum(peakperm)
from dbc.tablesize
where databasename = 'dbnae'
and tablename = 'table1'
group by databasename , tablename;

Total size : 312231815168.00

[chulett]

we can use alter table even though there is data in the table. But for some unknown reason that is rejected.

Me, I would spend some time to figure out why it was "rejected", there's no reason a simple alter to increase a field size should involve copying billions of records.

[ds_teg]

DBA's are skeptical bout using that alter statement as it has corrupted the data in V2R5 version of teradata . This is the reason why I am exploring different options .

[chulett]

So, it's not that it didn't work, they don't even want to try it. Nice. Maybe you just need a patch, if there is some kind of "known issue" with doing an alter like that. Any way, have fun exploring!