DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
rameshrr3



Group memberships:
Premium Members

Joined: 10 May 2004
Posts: 609
Location: BRENTWOOD, TN
Points: 6937

Post Posted: Tue Apr 12, 2016 6:35 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Additional info: The Debate revived
I'm at a site where we have two databases on Exadata. Although I have a DataStage bias, I would have to admit that ODI probably does a better job at pushing things down to Exadata where the processing is Oracle centric or performance limitations are based on Oracle side. This helps us avoid moving data through a network, and for that matter avoid data moving through a third server (DataStage) when its being sent from one Oracle server to another.

Apart from Exadata, we also have other databases and data sources
like Teradata, DB2/Z, Informix , and MQ ASCII files. We also have a fairly well established data stage infrastructure (SMP cluster) which will be upgraded to 11.3 starting this year end.

Now of course we do have the Balanced Optimization add-on in DataStage,
so here are some questions from our in-house Oracle Architect to see if it makes sense to acquire an ODI license when we already have DataStage with the option of adding on Balanced Optimization?

'It' here refers to DataStage with Balanced Optimization.

1. Do you find that if you follow the recommended patterns to push as much processing to the database, Balanced Optimization will be able to result in a 100% ELT outcome?
2. What are some real-world results using it? Do you find that only ideal cases are 100% converted leaving the majority to be mixed or 100% DataStage?
3. Working with partitions in Oracle it seems it only supports INSERTS, which really is something the database does automatically (finds the right partitions). Can it work with advanced functions such as Partition Exchange, or compressed tables (which really require INSERT only code)?
4. Handling of Indexes and constraints (DISABLE, ENABLE, UNUSABLE, REBUILD, etc) – is it able to perform these without extensive manual configuration or coding?
5. What do you think are its major strengths (ideal cases) and weakness (non-ideal cases)? Are there any areas where DataStage Balanced Optimizer shines compared to ODI ?

Of course not everyone here has the required PL/SQL expertise, but from what I hear ODI uses PL/SQL for its ELT features.

My personal opinion is ODI is better than DataStage so long as the workloads are Exadata centric , with their infini band network etc, but when we get in to the big bad world of heterogeneous data sources and custom user defined stages etc, its capabilities give way to DataStage. I believe if both can co-exist - it would be a win-win situation. I also believe with a suitable CWM XMI format metabroker, DataStage should be able to import and metadata stored in ODI and help with the metadata management problem.


Thanks
Ramesh
eostic

Premium Poster



Group memberships:
Premium Members

Joined: 17 Oct 2005
Posts: 3764

Points: 30158

Post Posted: Wed Apr 13, 2016 4:34 am Reply with quote    Back to top    

To address the co-existence issue, consider all the ways that ODI could be illustrated within Information Server for lineage and governance, using the OpenIGC API ...where you can create your own clas ...

_________________
Ernie Ostic

blogit!
Open IGC is Here!
Rate this response:  
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2768
Location: USA
Points: 20827

Post Posted: Wed Apr 13, 2016 7:21 am Reply with quote    Back to top    

I got the impression from the IBM web site that DataStage Balanced Optimization features are included in version 11.5. Previously it was an additional cost option.

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42574
Location: Denver, CO
Points: 219079

Post Posted: Wed Apr 13, 2016 7:29 am Reply with quote    Back to top    

My biggest question right now is - where is part 1? A (mostly) honest question as I figured it must have been something I didn't recall reading but a search failed to turn any such thing up. Hence the question. Wink

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
asorrell
Site Admin

Group memberships:
Premium Members, DSXchange Team, Inner Circle, Server to Parallel Transition Group

Joined: 04 Apr 2003
Posts: 1669
Location: Colleyville, Texas
Points: 22716

Post Posted: Wed Apr 13, 2016 11:26 am Reply with quote    Back to top    

rameshrr3 wrote:
1. Do you find that if you follow the recommended patterns to push as much processing to the database, Balanced Optimization will be able to result in a 100% ELT outcome?

Yes - it does a pretty good job of realizing when it can dump everything into the database and creating the appropriate SQL to to do so.

rameshrr3 wrote:
2. What are some real-world results using it? Do you find that only ideal cases are 100% converted leaving the majority to be mixed or 100% DataStage?

We were very judicious in our usage. We were in an environment with lots of databases, so many of our jobs were "mixed" because they weren't Oracle to Oracle. Also, you are assuming that the Oracle system is big enough (and has bandwidth) to out-perform the DataStage server, which may or may not be true. We did have several jobs that were not pushed down to Oracle when testing showed it would be slower because the Oracle server was overwhelmed during that time-frame. It was faster to offload the processing to DataStage and then post the results back to the Oracle server.

rameshrr3 wrote:
5. What do you think are its major strengths (ideal cases) and weakness (non-ideal cases)? Are there any areas where DataStage Balanced Optimizer shines compared to ODI?

All I can do is re-iterate much of what Ernie said earlier. Mixed database environments, or environments with limited SQL skills. Several times I've been paid to take pages of complex SQL and re-write it in DataStage because the existing staff couldn't support the SQL job. They were willing to take the performance hit to get something they could easily modify and extend. Now at 11.5 (with Balanced Optimization included), they can still use the GUI but push the processing back down to Oracle for improved performance. Best of both worlds.

Sorry - don't remember enough details to address other two questions.

_________________
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2017
Rate this response:  
rameshrr3



Group memberships:
Premium Members

Joined: 10 May 2004
Posts: 609
Location: BRENTWOOD, TN
Points: 6937

Post Posted: Wed Apr 13, 2016 11:52 am Reply with quote    Back to top    

Thanks everyone, it will be a couple of days before I revive my 'premium' membership
On Q4

I'm of the opinion that Datastage/BalOpt already does this , IF the options are so selected, but ODI may be doing it a little more intelligently `depending on how [knowledge modules] are configured ( Pardon my ignorance of ODI Smile )

Quote:
4. Handling of Indexes and constraints (DISABLE, ENABLE, UNUSABLE, REBUILD, etc) – is it able to perform these without extensive manual configuration or coding?


If Datastage offers a free add on of Bal.Opt in version 11.5 , would it make sense to go there directly ?
Rate this response:  
rameshrr3



Group memberships:
Premium Members

Joined: 10 May 2004
Posts: 609
Location: BRENTWOOD, TN
Points: 6937

Post Posted: Thu Apr 14, 2016 5:04 pm Reply with quote    Back to top    

@Chulett : (Part 1) is on the same shelf where windows 9 and datastage 10 are Wink

Part1 is just what I consider previous debates on this topic , but did not want to hijack those older threads where I was not the OP.
Rate this response:  
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54221
Location: Sydney, Australia
Points: 294071

Post Posted: Thu Apr 14, 2016 5:13 pm Reply with quote    Back to top    

rameshrr3 wrote: @Chullet : (Part 1) is on the same shelf where windows 9 and datastage 10 are DataStage 10 does exist - it's the engine underneath IBM's Master Data Management server. It ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
rameshrr3



Group memberships:
Premium Members

Joined: 10 May 2004
Posts: 609
Location: BRENTWOOD, TN
Points: 6937

Post Posted: Thu Apr 14, 2016 6:02 pm Reply with quote    Back to top    

Does the internal version of datastage engine have anything to do with the versions of the Universe Engine being release by Rocket UV ? Because the currently marketed version of UV is 11.x. I had an impression the 2 went their own way after UV version 9.
Rate this response:  
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54221
Location: Sydney, Australia
Points: 294071

Post Posted: Thu Apr 14, 2016 6:26 pm Reply with quote    Back to top    

Rocket U2 UniVerse and DataStage engine did indeed go their own ways (although many similarities remain, the development trajectories have been quite different) when IBM took over UniVerse from Inform ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours