Day 454 - New GIS Version Required

A new GIS software version is required in order to overcome a problem in account that has been stalled for weeks.
Time is running out and the new GIS version should be available at the end of this week.
This will be a major improvement in the account area because the mapping and testing teams will finally be able to define it and test it.

Day 422 - Unstable Mapping

Things are not going the way I, and the management team, was expecting.
After the restart, teams start working with focused on the current official project dates, even if they don't really believe it.

But the last GIS loadings we've performed showed that there was a major mapping regression. For instance car insurances were all loading correctly and now there isn't a single insurance that loads correctly, they have all been rejected.
It turns out that the products configuration in GIS, that should be concluded some weeks ago, has changed again.
And that was not all, all the product insurance mapping rules are changing. This mapping instability means that the project has an overall regression when there's only 8 weeks left to the first data migration simulation.

But this is not all. When the testing team tried to perform tests, they canceled the task because there was nothing that they could test. The migrated data that was loaded and available for testing will change soon.

Day 372 - Project Due Date Postponed Again

The project due date has been postponed four more months. It looks likes the data migration will now happen in the 4th of October.
This happened mainly because the target system will not be fully developed up to June.

It looks management wants to keep the data migration plan to June and give us some months of force holidays. I do hope management won't go with this plan because it is impractical.
It is wrong to believe that the transformation rules can be frozen for 3 months when there will be massive development on the target system. And the project sponsor teams already know it by experience, whenever it was required to change something on the target system there was always a considerable amount of mapping rules that had to be redefined, implemented and tested. Plus, it is also wrong to believe that whatever tests the testing team will perform over this 3 months will not require any mapping changes.

This postpone is a great opportunity to define and implement the test plan. Some of the people from the testing team, which I've been talking to, have seen the number of preliminary tests we've identified and agree that it would be unfeasible to define and implement a testing plan if the data migration was to be performed in June.

This is actually the first time I'm on a data migration project were, once set, the official D-Day had been postponed. And in this project, it has already happened twice!
Like in the first postpone, and as far as I know, the data migration itself is one of the few projects that was on schedule by the previous plan.

As in the previous postpone, this probably means that we will have extra resources for most of the time, considering the amount of work of the data migration team in this stage.
But it is still not clear what management will do regarding all the changes that this postpone brings.

Day 363 - First Production Problems

The testing team was right. They needed more time to test the system.
The first problem found regards product integration and interface testing.

It looks that the data migrated by us is using a key that differs from a satellite system. We checked the data migrated and it complies with the specification, the mapping rules defined and the transformation rules implemented. This was also confirmed various times over the last 4 months by the tests performed on the migrated data.

The other system is sending a different key from the one we're sending.
This is an obvious integration error which would easily be found and corrected if integration tests had been performed.

With less than 3 month from the real data migration, this is not a good sign. Specially because we've already alerted several times to the need of consistent and automatic tests, something that no one here seems to care for except the testing team which, by the way, does not have the power to enforce it.

Day 359 - Pre-Production Stress

People are getting pre-production stress.
Things should go smooth, but since people do know that the tests should have been better, they get stressed.
Today, one of the people responsible for the data migrated for the system go live tomorrow has just came to ask me why the data has only 379 records. That is the same person that defined the data source scope, the transformation rules and has been validating the this data for the last 6 months...
In the end, it was all correct, it was just a pre-production stress attack.

I think this shows how uncomfortable people really are with the data migration quality assurance procedure.

Day 358 - Go Live Files Ready

We have received the source files and have loaded them into our staging area.
We have executed the data migration for the entities and created the GIS loading files required for the go live.

The files have been loaded in the quality acceptance environment without a single problem. Tomorrow the testing team will check if everything is in order and, if so, the data will be loaded on the day after.

Day 352 - Data Migration Plan For Go Live

We have just received the plan for the data migration part that is required for the new system kick-off.
We will receive the source data, loaded it in our staging area, execute the transformation rules for the entities and create the GIS loading files for the acceptance and production environments.

Since a postpone has not been granted for the new system go live date, the testing team seems to be working a bit harder and things seem to be a lot better.

297 - Data Loading Files Performance Problem

The migration scope has increased.
New source data has come into play and thus new mapping specifications have been made.

This has resulted in a really big data loading performance problem.
Our data transformation procedure is still fast, but the creation of the GIS loader files from the transformed data is starting to give us some headaches.

The GIS data loader has a flat file structure that is very verbose, the loading of each single value of each record is done through a 300 character text file line.
This means that a single database table row is transformed into, something like, the same number of text lines as the number of columns the row has. Plus the file and records header and footer structure.

As an example, house insurances data transformation is performed in around 4 hours, sequential time, and it generates about 184 millions of records. This is all performed in the transformation server.
These records are then exported into the GIS data loader file format from the Windows directly into the AS/400. This procedure is now taking much time, over 6 hours, sequentially, in the best case scenario.
This is obviously too much time, so we are exploring several hypotheses, ranging from creating a parallel file write process; writing the files locally, with and without compression, and transfer them via FTP to AS/400; cluster indexes with full coverage maintained in a different disk; splitting the database schemes across several disks.
Some of these techniques can, and will, be combined.

We have to tune the process from our side since there is not much the GIS or the AS/400 can do when it comes to massive data load tuning.
We are facing a lot of hard work in these next days.

Day 238 - Migration Status

Finally I got a set of entities that migrated correctly.
It was actually quite simple, once the mapping rules were correctly specified according with the data contents. The main problem is that the source team had, as usual, some difficult to admit that their data is dirty and its quality is actually a lot lower than they expected. In this particular case, it was the zip codes.
Once they admitted that the zip codes needed specific rules because they were dirty, it became an easy migration task.
In the next few days I expect to migrate other entity sets without errors.

The migration performance as an overall is quite slow, even using 5 parallel executions. We got some times and, as already known, the GIS loading procedure is responsible for the biggest migration times.
Usually we perform the ETL cycle within Data Fusion, which means we deliver the data directly to the target database, i. e. usually we would only have the Data Fusion transformation procedure time, but in this particular scenario we have to create a text file which GIS will use to load the data.
Here's an example of the performance times we're having now:

Car Insurance Claims (470.563 records):

• Data Fusion transformation procedure: 51m
• File creation for GIS loading: 2h 10m
• GIS file loading: 238h sequential time (time calculated based on 16h of loading)

Personal Accident Insurance Claims (29.303 records):

• Data Fusion transformation procedure: 1m 51s
• File creation for GIS loading: 1m 40s
• GIS file loading: 2h 17m with 3 parallel processes (5h 9m sequential time)

Entities (682.569 records):

• Data Fusion transformation procedure: 6m 3s
• File creation for GIS loading: 23m
• GIS file loading: 5 h 55m with 5 parallel processes (27h 17m sequential time)

Tonight GIS will be cleaned up and the AS/400 will be tuned for better performance for file reading. In the next few days I'll get some new times, hopefully better.

Day 219 - New Convert Sequence Function

We have a functional requirement that needed our technical expertise to be solved, it was a small challenge but an interesting one.

Data Fusion already has a convert sequence function, which receives a key a returns a sequence.
It is very useful to convert things like keys and identifiers between the old system and the new system.
It works on a very simple basis: the new system key is requested based on the old system key, if it exists it is returned, otherwise it is created based on the addition of 1 to the current maximum value present, and the returned.
Here is an example:

Old Key ACC0005 ACC0008 ACC0017 [...]

New Key 1 2 3 [...]

This is persistent between data migration executions, which means only new keys will be added, and it is quite efficient since it is entirely loaded into memory.

This has suit us for almost a decade. All our previous data migrations never required identifier conversions, or creations, based on anything other than a set of identifiers.
But here a new challenge raised. We still must return the new system key based on the old system key, but the creation of a new system key is no longer based on the old system key.
The creation of the new system key is now based on a subset of the old system key.
Here is how it works:

1. Request the new system key based on the old system key
2. If if exists: return the value
3. If if does not exist:
1. Select the next value from the old system sequence key
2. Add an entry to the conversion table registering the old system key, the old system sequence key and the new system key
3. Return the new system key
   

Here is a very simple example for better understanding.
Consider the old system key to be based on the source system account number (ACC) and historical image (IMG). Consider the sequence being incremented only by the account number. A conversion table would be something like:

Old Key ACC0005 # IMG0001 ACC0005 # IMG0002 ACC0008 # IMG0001 ACC0017 # IMG0001 ACC0017 # IMG0002 ACC0017 # IMG0003 [...]

Old Sequence Key ACC0005 ACC0005 ACC0008 ACC0017 ACC0017 ACC0017 [...]

New Key 1 2 1 1 2 3 [...]

Note that the old system sequence key varies based on a subset of the old system key.

Obviously the above example is purely academic, but the this easy to use mechanism is being used to solve a complex transformation problem and it has became extremely useful.

Day 205 - First Change Requests

As expected, lots of change requests and lots of mapping meetings are taking place this week.
My guess is that by December, the maximum number of change requests covered by the contract will be reached. But this is just my guess...

Day 199 - Firts Entity Migration Test

Today the entities, clients only, will be migrated for the first time for real.
The data transformation process itself is quite fast, less than 500000 records will be transformed in less than 30 minutes, but them we have to create structured files that gather all the transformed information into GIS the loading file format, and that will take around an hour.

I believe in a low error number from this first real client migration test, but the results will only be available Monday.

Day 88 - Mappings at Cruse Speed

Insurance claims mapping is rolling at cruse speed and we're already implementing some of them.
There's already other areas eager to start the functional mapping, but we've decided to close the insurance claims before starting up new areas.

Since the due date has been shifted from October to April next year, the project sponsor decided to interrupt the project in August for a full month vacation. This makes sense since October was only possible if the sponsor, and ourselves, worked full time, meaning no vacation for anyone.

Day 46 - Performance Tests

I know it's Saturday, but a lot of data migrations work is done during the weekends. Specially during the last weeks before the end of the project.

But this Saturday, we were focused on performance tests. We had the AS/400 entirely for us for about 3 hours.
We executed performance tests for the transformation rules engine and for the GIS data loader.

As excepted, the performance increased both with a single process and with multiple processes. for both systems.
In the transformation engine we've executed up to 6 parallel migrations, but the maximum gain was below that, the 4th or 5th, depending on the transformation features, more I/O bound or more CPU bound.

There was not time to perform one interesting test though, running the transformation engine at the same time as the GIS data loader.

Nevertheless, the outcome was what we expected: to have a dedicated Intel machine running Linux for the exclusive usage of the Data Fusion. This will allow GIS data loader to have more resources, since we're not competing.

Here's some nice charts from the tests performed.

Day 34 - Denial Phase

The meeting with the project sponsor went almost as expected.
They loved the idea of having a separate server to execute the transformation rules, but the AS/400 administrators are in denial.
AS/400 administrators don't want to believe that AS/400 is not that as fas as they all brag about. If I had spent as much money as an AS/400 costs, I would be denying those facts too... It's just too painful to hear that in certain tasks, the AS/400 can be beaten by a much cheaper 8 CPU Intel machine running Linux...

By the way, the AS/400 were we're testing, the development environment, has the following features:

• iSeries 570
• 1 CPU Power 5 at 1.65GHz
• 3000 CPW
• 10GB RAM DDR1
• Internal HD: 1.6TB

The AS/400 administrators now want us to perform tests on the qualification machine:

• System i Power 6 570
• 1 CPU a 4.7GHz (maximum of 4 processors)
• 5000 CPW (maximum of 20000 CPWs)
• 20GB RAM DDR2 400MHz (maximum of 90GB)
• External HD: 2.4TB

I bet the test will turn out the same results, since the transformation engine is CPU bound, but nevertheless, we'll do the test during a weekend where there's no activity on the machine.

./M6

Day 30 - Performace Tests

There's a set of performance tests to do before choosing the final migration infrastructure.

There's tests over the initial data extraction from OS/390 and loading into AS/400.

There's tests over the data transformation rules.

There's tests over the final data loading into GIS.

From this set of tests, only ours, the data transformation rules, have been performed so far.

As expected, AS/400 is not a fast machine to run Data Fusion transformation engine. We already knew this, but AS/400 system administrators always say we're wrong and that they have a fantastic and fast machine. Their pride has been hurt...

As an example, running entirely in AS/400, 400000 source records have migrated at ~800 records per secord. Note that this was a local data migration execution, the source and target databases are on the same machine as the transformation engine.

But running the same transformation engine on my, not very fast, desktop computer with 1GB of RAM, the same 400000 records migrated at ~1400 records per second. Note that on this test the source data was retrieved from AS/400, transformed on my desktop compute, and sent back to AS/400, i.e. the data had to travel over a common 100 Mbit network.

The protocol loading interface was also subject to tests and it's CPU bounded, We have one single processor, of a 4 processor machine, and it consumes 100% of that CPU. When running on AS/400, it perfomed ~12000 records per second. Running on the desktop it does only ~8000 records per second.

The scalability tests on AS/400, with a single CPU for us, proved that a linear execution is faster than a two parallel executions. We haven't had the chance to test parallel execution with more CPUs.

We've been discussing what was the best approach allow us to scale and one thing is for sure, we need to run the transformation engine outside the AS/400, because on the D day we have to share, as it's happening now, the machine with others, specifically with GIS data loading.

Probably we'll have a 6 or 8 CPU machine running Linux and connected to AS/400 over a 1 Gbit network. This will allow us to run multiple transformation engines and free AS/400 for the other data migration processes.

Day 24 - New Mapping Rules

There was a new meeting for mapping rule definition. Most of the transformation rules are closed, there's just some tweaks related with foreign addresses and some internal codes.

Next week there will be more full migration cycles with fresh data, since we've discovered that the current test data was not correct.

Meanwhile, performance tests are being performed on AS/400 and we were able to convince the system administratos to reconfigure the anti-virus not to read Java .jar files, allowing a better performance on our desktops.

And yesterday, the desktops were automatically updated and rebooted. This policy has also been removed from our desktops, since it is far too dangerous to perform such operations when a data migration is being controled from our desktops.

Day 17 - First Try

The protocol loading interface is ready, it's cool but I did not code it.

And I have the first mappings ready to roll.

We've done a full cycle, with about 85% of the transformation rules implemented, and we got less problems than I expected for the first try. 

Foreign zip codes were wrong, which we were expecting since this mapping specification is still under discussion.

People names were wrongly specified, since the surname was being lost.

The were some empty honoricif titles, since they are also still under specification.

A couple of bytes were on the wrong place on the protocol file.

We've tried it with one hundred records from the source, which resulted in, aproximatly, 2600 records in the protocol file.

The whole process was executed in our desktop computers, since the AS/400 is still not ready for us, the Java 1.5 is missing. Next week I'm expecting to execute the whole process in the AS/400.

Day 14 - First Mapper

I've just set up the Data Fusion IDE and server on my machine. Until we install the server on AS/400, my desktop will have to do the trick. I'm using the JTOpen JDBC driver to connect to AS/400 files via DB2.

In order to create the Data Fusion tables layout automatically, I've set up a simple command line application that connects to AS/400 through the same JTOpen JDBC driver and queries the files/tables metadata, in particular it's structure, the columns name, data type, precision, etc..

I've also implemented some transformation rules to run some tests and everything worked fine.
I'm still waiting for some target specification information from the target team, but I believe that I'll have two mappers ready for some serious testing real soon.

I've also received the production data, which means know I can test my data profiler with real data.

Day 7 - First Mappings

The first mappings have been specified.

I've spent almost all day with the target team and the source teams, one from the main system and another from a secondary system.
It was a productive day, since we were able to map over 90% of both systems, that will migrate into the same target specification. The doubts and problems detected will be easily solved.

As expected, some cleaning will be performed by the trasformation rules. For instance, in some circumstances, the honorific title will have to be automatically inferred from a free form text field.
It will also be necessary to perform duplicate detection and entity fusion before executing the migration.

None of these seems really problematic at this time.