Source : 17
Mistakes that ETL Designers make with Very Large Data -
Vincent McBurney
These are
the mistakes that ETL designers can make when processing scary high data
volumes.
Dan Lindstedt is a very large data
guru and he has a series of outstanding blog posts on very large databases, the
latest is ETL Engines: VLDW & Loading / Transforming. I first
came across Dan on ETL/ELT forums where he has over 20,000 forum
posts. He popped up on B-Eye-Network blogs. Dan has had no comments
on his latest post series yet as B-Eye don't have a reader friendly design
and it discourages reader participation. For example I just got three
comments over a weekend on three old archived ETL posts. ITToolbox is a
friendly place for reader participation.
My favorite part of Dan's latest post
is the 17 mistakes that ETL Designers make with very large data. In fact
that's the title I would have gone for! For some reason blog titles with
a number in them attract more hits. I've shown Dan's list of 17 below
with my own comments on how that impacts DataStage developers. I would
love to hear your own contributions for common ETL design mistakes.
1) Incorporating Inserts, Updates, and Deletes in to the _same_ data flow / same process.
Agree 100%. I believe in have
at least three bands in your processing: Extract from source to file, process
file to load ready dataset and load load ready dataset. For a standard
target table load I would have an insert job, an update job, a delete job (if
needed), a bulk load job (for large volumes).
I can see this working well for
smaller volumes - and the combination of DataStage accessing data from multiple
systems via a Federation Server plugin is intriguing, but this type of cross
database joining would be nasty on very large volumes. Pulling the
smaller data volumes into target tables or lookup datasets would be faster.
3) Targeting more than 1 or 2 target tables
Agree 100%. You could take it
to two tables if there is a very close parent/child relationship like a master
table and an attribute table, or a header table and a detail table. But
that's the exception, most large volume ETL jobs should be preparing data for
one target table.
4) moving rows that are too wide through a single process
I don't know how you get around this
one. If you have a lot of columns then you gotta get it in there!
5) loading very large data sets to targets WITH INDEXES ON
DataStage makes this easy to manage,
on a standard database tab (insert or bulk load activities) you can use the
before-SQL tab to turn indexes off and the after-SQL tab to turn them back
on. The statements on those tabs are run just once for each job (and not
per row). You don't need indexes and keys if you have other ways to check
your referential integrity.
6) not running a cost-based optimizer in the database
7) not keeping statistics up to date in the database
8) not producing the correct indexes on the sources / lookups that need to be accessed
Examples of why you need to be in the
good books with your DBAs!
"My DataStage job is running
slow, I think there is something wrong with the database table."
"You sure it's not
DataStage?"
"Can you check the table, maybe
run a trace?"
"What is DataStage?"
"Could you just check the table,
I've got the job running right now!"
"I ran a query on the table and
it's running fine."
"Could you maybe get off
Facebook and do a friggen trace!"
9) not purchasing enough RAM for the ETL server to house the RAM caches in memory.
DataStage Parallel Jobs need a lot of
RAM for the lookup stage. The IBM Information Server Blade starts
with 4G of RAM per blade for two dual core CPUs on each blade.
10) running on a 32 bit environment which causes significant OS swapping to occur
11) running on a 32 bit environment which causes significant OS swapping to occur
12) running on a 32 bit environment which causes significant OS swapping to occur
I think the treblification of this
one is an OS swapping joke. I am going to be talking about
64 bit processing with the IBM architects when I meet them at IoD 2007 next month. The Information
Server can run on some 64 bit environments but it will be interesting to find
out what plans IBM have for expanding this.
13) Trying to do "too much" inside of a single data flow, increasing complexity and dropping performance
This is a tricky one - kind of like
rows that are too wide - sometimes you need to do it and you hope the massively
parallel architecture is up to it. DataStage can make almost every type
of stage work in parallel so it can get away with extra steps, however this
does make the job harder to debug and sometimes you get those random out of
resource errors...
14) believing that "I need to process all the data in one pass because it's the fastest way to do it." This is completely false, multi-passing the data can actually improve performance by orders of magnitude. IF parallelism can be increased.
I'm not sure what Dan means by
multi-passing and I'll ask in his comments thread.
15) Letting the database "bounce" errors back to the ETL tool, dropping flow rates and throughput rates by factors of 4x to 10x.
More detail about this is in Dan's
post. If you try to trap database rejects in a Server Job you use a
reject link from a Transformer prior to the database stage. Dan points
out that each reject row slows down the job by a factor of 4 as the ETL job
stops processing to handle that reject row.
Parallel Jobs are more efficient as
they use a reject link out of the database stage and on a parallel architecture
can push the handling of those rows into a new process. I haven't seen
any overhead in this design if you don't get rejects, and database rejects
should be under 0.01% of your rows or else your design is faulty.
I've never try to trap bulk load or
multi load or tpump or any other type of native database load errors back in
DataStage and I don't think the stages can accept them anyway, I let the
database handle them. On smaller volume jobs I use the database reject
link and insert instead of bulk load a lot for a more robust auditing of rows.
16) "THINKING" in a transactional mode, rather than a batch mode, and processing each row, one row at a time (like they would code a cursor in a database language).
A bit hard to avoid in a
row-by-row ETL tool! The parallel architecture and caching and memory
sharing and a bunch of other things make it fast.
17) LOOPING inside an ETL process, because they think it's necessary (transactional processing again).
Fortunately this is very hard to do
in a DataStage job or people would misuse it! In DataStage looping on a
per row basis can be done via lookups to database stored procedures or custom
code modules but most standard DataStage stages do an action per row.
Think outside the box.
A lot of ETL sites have templates for
building new jobs or design standards or example jobs. These are
excellent for 90% of your ETL work however very large data jobs may need custom
designs. You should look at what has been done in other jobs but also be
ready to take a job into performance testing to try out dozens of other
configurations. As Dan says in his post - "performance and tuning at
these volumes usually means going contrary to the grain of what you've
typically learned in building ETL load routines".
njoy the simplicity.......
Atul Singh
