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 58 - New Mapping

A new mapping meeting has been performed.
The new mapping is easy, since it's actually the same one that's already implemented.

Unfortunately, new mappings will not be available soon. Looks like the target system specification is way long to be closed, and I'm getting the impression that the project timings will slide...

Day 50 - New Mappings Posponed

Since the first stage has over, we're now expecting for the meetings to map the new entities.
Unfortunately, the target team still does not have the product fully configured, it looks like the requirements are not fully closed yet.
Therefor, the new mappings have been postponed.


./M6

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 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 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