SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 as400 to SS2K
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 09/11/2007 :  13:46:10  Show Profile  Visit funketekun's Homepage  Reply with Quote
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

Posted - 09/11/2007 :  13:47:08  Show Profile  Reply with Quote
DTS?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 09/11/2007 :  13:59:09  Show Profile  Visit funketekun's Homepage  Reply with Quote
using copy object..too slow.

=============================
http://www.sqlserverstudy.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 09/11/2007 :  14:02:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/11/2007 :  14:25:30  Show Profile  Reply with Quote
For me, I would unload the tables in tab delimted form from as400 (DB2?) and the bcp the data in



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 09/11/2007 :  14:27:53  Show Profile  Visit funketekun's Homepage  Reply with Quote
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
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 09/11/2007 :  14:32:02  Show Profile  Visit funketekun's Homepage  Reply with Quote
quote:
Originally posted by X002548

For me, I would unload the tables in tab delimted form from as400 (DB2?) and the bcp the data in



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam







how do you unload the tables from as400

=============================
http://www.sqlserverstudy.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 09/11/2007 :  14:35:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
By "unload" I think Jeff means "export".



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 09/11/2007 :  16:04:38  Show Profile  Visit funketekun's Homepage  Reply with Quote
any tutorials? about exporting from as400

=============================
http://www.sqlserverstudy.com
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/11/2007 :  16:19:48  Show Profile  Reply with Quote
Jeff?

http://www.dbforums.com/archive/index.php/t-934789.html



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36648 Posts

Posted - 09/11/2007 :  16:20:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 09/11/2007 :  16:25:36  Show Profile  Reply with Quote
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.












Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/11/2007 :  16:27:59  Show Profile  Reply with Quote
Part time or full time?

Remote or on location?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 09/11/2007 :  16:50:04  Show Profile  Reply with Quote
sent you an email on this.
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 09/11/2007 :  17:13:03  Show Profile  Visit funketekun's Homepage  Reply with Quote
please read above again. Edited my post.

=============================
http://www.sqlserverstudy.com
Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 09/11/2007 :  17:52:13  Show Profile  Reply with Quote
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.

Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 09/11/2007 :  22:58:42  Show Profile  Reply with Quote
But will moving 2m rows via linked server be faster than dts?
Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 09/12/2007 :  08:10:18  Show Profile  Reply with Quote
Depends.

Take a look at Gert Drappers presentions on data loading.

http://www.sqldev.net/events.htm#2004_PASS_Community_Summit_Sept_28-Oct_1,_2004,_Orlando_(FL)


PS: If anyone is interested in a sql position in Connecticut with excellent pay please send me a resume to ValterBorges@msn.com
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 09/13/2007 :  15:28:09  Show Profile  Visit funketekun's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/13/2007 :  15:31:22  Show Profile  Reply with Quote
quote:
Originally posted by funketekun
we already have the SP againsts this database. we don't want to create linkservers and then rewrite the SP. There are hundred.




Huh?

No sequitor...does not compute --Nomad

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 09/13/2007 :  15:34:42  Show Profile  Visit funketekun's Homepage  Reply with Quote
quote:
Originally posted by X002548

For me, I would unload the tables in tab delimted form from as400 (DB2?) and the bcp the data in
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam



Just tested it..Linked Server is faster than BCP.
Wonder why you suggested that.

=============================
http://www.sqlserverstudy.com
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000