as400 to SS2K - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

as400 to SS2K

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-09-11 : 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

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-11 : 13:47:08

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

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-09-11 : 13:59:09

using copy object..too slow.

=============================
http://www.sqlserverstudy.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 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

Posted - 2007-09-11 : 14:25:30

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

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-09-11 : 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

491 Posts

Posted - 2007-09-11 : 14:32:02

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 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

491 Posts

Posted - 2007-09-11 : 16:04:38

any tutorials? about exporting from as400

=============================
http://www.sqlserverstudy.com

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-11 : 16:19:48

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-11 : 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
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-11 : 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

Posted - 2007-09-11 : 16:27:59

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

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-11 : 16:50:04

sent you an email on this.

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-09-11 : 17:13:03

please read above again. Edited my post.

=============================
http://www.sqlserverstudy.com

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-11 : 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
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-11 : 22:58:42

But will moving 2m rows via linked server be faster than dts?

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-12 : 08:10:18

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

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-09-13 : 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

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 15:31:22

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

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-09-13 : 15:34:42

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

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources