x

Moving SQL Server tables to Iseries

I have a project that requires that I perform the following:

  1. import an Access 2003 database to SQL Server 2008
  2. clean up data types/data/column names
  3. export the cleaned tables to an Iseries database

I used the Miscrosoft SQL Server Migration Assistant 2008 for Access to import the database into SQL Server.

I cleaned up the columns and set the data types to ones that would be compatible with Iseries DB2

Where I am running into problems is with the conversion/export of the tables to the Iseries database server. I have never had to do this before, so I have started with creating an SSIS package to perform this function, but am stuck. The SSIS does a great job of moving data, but I need to move the structure as well.

I could certainly use some ideas on how to perform the last part of this task.
more ▼

asked Aug 04, 2010 at 03:29 PM in Default

Dave Myers gravatar image

Dave Myers
123 15 15 16

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I take it you already have an ODBC-connection to your iSeries machine. If that ODBC-provider is any good, you should be able to setup the iSeries machine as a linked server and create the objects with SQL.

With linked server, you can use AT to execute statements directly into your iSeries machine. Let's say you have a linked server setup called DB2400, you could alter the create scripts from

CREATE TABLE schema.tbl (ID int, col varchar(10))

to

EXEC('CREATE TABLE schema.tbl (ID int, col varchar(10))')
AT DB2400

I'm no iSeries expert at all, I've just done silly little things like connecting to an AS/400 to read a customer-table in a one-off job. Creating objects in iSeries will take much more iSeries knowledge, like knowing how indexing works etc.

I'm sure there are tools to migrate to and from iSeries. My colleagues have used Data Mirror to replicate between iSeries and Oracle, but they have only set it up as a one-way connection to read from iSeries. That - unfortunately - seems to be the common task when googling for some hints.

If you want more about linked server and iSeries: [http://www.mcpressonline.com/database/db2/db2-integration-with-sql-server-2005-part-i-linked-server-enhancements.html][1]

[1]: http://www.mcpressonline.com/database/db2/db2-integration-with-sql-server-2005-part-i-linked-server-enhancements.html
more ▼

answered Aug 05, 2010 at 03:44 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

nice, hadnt thought about using a linked server. +1
Aug 05, 2010 at 04:09 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

Dave, its likely you will get better answers to this on a Iseries forum (here maybe? :-/ [http://forums.systeminetwork.com/isnetforums/forumdisplay.php?f=30][1] ). If you were coming to SQL from Iseries then we would be full of answers as we are SQL focussed here, Iseries isnt something many of us will have experience with.

My expectations would be that it should auto sense the majority of data types during its import stage so you should get most of the way there. I would go for a flat file source with a codefile if necessary - the Iseries equivalent of BCP.

Alternately can you script the source database and then convert the T-SQL to Iseries format and build the destination database that way?

[1]: http://forums.systeminetwork.com/isnetforums/forumdisplay.php?f=30
more ▼

answered Aug 05, 2010 at 01:08 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x939
x66
x28
x19

asked: Aug 04, 2010 at 03:29 PM

Seen: 3066 times

Last Updated: Aug 04, 2010 at 03:29 PM