| Author |
 Topic  |
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
 Posted - 09/11/2007 : 13:46:10
|
Simplest, fastest way to copy 7 tables from as400 to SS2K weekly. Any idea?
I have a as400 server and 2 SS.
as400
SSbox1: db1 and db2 (two databases)
SSbox2: db2 (one database called db2)
SSbox1: db1 is getting data from as400 then sending it into db2 daily.
SSbox2: DTS is copying everything from SSbox1.db2 into SSbox2.db2 weekly.
I wanna simplify process from SSbox2. Maybe a linked server from SSbox2.db2 to SSbox1.db2?
let me know if you don't understand.
=============================
http://www.sqlserverstudy.com
|
Edited by - funketekun on 09/11/2007 17:14:13
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/11/2007 : 14:02:08
|
It depends.
How many records are there in each and one of the 7 tables?
How many columns are there in each and one of the 7 tables?
E 12°55'05.25"
N 56°04'39.16" |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 09/11/2007 : 14:27:53
|
quote:
Originally posted by Peso
It depends.
How many records are there in each and one of the 7 tables?
How many columns are there in each and one of the 7 tables?
E 12°55'05.25"
N 56°04'39.16"
biggest has 2.3 millions records and 13 columns
then the rest are less than 1million record
=============================
http://www.sqlserverstudy.com
|
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/11/2007 : 14:35:02
|
By "unload" I think Jeff means "export".
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 09/11/2007 : 16:20:14
|
quote:
Originally posted by Peso
By "unload" I think Jeff means "export".
By "unload", I think Brett is referring to a DB2 command. 
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/ |
|
|
|
ValterBorges
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 09/11/2007 : 16:25:36
|
If you can get odbc drivers for AS400 / DB2? such as data direct and then use a link server you can write queries using openquery or if you use an oledb driver you can even use four part naming conventions
select * from linkedserver.catalog.schema.object
or
select * from openquery(linkedserver,'select * from catalog.schema.object');
PS:
If anyone is interested in high paid work in Connecticut give me a shout or send your resume to me at ValterBorges@msn.com.
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ValterBorges
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 09/11/2007 : 16:50:04
|
| sent you an email on this. |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
|
|
ValterBorges
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 09/11/2007 : 17:52:13
|
Yes, you can use a linked server to move data between two SQL Server Instances and between non sql such as DB2, Oracle, Sybase, etc. if you have the right drivers.
|
|
|
|
rmiao
Flowing Fount of Yak Knowledge
USA
7266 Posts |
Posted - 09/11/2007 : 22:58:42
|
| But will moving 2m rows via linked server be faster than dts? |
|
|
|
ValterBorges
Flowing Fount of Yak Knowledge
USA
1429 Posts |
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 09/13/2007 : 15:28:09
|
valterborges,
we already have SP againsts this database. we don't want to create linkservers, then rewrite the SP. There are hundred of them.
=============================
http://www.sqlserverstudy.com
|
Edited by - funketekun on 09/13/2007 15:31:51 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
|
|
Topic |
|
as400 to SS2K
