Datawarehouse on AS400 + DB2 2

[Hans63]

Does anyone out there have experience (good or bad) in building a datawarehouse on AS400 and DB2?

Any relevant links will be appreciated as well.

We have to make a decision for the infrastructure of our datawarehouse. Choices are AS400 + DB2 or Microsoft SQL server.
We are using Informatice powercenter as ETL tool and BO as reporting tool.

 

[RiverGuy]

I've never seen it done on AS400. I've used AS400 as a data source to a SQL Server data warehouse plenty of times. I'd have to think that TCO for the AS400 solution would be much higher than SQL Server for the same performance.

 

[MDXer]

AS400 has and can support a DW. Back when EssBase was one of the main MOLAP tools IBM and EssBased had teamed up if you went to either one for a BI Solution they brought in the other. TCO & ROI seem like they would be factors to consider in making the final choice.

If you are using Informatica as your ETL and BO as your reporting tools then you would probably want to factor these costs into your decision as well.

If final decisions haven't been made then I would give MS SQL 2008 serious consideration. You could lower your TCO by leveraging the tools withing SQL Server. I belive SSIS is more than capable to handle average to large data volumes. When you put BO against The MS BI Suite I believe BO will lose. MS has SSAS which can utilize a MOLAP storage engine as opposed to ROLAP increasing query performance. With Reporting Services, Proclarity and Performance Point you cover all reporting needs. Significant improvements have been made in the 08 reporting services capabilities.

 

[blom0344]

Some years ago I was forced to move my datamarts from DB2/windows to DB2/AS400. At the time I was using Powercenter 6.2.2 as an ETL-tool.

Basically the move was something of a nightmare. The biggest issue was with INFA not being able to perform solid block inserts in AS400. We were forced to change a server setting to switch to single row inserts. Load times tripled due to the increased overhead.
I was also forced to use AS400 native tools to generate tables , cause there was a large community that needed the query-builder that comes with AS400.

Apart from all the AS400 tooling I had to learn, the optimizer left me in shock compared with DB2 7.1 on Windows.
I have forgotten the details , but it took a lot of work to get some performance back (AS400 was a beast compared to the little 512 MB Windows server I was used to)

The conclusion:

DB2/windows/Powercenter was a pretty good combo, really enjoyed working with DB2 on that platform.
However Powercenter with SQL Server really flies! Please be rational and use this setup If you can.
AS400 has many good features, but it is just not the proper choice when working with DWH/Powercenter.

Ties Blom