Code generation from datastage to SQL

yousuff1710 · Post by **yousuff1710** » Tue Jun 30, 2015 5:06 am

Hi All,

I have a very challenging question, it may be bold but it will be very helpful & effective solution if its possible/available;

My question is:-
1) Is it possible to convert datastage code of the entire job to SQL code in script? I doubt there's no such convertor tool as of now

2) Also, Is it possible to convert mapping specifications/requirements sheet in to SQL script? Here, may be we have to design the mapping sheet first in such a way that it can be convertible automatically to SQL script.

Please share your ideas on this, let me know if any tools can be made use of?
We require this, as our ETL process is performing bad compared to ELT. We are going to convert ETL jobs to SQL scripts to do transformations.

Thanks in advance,
Yusuf

ShaneMuir · Post by **ShaneMuir** » Tue Jun 30, 2015 6:21 am

yousuff1710 wrote:We require this, as our ETL process is performing bad compared to ELT. We are going to convert ETL jobs to SQL scripts to do transformations.

Or maybe you have badly designed ETL jobs?

If your jobs are just a straightforward lookup to a table in the same DB and schema then it is quite possible that SQL would be faster.

But if you were doing anything more complex than that (ie multiple transformations, conditional changes to data etc) a well designed ETL job should be quicker.

Of course each scenario is different, and a judgement call needs to be made each time. There are many factors which can make an ETL job 'slow' when compare to a purpose built SQL process, (eg network speeds, o/s storage, memory availability, data volumes etc)

But to answer your question - not that I am aware of. There are methodologies out there which do similar to what you suggest, but they rely on the requirement for the SQL design being known before development begins. (ie they are purpose built in SQL).

yousuff1710 · Post by **yousuff1710** » Tue Jun 30, 2015 6:40 am

Thanks for reply Shane.

Anybody, if you are aware of such methodologies as mentioned by Shane, please let me know the details.

chulett · Post by **chulett** » Tue Jun 30, 2015 7:02 am

I'm going to agree with Shane's first question if you feel the need to use SQL to 'do transformations'. Has your company considered bringing in someone to review your job designs and ETL methodologies? Seems like it might be a prudent investment before spending time and money on an alternate plan when you already have an investment in a tool more than capable of doing the job.

Your issue could also be aggravated but running a very old version of the product that upgrading could help with in you really are running an 8x release. And of course hardware and topology play a part here.

I'm not aware of any such methodologies.

ShaneMuir · Post by **ShaneMuir** » Tue Jun 30, 2015 7:38 am

Just to be clear, when I mentioned methodologies - I was referring to using SQL to do transformations in conjunction with ETL tools (eg use Datastage to load all your data into canonical models in the same db as your mapping tables, then create view which performs those mappings to use as the inputs into subsequent load DS jobs). I was not referring to a methodology to convert DS jobs to SQL.

PaulVL · Post by **PaulVL** » Tue Jun 30, 2015 8:03 am

Have you thought about looking into Balanced Optimizer?

yousuff1710 · Post by **yousuff1710** » Wed Jul 01, 2015 12:44 am

Yes Paul, I have read about that but never got a chance to work on that till now as it is an add-on.

qt_ky · Post by **qt_ky** » Wed Jul 01, 2015 8:47 am

chulett wrote:Has your company considered bringing in someone to review your job designs and ETL methodologies? Seems like it might be a prudent investment before spending time and money on an alternate plan when you already have an investment in a tool more than capable of doing the job.

I would second Craig's suggestion.

kduke · Post by **kduke** » Sat Jul 04, 2015 9:53 pm

I wrote something like this for BellSouth. They were converting from DataStage to Oracle stored procedures. I wrote it in BASIC. I doubt if it would work now even if I had the code. That was version 6 of server.

I would imagine you could do something similar by exporting your jobs to XML. Then you need to parse your XML. I would name the cursor the same name as the link then formulas in the transforms would work. Any calls to DataStage functions would have to be hand coded. They could not run the code generated but they could read it and understand it enough to take it and write the new Oracle from reading it. Saved lots of time. The jobs back then were not complex and had just a few stages.

So every source stage becomes a cursor with a loop around it. I lookup was a lookup cursor. The transforms were done by just setting variables equal to the derivations. The variables were created from the target table. So you have figure out data types from what is in the XML. I created a lookup. So a 1 got translated into char and a 2 into a varchar as examples.

Good luck. Not a simple task. I would optimize yur jobs before I would do this.

DSXchange

Code generation from datastage to SQL

Code generation from datastage to SQL

Re: Code generation from datastage to SQL