Creating a DataStage Job using a high level programming Lang

sumitgulati · Post by **sumitgulati** » Fri Nov 07, 2003 3:21 am

Hi All,

I want to create a DataStage Job (say a simple one to one mapping OR a Batch Job) using a Programming Language (say C++).

Is it possible? If yes then kindly guide me?

Regards,
Sumit

ray.wurlod · Post by **ray.wurlod** » Fri Nov 07, 2003 3:38 am

Such an API was developed in an earlier release of DataStage, but was withdrawn (about release 4.2 or 5.0). It is no longer possible.

Why do you want to do this? What's wrong with the DataStage Designer? How did you propose to manage the metadata?

sumitgulati · Post by **sumitgulati** » Fri Nov 07, 2003 5:22 am

ray.wurlod wrote:Such an API was developed in an earlier release of DataStage, but was withdrawn (about release 4.2 or 5.0). It is no longer possible.

Why do you want to do this? What's wrong with the DataStage Designer? How did you propose to manage the metadata?

Because of some peculiar requirement I wanted this to be done using a front end.

Thanks
Sumit

kduke · Post by **kduke** » Fri Nov 07, 2003 10:10 am

I think you may be able to solve your problem easier than that. You should be able to parameterize the select on a job. This will allow you to completely change the columns as long as the metadata does not change then it would work.

Otherwise it is my understanding that the original MetaRecon created jobs by generating an export file. The DSX file would still need to be imported. You would have to reverse engineer the DSX file. You would have to import it, compile it and then you could run it.

Kim.

kcbland · Post by **kcbland** » Fri Nov 07, 2003 10:18 am

Only for very simple jobs could you do this. If they're so simple, a developer could knock it out very quickly.

Not only would you be version specific, (as each release requires you to update your logic to maintain compatibility) but you would also be unable to tweak jobs, as your source code system of record is "your" code generator. Any tweaks are lost if you regenerate or deploy changes.

What you need to look at is shared containers, job instantiation, and well-defined structured job designs as means to optimize your code work and development.

clshore · Post by **clshore** » Fri Nov 07, 2003 11:00 am

I've done something like this for specific purpose with Perl, but as has been said, 'why?'.

kduke · Post by **kduke** » Fri Nov 07, 2003 1:55 pm

I would love to see that code.

I wrote a DataStage to PLSQL conversion and another one for VB. I have never gone the other way. I might trade.

Kim.

Teej · Post by **Teej** » Fri Nov 07, 2003 5:18 pm

Damn Kim, you must've been very bored that day.

I had to think of how it would be possible, and yes, it's doable, just need some really deep knowledge of where things are (and a kick ass field parsers.)

The other way is tricky, it all depends on how much you want to break down that SQL statement...

However, I might just as well put something together, but not before I see what the DSX formatting is for 7.0 and upward.

-T.J.

clshore · Post by **clshore** » Mon Nov 10, 2003 1:42 pm

Kim,
I'd like to see it too; it's still at the client site, 2 years later, unused now I'm sure. It was a bit messy and problem specific, a 'throw-away'.
We had boilerplate jobs that were created using metadata extracted from the DB dictionary; if and as the modellers changed schema (sometimes daily), we used the Perl script to create a fresh array of DS jobs.
Lot's of procedural code, hand parsing, etc, it grew from a simple script as the scope crept.
If I was doing it now, I'd use the Perl XML libraries, and build it OO.

Carter

kduke · Post by **kduke** » Tue Nov 11, 2003 12:24 am

The PLSQL converter went straight from DS_JOB## in version 4. It would only do simple jobs. Each primary source routine was a cursor. Each lookup was a parameterized cursor. If you know what that is. Each cursor was named after the link name so the derivations would work. (linkname.columnname). Each tranform was a function. All of it was wrapped in a package.

Here is what happened. BellSouth was converting DataStage jobs to PLSQL. They wanted me and some others to lookup the business rules in DataStage to make sure they were in the PLSQL routines. Just as a double check. I got tired of copying from DataStage to an editor so I wrote a BASIC program to pull the SQL, constraints and derivations. I thought about how it might be easy to genrate the cursors from the SQL. A few hours later it was generating a complete PLSQL program.

The VB stuff was a little more direct. I wrote BASIC programs to put row counts from jobs into hash files. I needed these copied down to Access tables. So I prototyped them as DataStage jobs. There are 6 or more jobs so I generated VB from DataStage metadata that is in Reporting Assistant. The generator is a part of DwNav. It is hidden. It does not handle constraints or multilpe tranforms but it could be fixed. Each link is a VB class. Each column is a property in this class so derivations work.

I am just lazy. This stuff is fun. I love reverse engineering stuff. I wanted to create an Ab Initio or Informatica to DataStage converter. Maybe someone could pay me.

I have created a lot of tools like this to generate ctl files for Oracle bulk loads or DDL for create tables or create indexes. DwNav was designed to add functionality like this easily. It contains a complete metadata browser. Before MetaStage I wanted to pull all the table definitons from Oracle and SQL Server to do reports or browse in DwNav. If this is important then these programs could easily be modified for anyone. Metadata should be used to generate Java or C++ or DDL. Metadata is not very valuable if it is not used to help build programs. If all you do is view metadata descriptions then not as useful as using it to design or prototype new systems.

You are right. I do get bored if all I do is build jobs. I always think in terms of how can I do this faster the next time around.

If you download DwNav then I can email you how to enable the VB program generator. The PLSQL one only works on Ds 4. I would have to do some work to make it work with DS_JOBOBJECTS. It may be easier to rewrite it to work like the VB version off Reporting Assistant tables. If anyone wants or needs this stuf then let me know. It is funs stuff.

Kim.