Day 573 - DB2 Crash

This weekend we are going to load a specific sample in the quality system for integration tests.
Unfortunately this task were almost compromised because our DB2 staging area database crashed real hard.
It took me around 4 hours to put everything working again, and I had to reconstruct the entire staging area responsible for holding the transformed data.

Day 153 - Back To Work

The vacations are over for us, but some sponsor people are still on vacations, including management, so it's a slow start up.

This return has some new changes, we no longer have a partner on the data migration task, so we are now entirely responsible for the data migration.
This means that we are now doing what we know best: data migrations at all levels.

We had a problem with the DB2 license, seems like it has expired during the vacations, but this will soon be solved, since the system administrators are also back to work.

Day 84 - DB2 JDBC Useful Setting

When using the DB2 Command Editor, there's always a lot more error information available than using any other generic database tool.
Why is that, we've inquired.

We've dig a little and we found out that there's a connection string parameter that one can enable that will allow any generic database tool. The parameter is retrieveMessagesFromServerOnGetMessage and must be included in directly in the connection string:

jdbc:db2://{server}:{port}/{database}:retrieveMessagesFromServerOnGetMessage=true;

Please note that the connection string does not follow the standard syntax. It has a : instead of a ;, that is not a mistake, that's relly like that.

./M6

Day 83 - Data Loaded into DB2 on Windows

We have finally received the database data from OS/390 in a format that we were able to load it without trouble in DB2 on Windows.

Some of the weird problems we got on AS/400 will no longer bother us but now, we have to rewrite some scripts because DB2 in Windows is different from DB2 on AS/400.

One of the problems was that I an insert into script that loads the postal code with almost 300000 records. I had to loaded it through the DB2 command line tool. Here's how one can do it:

1. Open the DB2 Command Line Processor tool. Don' t just open a regular shell since it will not work, some environment settings are required.
2. In the shell, execute the following commands:
   

db2 connect to {database} user {username} using {password}
db2 -vf {path/script.sql} -t > output.txt

Don't forget to have a tail and grep installed, if you wish to track the evolution of the script execution and check for operation success.

The entire process took about 6 hours, this includes taking the data from OS/390, moving it to Windows, load it into DB2 in Windows and preparing the staging area with look up and convert tables. Way to much time. This process will definitely be optimized.

We've also found out one of the most idiotic functionality in software I've ever seen.
After installing the database client on the local machines, and after configuring the server access, the Command Editor only allows one to open files from the database server computer. Yes, I mean it is impossible to open any local file using the Command Editor. And no, that is not configurable!

Day 76 - Back from Vacation

I've just came back from a one week vacation.
Unfortunately there's no real news.

It was still not possible to load data into the new DB2 on Windows. Since some field values hold the " character, the DB2 LOAD messes up the data when loading. A new field qualifier will be tried soon.

There was also a kick-off meeting for a new mapping for insurance claims programmed, but unfortunately it did not happened.

I still hope to have new mappings during this week, though.

Day 59 - Perfomance Tests on Intel

We've finished the performance tests on the Intel server.
It's an 8 CPU machine with 8 GB of RAM running Windows Server 2003 64 bits. I was hopping to get it running Linux, but that seem not to be possible.

If you recall, our transformation engine it CPU bound and each transformation engine runs on a single CPU, therefor a direct comparison is easy to perform.

The server is powerful enough to hold the transformation engine and the database itself. Therefor, we made several tests combining the servers.
One test case used the AS/400 as the database server and the Intel machine as the transformation engine. On another test case, both the database and the transformation engine were on the Intel machine.
For easiness, the database on the Intel machine is a, real, DB2.

Amazingly, or not, the Intel CPU was a lot faster than the AS/400 CPU. One single Intel CPU runs our allows us to migrate faster than any combination we've tried using the AS/400.

Here's a full graphic showing the results on both systems. It's easy to see that the Intel machine scales very well when we use several processes in parallel.

Day 41 - DB2 Problems, Part 2

As expected, and as I've logged on Day 28, DB2 on AS/400 is giving us a bad time.

DB2 on AS/400 is not the "real" DB2. Is something awkward, that usually behaves like DB2 and sometimes does not behave at all.

We've used DB2 on other data migrations and we've only had problems on AS/400 version.

To get a glimpse, here's some problems we're having:

• trying to create a view is impossible since DB2 returns a generic error saying it is not possible to complete the command.
• trying to select specific fields from a view is impossible since DB2 returns an erro saying it does not find such fields.

One could think I may have extremly complex queries, well, judge for yourself (real column and table names have been changed).

Running the following query works fine:

select substr(ZIP1, 1, 2) ZIPinit, city,
       (ZIP1 || '-' || ZIP2) as zip_code
from (
    select ZIP1, ZIP2, city, 
    row_number() over (partition by substr(ZIP1, 1, 2), city 
    order by ZIP1, ZIP2, city) as ZIP_ORDER
from LIBWRK.ZIP_CODES
) as ZIPs

but creating a view over it is impossible:

create view LIBWRK.ZIP_APROXIMADO as
  select  substr(ZIP1, 1, 2) ZIPinit, city,
         (ZIP1 || '-' || ZIP2) as zip_code
  from (
    select ZIP1, ZIP2, city,
      row_number() over (partition by substr(ZIP1, 1, 2), city
      order by ZIP1, ZIP2, city) as ZIP_ORDER
      from LIBWRK.ZIP_CODES
  ) as ZIPs
  where ZIP_ORDER = 1

Also, it is not possible to perform all queries over this view:

create view LIBWRK.ZIP_CONVERTION as (
  select ZIP.ZIP1 as ZIP1, ZIP.ZIP2 as ZIP2, 
    ZIP.cityDescricao as cityDescription,
    ZIP1 || '-' || ZIP2 as ZIP_CODE,
    substr(ZIP1, 1, 2) as ZIPinit,
    case 
       when ZIP.ZIP2 = '000' then 
          ZIP1 || '-' 
          || (select min(x.ZIP2)
              from LIBWRK.ZIP_CODES as x 
              where x.ZIP2 <> '000' and x.ZIP1 = ZIP.ZIP1)
       else ZIP1 || '-' || ZIP2
    end as zip_code,
    ZIP.NormalizedCity as NormalizedCity,
    row_number() over (
             partition by substr(ZIP1, 1, 2), NormalizedCity
    order by ZIP1, ZIP2, NormalizedCity) as ZIP_ORDER
    from LIBWRK.ZIP_CODES as ZIP
);

select * from LIBWRK.ZIP_CONVERTION

works fine, but

select ZIP_CODE from LIBWRK.ZIP_CONVERTION

does not work at all.

But DB2 on AS/400 has a lot more problems. We've still not found them here but have runned into this in previous data migrations:

• query exploded when running a query: sometimes the query runs fine, some times it explodes, and yes, "query exploded" is the exact term that DB2 writes on its log;
• problems with summary querys: the optimizer was unnable to optimize de query and it tooks so much time it it becomes impossible to run such queries.

The "pragmatic" solution for all these problems has been creating tables populated with the queries result. In cases like the summaries, it was necessary to build sereveal summary tables with small subsets of the data and then create a view, if possible, or a table that joinned all the pre-calculated data.

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 28 - DB2 Problems

We've already used AS/400 and DB2 on previous migrations.

Our experince tell us that they're not very good when it comes use it as the main data migration machine.

There's a set of problems on DB2 that we were afraid to find again on this AS/400 version. This is the latest AS/400 version and, in the words of the system administrator, currently it may be the fastest AS/400 in Portugal.

Our pessimistic expectations proved to be right. DB2 continues to have problems, with seem to be bugs on its engine.

For instance, executing an query works fine, but creating a view over that same quety is impossible because DB2 crashes with an internal error!

I had to create a new table with data from the resulting query.

I stil have not found the "query exploded" problem, which seem related with DB2 optimizer that crashes when trying to optimize the query.

Our confidance on DB2 on AS/400 is a bit low right now, since we proved to be right when we said that we were expecting problems from it.

Day 9 - Data Profiling

I'm continue to develop the data cleaning function for the honorific titles.
Currently I'm developing a data profiler, a stand alone Java application that queries DB2 via JDBC JTOpen, that has already found some anomalies in the person contact name. There's the expected typos, some ad-hoc markers, and finally, the usual user creativity.

These user creativity anomalies are the funniest of them all. In the person contact name there's random chars, like "USO USSSSS", training data, like "TEST" and totally nonsense person names, like "EXITING NOW", "ALSO EXITING" and "NEW".

Day 6 - Setup for First Mapping

The plan for phase has rolled out and we've prepared for the first mapping meeting tomorrow.
We've also set up the development environment on the computer that has arrived.
My computer has not arrived yet, and that will become a problem if it does not arrive in the next two days.
We have also requested for a new data loading into DB2, but this time with the structure will be preserved.

Day 3 - Target specification

The target specification arrived today.
It's an hierarchical structure that will result in a flat file where each line will represent a specific piace of information, identified by a code.
We've started to work on it, but since the specification is more like a meta-model, we had to make some adjustments to comply with such a dynamic structure.
All problems have been solved, they were actually simple decisions that the source team helped to make.
The DB2 model has been created and our scripts turned it into mapping sheets, that will be used in the functional mapping performed by the source and the target teams.

A piece of the source data has become available. It has been extracted from the OS/390 in EBCDIC and in text formats in order to allow some AS/400 loading tests.
The data looks correct, but each row has been loaded as a single record, the copybook rules have not been applied and therefor, it will have to be loaded again. Nevertheless, the fact that the information looks correct is a good enough as the result of the first try.

The first workstation also arrived today, allowing us to install and configure some of the software required for the data migration, and allowing us to access to the development AS/400 machine.

It will not depend on us, but if next week things work out the way we've planed, we'll be able to recover the one day delay.

Day 1 - Kick off

The project has been officially kicked off.

There was the traditional meeting where all teams where present and an overview of the project was presented. All teams involved have previous data migration experience, which is good.

The project should be completed around mid October, but I felt that management confidence to comply with the date is not very high. This is not a bad signal as one might think, since management showed that it is aware of some real difficulties. Those difficulties may become technical challenges and may delay the project.
Nevertheless, I do believe that the original due date can be achieved.

There will be two phases. The first phase, the current one, will use a small entity subset in order to test the performance of the ETL and to tune the process.
The second phase will be the continuity of the previous phase, but now including all applications and entities.


The project itself consists of migrating the current insurance application, running on OS/390 mainframe, into the new system, the GIS Non-Life, running on AS/400, all using DB2, SAM and VSAM.
There are some other satellite applications running on SQL Server 2005 on Windows.
The ETL tool will be Data Fusion and it will run on AS/400 under the Qshell.

The ETL process will be implemented in a slightly different way from the usual scenario. Usually the data is extracted from the source database and loaded into the target database. This is not the case in this project.
Eventually, the data will be loaded into the final target database, but there's an intermediate step. The ETL process performed by Data Fusion will deliver the data on flat files that will be later consumed by the GIS loader. The GIS loader will validate and load the data into the final database.

Since our (ETL team) experience tells us that performance on AS/400 may be a problem during the ETL process, there was some discussion over the performance tests to be performed during phase one.

If the performance becomes an issue, the data migration may have to be incremental, instead of one-shot. This will be a problem because the source team cannot identify which data has been changed and needs to be migrated again to refresh the target system. One challenge that has been given to us, is to think in a fast way to identify if a record has changed and needs to be migrated again, just in case of a B plan is required.