RE: How to Restore AS400 data to SQl Server

[<lgoodbar@xxxxxxxxxxxxxx>]

A few tables? A lot of tables? Indexes? Views?

You can't "restore" the tables, but you can migrate the data either by
export (another posted mentioned CSV), or using linked servers.

I set up a linked server from SQL Server to i5/OS, created the tables
using corresponding data types (packed/zoned typically become decimal,
etc.), and wrote insert/select statements.

I only needed a few tables (less than 20) so didn't pursue a more
automated approach.

Some considerations:
* Since I used SQL Server as a "reporting" server (instead of
transactional), I chose to convert fake dates (numeric 8,0 yyyymmdd,
numeric 7,0 cyymmdd, etc) into real date data types. Be prepared to deal
with invalid/null values and adjust in the migration script.
* Our DB2 tables were generated by Synon so have gibberish field names
(CZAZCD = location). I chose to use meaningful names instead.
* If you are doing ongoing migration, set up a mechanism to remember
last key values copied. Several of our files are multi-million rows, and
are keyed using a surrogate number (similar to an identity field). We
have a table with the last surrogate copied, and after the first
migration, we can simply copy new surrogates.

--Loyd

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Ahmed.Hussain@xxxxxxxxxx
Sent: Tuesday, April 14, 2009 12:03 PM
To: midrange-l@xxxxxxxxxxxx
Subject: How to Restore AS400 data to SQl Server

Hi,

I have to restore some of AS./400 Physical files to SQL Server, and was 
thinking that I can get good ideas from the group. Is there any ideas to

do that.


Thanks,

Ahmed Hussain
BPCS Support