How long time to need to start with DB2 64 partitions

[dhwankim]

Hi All

Now I am participating DW Project with DataStage PX and DB2 BCU Architecture.

This system will Near Real Time DW with huge volumn data.

So DBA draws 64 Partition Configuration on 8 SMP Machines.

My Concern is how long time to need to start DS PX Job with DB2 Enterprise Stage.

Does Any body have a experience with 64 Partition DB2 Archiecture.

Batch window is 20 minutes with 1000 more jobs.

So If DataStage PX start time takes long time(?). It should be affected ETL Performance.

I really really want to actual information.

Now I do not have Product machine. so I could not test this environment.

Please Help Me.

Thx in advance.

[hamzaqk]

it would depend on several factors such as how good your hardware is, how well your jobs are designed / tuned . i dont think there is a general answer to this.

[hamzaqk]

it would depend on several factors such as how good your hardware is ? network ? how well your jobs are designed / tuned ? . i don't think there is a general answer to this.

[ArndW]

The startup time isn't as important as the high-performance throughput you are going to get by using a 64-way partitioned database. PX Jobs should be designed to process a lot of data. If you are worried about startup time then perhaps you should be using server jobs instead of PX jobs for those jobs whose ratio of startup time : processing time is high.

[kcbland]

You have all choices in partitioning from sources and thru transformation (any target lookups are the exception), it's only when loading into your 64-way do you need to consider aligning your data to the database partitioning scheme.

[dhwankim]

Hi

Now IBM has suggested Db2 BCU Architecture with datastage EE.

My role is a design of ETL Architecture with this platform.


The Platform is below =============
It consists of 10 P570 Macnines.
One of Machine is DB2 Admin BCU and another is ETL BCU (ETL Main Machine, ETL Main machine has 12 core (24cpu))
and others are Data BCU.

My Designe for General ETL Archiecture.
Generally, I will use 2 degress of Parallelism for PX Jobs. Because
I have run 1000 more jobs in 20 minutes batch window every hour.
and I will use transformation processing in ETL BCU Machine Only.

Big table has 20 billion record (About 10 tables have that volumn).

but generally Most of txn tables is fed 1 million record less in each Batch Window(20 minutes). (All handling records of tables is about 30 million in 20 minutes)

and during ETL Batch Time, I have to service data for Biz user.
so All Load mode is upsert like record level handling not load mode.
and I have to give low stress to DB2 during ETL Time.

Frankly speaking, My Concern is that It is possible to do ETL in the batch window with DB2 BCU Architecture.

so I want you to share your exprience with DB2 and DataStage.

Thx .

[ray.wurlod]

Hello DaeHwan,

If you are concerned that the time window might be a problem, go back to IBM and ask them on what basis they recommended this particular configuration?

There are several very large corporations using 24+ nodes for DB2 (with large DataStage configurations such as 24 nodes in the DB2 node pool and more nodes allocated for processing). Data volumes (and execution time windows) are achieved comfortably.