Datastage to Stored Procedures conversion

[manikandan.kulanghat]

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.

[BI-RMA]

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.

[ray.wurlod]

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!"

[ray.wurlod]

As part of our new requirement...

<rant>Resist stupid requirements!</rant>

[vmcburney]

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.

[SURA]

Sometime Developers are helpless and the Mangers / Lead who used think link "Genius" used to take these type of decision.

All i can say "BEST OF LUCK"

DS User

[evee1]

I too wonder what is the rationale. Can you tell us a bit more?

[chandra.shekhar@tcs.com]

@SURA

Lolz.