Generate jobs

[akrzy]

Hi,

I'd like to know how can I generate job's structure without using DS Designer.
Do you know any language or method to do that?

I know that we can import .dsx or .xml files but first we have to create nad export jobs from DataStage to create this files.

For example, I would like to build this files from the begining.


Do you have any knowledge or idea how to do that?


Regards,
Anka

[ArndW]

Hello akrzy,

the answer to your question is "no, it is not possible". In reality a number of things will happen in the background and within the DataStage engine when you either create a job in the graphical designer or import it through the manager and there are just too many complex steps to perform manually.

[Sainath.Srinivasan]

The export of datastage can be compared to an archive of Oracle. It is designed and developed by the engineering team who developed the product. Also this changes with the version, components installed in the project, option selected while export etc. Hence to replicate this will be identical to make a fixed roadmap to go to a moving target.

[akrzy]

Ok, I understndn that there is a lot of things in the background.
But...
I would like to move "the desription " of the job into Designer and then to create it in the Designer.

I need some way to auto create jobs from some kind of description, not using graphical interface.

[Sainath.Srinivasan]

Did you try using MetaRecon. It does a 'source system analysis' and produces simple DataStage jobs to replicate what is accepted by the analyst.

[Alexandre]

Another possibility is to reverse engineer the xml file generated.
Then, based on some template job (for example) you can create an xml file that suit you needs and import it using the manager...
This way, you can create job without using designer, but it is really tricky.

[akrzy]

Another possibility is to reverse engineer the xml file generated.
Then, based on some template job (for example) you can create an xml file that suit you needs and import it using the manager...
This way, you can create job without using designer, but it is really tricky

Ok,
I don't know if I understand you.

Because I know that to have a template job, first you have to create the template into Desigenr. And I need to create job from the begining without Designer.

Or maybe you have any description of this xml file?

[peternolan9]

Hi,

I'd like to know how can I generate job's structure without using DS Designer.
Do you know any language or method to do that?

I know that we can import .dsx or .xml files but first we have to create nad export jobs from DataStage to create this files.

For example, I would like to build this files from the begining.


Do you have any knowledge or idea how to do that?


Regards,
Anka

Hi Anka/All,
this is a very good question and one that took up a large slice of my time a while ago. I implement Sybase IWS for a living at the moment and one of the problems is that the ETL must be completely re-written for each site because the cost of changing the ETL is higher than the cost of writing it from scratch in the first place. This is the case for both INFA and DS.

So, being the kind of person I am I investigated the possibility of writing an application that would read the source and target data models and generate DS/INFA jobs to perform the required processing.

It took 2 weeks but my investigation was that this is certainly possible, and not even that difficult, there is just a LOT of XML manipluation to perform. This is because the way to go about it is to generate a DS XML job and then load that job into DS. (Or INFA, they are pretty much the same..)

Certainly, I could write such a program in 4-5 weeks in VB.net, no sweat. (I have published a program that reads DS XML to edit it on my web site.)

Then I looked at the problem of 'how might I get paid for being ingenious enough to do this?' Always a concern for an independent....

My conclusion was that if I were to try to charge a fee for that software I would be asking an IT manager to ask his boss to pay money for a productivity tool to make his expensive, world leading, productivity tool (DS) more productive........and there is not an IT Manager on the planet who would be interested in asking his boss for any serious money for that.

So, I then thought about providing the software as a 'web service' where the client supplies some information and the web service sends back the finished job. As a web service the fee could be very small per job. Like just a few dollars.

The problem with that approach is that if it becomes popular all I have done is develop a revenue stream for Ascential because they could replicate it and offer the same service. There was no 'barrier to entry' for Ascential.

So I gave up on the idea of generating DS jobs in XML. If anyone wants it they have to ask ASCL to do it I would guess.

To solve my problem (loading IWS) I wrote my own ETL tool instead. It discovers that data model at run time and separates processing rules from the data structures involved. That makes it more productive than DS, though it is clearly not a replacement for DS.... ...if the client chooses DS/INFA to implement IWS, I actually make more money because of the extra time it takes to develop jobs in DS/INFA.

Lastly, I've mentioned here a few times, we do a lot of editting of our DS jobs directly in XML nowadays. The Designer GUI just slows us down.

What we do is structure templates for certain types of processing. Then we unload the template, perform a whole bunch of 'change alls', reload the job, polish it up as we need to and then finish it.

We are able to build about 20 type 1 dimension processing jobs per work day like this and 10 fact table processing jobs per day. This is a coding rate I have not heard of at any other site. But I did spend a lot of time agonising over the template. Since we have 60+ dimension tables now with the 60+ jobs to load them, making a mistake in the template would require us to change all 60 jobs.

When I see ads for '10 DS programmers' I wonder what the hell they are all doing and why anyone would want to hire that many. We just built a Data Mart based on Oracle Applications with 3000+ fields moved with 8 work weeks of DS coding. I have just done an estimate for a very large and complex EDW job and I think we are looking at 16-20 work weeks for all the DS coding....so we are going to have 2.5 DS people.

By the way, this collapsing of processing with data models is one of the things I do not like about all the ETL tools. I mean, it is not even 'computing science 101' to know that systems are easier to maintain if you separate data access from processing logic. It is a very basic concept, not used by all the repository based ETL tools.

In my very first project in 1982 we had 'data access modules' to IMS to separate data access from business logic....and here we are 23 years later building systems in tools that collapse business logic with data structure of the inputs and outputs so that we need a separate set of processing rules just because we have separate sets of inputs and outputs....hhhmmmm

[Alexandre]

I wasn't talking about designer manipulation but about "pattern extraction".
In order to reverse engineer the XML format, you need some example.
From them, you can extract usefull information like : how are the stage defined, how are the link defined and so on...
Once you found out the structure of the xml, you can modify/create job directly in XML.

What I called "template" in my previous post is the pattern you will use to create structures in you XML file.

The problem with the XML file is the relations between the different structure, and in order to avoid inconsistency, you need to load the whole structure... i.e. recreate an ETL...

I don't think it can be interesting for general purpose, but maybe, in some limited areas where you can create jobs based on a "library of template", like a job toolbox, it may be powerfull enough without the pain of an huge project...

[peternolan9]

Another possibility is to reverse engineer the xml file generated.
Then, based on some template job (for example) you can create an xml file that suit you needs and import it using the manager...
This way, you can create job without using designer, but it is really tricky

Ok,
I don't know if I understand you.

Because I know that to have a template job, first you have to create the template into Desigenr. And I need to create job from the begining without Designer.

Or maybe you have any description of this xml file?

Hi akrzy,
how would you propose to know what to create in your xml document without first creating a template job similar to what you want to create and then reading/understanding the XML in the template?

To create a DS job in XML you need an excellent understanding of XMLDocument manipulation in whatever language you use and an even better understanding of how DS represents a job in XML....it's not rocket science, but there are hundreds of xml expressions in a reasonably complex DS job....and you need to know what they are doing if you are going to generate them with code.