As part of our new requirement, we are planning to convert all our datastage jobs to DB2 stored procedures.
Please let me know if there is any tool available in the market to do that conversion easily.
Datastage to Stored Procedures conversion
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 1
- Joined: Sun Nov 07, 2010 1:53 am
You mean that seriously? Really?
If you just use DataStage to push data from one table in DB2 to another, it may be theoretically possible to do such a thing. But if that should be the case, why did you decide to use it in the first place?
I can't believe that anyone could be bothered to develop a tool for a conversion like that, because with the width of options and functionalities available within DataStage (or any other competing ETL-Tools) the whole operation is bound to fail in most environments.
If you just use DataStage to push data from one table in DB2 to another, it may be theoretically possible to do such a thing. But if that should be the case, why did you decide to use it in the first place?
I can't believe that anyone could be bothered to develop a tool for a conversion like that, because with the width of options and functionalities available within DataStage (or any other competing ETL-Tools) the whole operation is bound to fail in most environments.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It's called a brain.
It's a tool that can be, and usually is, manufactured using unskilled labour initially but which needs to be tuned by the best possible practitioners.
"If you can read this, thank a teacher!"
It's a tool that can be, and usually is, manufactured using unskilled labour initially but which needs to be tuned by the best possible practitioners.
"If you can read this, thank a teacher!"
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Datastage to Stored Procedures conversion
<rant>Resist stupid requirements!</rant>manikandan.kulanghat wrote:As part of our new requirement...
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
We often get asked to convert unfriendly database code to DataStage but almost never the other way around. If they are doing this for performance reasons they should look at adding DataStage Balanced Optimiser - this will let you turn DataStage jobs into DB2 code automatically by pushing functionality down into the database via the Balanced Optimiser. It runs inside the DataStage Designer. This allows some functions to remain on DataStage - such as opening files and opening source databases and transforming data - and it allows the push down of some functions to DB2 such as aggregation and set based transforms and temporary table use.
If you are looking to provide more DB2 control over the data load then look at upgrading DB2 to InfoSphere Warehouse so you can use DB2 Data Flows to call DataStage jobs.
If you are looking to save money then you are going down a wild goose chase. You will end up spending more time and money building and supporting DB2 stored procedure loads then you will ever spend on annual DataStage costs. It's safer to stay with what you have and if you are having problems with it then use a DataStage expert to solve those problems. A few things will work against you - you will end up spending hundreds of thousands of dollars migrating the code and it will have provided you no new business benefits. You will decommission the old DataStage environment but discover you now have DB2 sizing issues and need to make your DB2 environment larger - because processing and transforming data on a Database can be more expensive than an ETL server due to the higher cost of CPU and RAM utilization on database engines. You will find the code more costly to maintain and if you ever want to switch databases - say to move from DB2 to Netezza or DB2 to Oracle - you are completely screwed. On DataStage you can switch databases, in DB2 code you cannot.
If you are looking to provide more DB2 control over the data load then look at upgrading DB2 to InfoSphere Warehouse so you can use DB2 Data Flows to call DataStage jobs.
If you are looking to save money then you are going down a wild goose chase. You will end up spending more time and money building and supporting DB2 stored procedure loads then you will ever spend on annual DataStage costs. It's safer to stay with what you have and if you are having problems with it then use a DataStage expert to solve those problems. A few things will work against you - you will end up spending hundreds of thousands of dollars migrating the code and it will have provided you no new business benefits. You will decommission the old DataStage environment but discover you now have DB2 sizing issues and need to make your DB2 environment larger - because processing and transforming data on a Database can be more expensive than an ETL server due to the higher cost of CPU and RAM utilization on database engines. You will find the code more costly to maintain and if you ever want to switch databases - say to move from DB2 to Netezza or DB2 to Oracle - you are completely screwed. On DataStage you can switch databases, in DB2 code you cannot.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Premium Member
- Posts: 353
- Joined: Mon Jan 17, 2011 5:03 am
- Location: Mumbai, India