Migrating from AS400\db2 to Wintel\SQL2000

Migrating from AS400\\db2 to Wintel\\SQL2000

Has anybody migrated their Oneworld XE config from AS400\db2 to Wintel\SQL2000. If yes what's involved and would you recommend it?.
Thanks
Oneworld XE service pack 22
AS400 (running like a dog)

Re: Migrating from AS400\\db2 to Wintel\\SQL2000

Harry,

I have migrated our DB2-AS/400 installation to SQL Server-Windows earlier this year. It went well. If you search the list, you will see LOTS of posts concerning this very subject and a lot of people have done it. If you are more or less familiar with CNC, than I would definitely recommend hiring a knowledgeable consultant who has done this before.

Simply put:

1) Setup NT enterprise server
2) Migrate data
3) Change data source definitions (both OneWorld and Windows)
4) Change OCM mappings
5) Enjoy that third cup of cappuccino that's waiting for you beside you computer screen ;-)

Oh... I almost forgot, TEST!, TEST!, TEST!

Re: Migrating from AS400\\db2 to Wintel\\SQL2000

Jim,
Your the daddy!!! Have you still got your notes (general) on the migration and would you be kind enough to share them with me?
Thanks in advance
Harry

Re: Migrating from AS400\\db2 to Wintel\\SQL2000

This is a relatively common exercise - and the only thing I would expand on in Jims list is the data transfer. Use OneWorld to perform the transfer. Personally, I create COPYTBLE scripts and import them directly into Table Conversions that I run locally on a number of clients or citrix sessions. This way, a large number of tables can easily be copied in a controlled manner.

Why use OneWorld ? Primarily because this ensures that the data is clean, and several tests can be performed relatively easily. It certainly takes longer, since the client must copy the data from DB2 and through into SQL Server (hence additional datasources are required) - but once it is done, then after a full package build, you'll be off and running relatively painlessly !

The longest tables will naturally be the largest - for example, F42199 or F0911 might be the largest tables, and these I recommend starting on a copy as early as possible, while you copy other tables in parallel.

I strongly recommend testing your network speed as early as possible as well, since these conversions will sap all of your network latency throughout the conversion.

I would certainly plan on testing several times prior to the "actual" conversion - just to make sure everything is converted correctly and is working fine. I plan on doing this again later in the year for another customer...

Re: Migrating from AS400\\db2 to Wintel\\SQL2000

Harry,

I do not have any notes on paper, sorry. However, I will detail the procedure a little more for you with pleasure. Again, keep in mind that if you do not have a really good understanding of CNC concepts, then again I would recommend hiring a consultant who has done this before. Nobody wants to mess up a production environment.

As Jon, a.k.a. altquark, has mentioned, get some network bandwith tests done early in the process and try doing a couple of simulations to catch and work out any irritants that might occur.

1) Set up your NT enterprise server

- See PeopleSoft documentation for this

2) Copy data from AS/400 to SQL Server

- I totally agree with Jon that you should use OneWorld tools
to get this done. There are many ways to copy data from the
400 to SQL, however only OneWorld formats your data to its
specifications. Although Jon likes to use TCs to copy the data
over (which is also a really nice solution), I prefer to use the
R98403. Depending on the speed of your network, I would
create anywhere between 10 to 15 versions of the R98403
and use data selection to balance the load of the data copy.
If you have Citrix in place, then use it to kick of the UBEs.
I try to use as many OW clients as I can to speed up the
data copy. As an example, my first try at copying data over using
only one version of the R98403 took 24 hours to transfer 40 Gb
of data. After spliting the process up into 15 versions, it took a
little under 6 hours to get it done using 10 OW clients. Keep
in mind that many different factors will influence the data copy:
network speed, network latency, network swiching, bandwith
occupied by other applications and of course client and
enterprise machine specifications. Depending on the how much
you use OneWorld and what modules you are using, use
dedicated versions of the R98403 to copy over the biggest tables
by themselves as Jon has mentioned: 1 for the F0911, 1 for the
F0911T, 1 for the F42119, 1 for the F43119 and so on.
Off cource, try doing a few simulations off hours prior to go-live.

3) Modify data source definitions

- Unless you can set up an isolated testing environment and
depending on your OW architecture, this step can be very
dangerous. You have to modify each database and logical
OW data source from AS/400 setup to SQL Server setup:
Business Data, Control Tables, Object Librarian, etc. Depending
on downtime availability, you can gradually migrate data.
Start by Business Data and Control Tables, then when your next
downtime window opens, migrate Data Dictionary, Object
Librarian, etc. That's the beauty of CNC! Don't forget to update
the Windows data sources as well (clients, app servers and
deployment server).

4) Modify OCM mappings

- Unless the machine names are the same which I doubt, you
have to change your data source OCMs to reflect your new
setup (Business Data -> new enterprise server,
Control Tables -> new enterprise server, Central Objects ...
unless already on the deployment server, etc)

- Same goes for UBEs and BSFNs

5) Coffee and test, coffee and more tests and did I say coffee and
lots more tests.

Good luck.