• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

moving data from AS400 TO DB2

hi,
we have requirment from client in which they want to move data from AS400 to db2 just a high level review, I was wondering if we can do it with simple insert/select query , As i know in oracle we can save file as flat file , and then we can load by sql loader, I am just new to db2 never used it,1) so wht u think i should ask questions to client
2) best way we can move data
Thanks,
Bobby
0
bobby2929
Asked:
bobby2929
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:

If your DBA is familiar with Federated systems, suggest that he simply establish a federated link to the AS/400 tables/files.  You can then query them from DB2 as if they were "local" to affect the copy to a new table.


Good Luck,
Kent
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Which "flavor" of DB2 will you be moving to?  (e.g. OS/390, Windows, Linus, Unix, etc.)

There are multiple ways to accomplish this.

IBM sells a product (I think it used to be called DataJoiner) that can read and write to several different databases.  If I remember right, it's not cheap, but it can transfer data between all the flavors of DB2, Oracle, SQL Server, etc.

Alternately, if it's a small amount of data, you could export the tables on the AS/400 using CpyToImpF  (Copy To Import File) to get the data into a delimited or fixed-length file (that can be easily imported elsewhere).

One method I used to use extensively back when I was a DBA for both Oracle and DB2/400 was using Microsoft Access as an intermediary.  In Access, I'd create link-tables that pointed to each data-source. Then, I could easily do an INSERT - SELECT in Access to move the data from one platform to the other.  It was clumsy and slightly quirky, but it was functional (and FREE).

HTH,
DaveSlash
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Oh, yeah. I almost forgot.  I believe DRDA can connect to non-AS/400 data sources.  If you decide to go that direction, it will take some set-up on the 400.

DaveSlash
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Kent OlsenData Warehouse Architect / DBACommented:

Hi Dave,

>>IBM sells a product (I think it used to be called DataJoiner) that can read and write to several different databases.  If I remember right, it's not cheap, but it can transfer data between all the flavors of DB2, Oracle, SQL Server, etc.

I've not messed with DataJoiner, but it would be fun to mess with.  I do know that the federated link works regardless of the DB2/UDB host platform.


>>One method I used to use extensively back when I was a DBA for both Oracle and DB2/400 was using Microsoft Access as an intermediary.

Good suggestion.  I've used this more than I care to admit, though it does tend to make one feel more like a hacker than a "professional".  :)


Kent
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
> though it does tend to make one feel more like a hacker than a "professional".  :)

"hacker" ... I have no problem with that. :-)  I've been called much worse!  ;-)

As I said, it a clumsy and quirky solution, but it's better than no solution at all  (or a really expensive one).

I've not worked with DataJoiner, either. And, I agree that it would be fun to mess with. (which I guess exposes our "geek-ness", doesn't it?)

If you think playing with a business software product is "fun" ... you might be a geek!  (and i'm with you all the way, my friend)

:-)

DaveSlash
0
 
ghp7000Commented:
easiest, most hassle free way is to export the required data, re create the tables on your db2 database, and load the data files. If you are using db2 v8.1, do NOT commit the creation of the tables until after the load is finsihed.

you can accomplish the same task by setting up a wrapper (nickname) to the db2 database and then use a simple insert select to 'load' the data. However, if you are inserting millions of records, youll need to adjust your log file space and it will take much longer than a simple load.

if you decide to use the import utility, set the COMMITCOUNT=0 and you'll avoid logging. However, import is useful for small data files, anything over 100,000 records I would use load.

0
 
bobby2929Author Commented:
someone suggest me transfer to MS Acess and then back to Db2
any comments on tht
thanks
bobby
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
You could do that, but it's easier to transfer "through" MS Access (using ODBC link tables) rather than into Access and then out of it.

What platform are you using your DB2 on?
0
 
bobby2929Author Commented:
hi there
its unix on  DB2 side
Thanks,
Bobby
0
 
bobby2929Author Commented:
hi,
From where can i download microsoft ODBC for DB2 for MSAcess as its not in bulid,
Thanks,
Bobby
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now