Hi
I'm a begginer to data satge
Plz may i know how can a batch job be coverted into a server job with less number of stages. plz...may i know the extent to which pl sql can be used in data stage jobs.ie., the looping concepts applicable to data stage server jobs
Usage of PL sql In Data Stage server Jobs ( Not Batch Jobs)
Moderators: chulett, rschirm, roy
Hi Uma,
Welcome Aboard :D !!!!
Though your post is not clear, I assume, your intention is to convert PX job to Server jobs. But why?
If you intend to convert, obviously it will be less no of stages. Because, the available no of stages is very less. And most of the functionality is aceieved through Transformer stage.
And rest of the questions cannot be comprehended.
Welcome Aboard :D !!!!
Though your post is not clear, I assume, your intention is to convert PX job to Server jobs. But why?
If you intend to convert, obviously it will be less no of stages. Because, the available no of stages is very less. And most of the functionality is aceieved through Transformer stage.
And rest of the questions cannot be comprehended.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Welcome Aboard,
Batch jobs, in Datastage, is a collection of DataStage jobs coupled into a batch job. You can also write your own for more functionlity. Changing a batch job into a single datastage jobs means doing everything a batch job does in a single job. Not advisable, not recommened. Keep the jobs modularized and if they are in batches leave it like that.
Pl/Sql can be used as a seperate database procedure, called by STP stage or inside a database stage.
Start Loop activity and End Loop Activity in sequence jobs can loop a piece of code/job etc. Looping can also be incorporated in Basic code in a Batch job.
My 2 cents.
Batch jobs, in Datastage, is a collection of DataStage jobs coupled into a batch job. You can also write your own for more functionlity. Changing a batch job into a single datastage jobs means doing everything a batch job does in a single job. Not advisable, not recommened. Keep the jobs modularized and if they are in batches leave it like that.
Pl/Sql can be used as a seperate database procedure, called by STP stage or inside a database stage.
Start Loop activity and End Loop Activity in sequence jobs can loop a piece of code/job etc. Looping can also be incorporated in Basic code in a Batch job.
My 2 cents.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
You may want to rephrase your questions. You can see there's a wee bit of confusion here.
Define what you mean by a 'batch job'. Then we can give you a better answer to that particular question.
You cannot use PL/SQL in DataStage jobs, at least not directly. If your PL/SQL in encapsulated in a Stored Procedure, than the STP or Stored Procedure Stage can let your sproc be used as a Source, Target or something in-between, whatever is appropriate. Stored Functions are used in DataStage just as they are anywhere else - by selecting them. IMHO, best to just do the work in DataStage directly and not use the tool to leverage your old PL/SQL code.
Also clarify your 'looping' question. If it is related to your PL/SQL question and to implicit or explicit cursors and 'looping' to process data, that's how a DataStage job works automagically. Records are read one at a time and the entire job is 'looped through' before another record is read. At least that's how it is done at a very high level. Anything other than that will need a specific question to get a more specific answer.
Hope that helps...
Define what you mean by a 'batch job'. Then we can give you a better answer to that particular question.
You cannot use PL/SQL in DataStage jobs, at least not directly. If your PL/SQL in encapsulated in a Stored Procedure, than the STP or Stored Procedure Stage can let your sproc be used as a Source, Target or something in-between, whatever is appropriate. Stored Functions are used in DataStage just as they are anywhere else - by selecting them. IMHO, best to just do the work in DataStage directly and not use the tool to leverage your old PL/SQL code.
Also clarify your 'looping' question. If it is related to your PL/SQL question and to implicit or explicit cursors and 'looping' to process data, that's how a DataStage job works automagically. Records are read one at a time and the entire job is 'looped through' before another record is read. At least that's how it is done at a very high level. Anything other than that will need a specific question to get a more specific answer.
Hope that helps...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
From a private email:
First off, private email is just that - for private conversations. This is where your questions go so everyone can answer and anyone can benefit.
Second, solve the problem. Don't worry about 'reduce the number of stages', that's not really a valid metric. Use what you need to do what you need to do. Kind of sappy, I know, but hopefully you get the point.
Thirdly, I don't have the time for a proper answer so a short one will have to do - from me. There will be others chiming in here now that your questions are here.
Whatever cursor fed your processing loop in the 'batch job' you need to put that same sourcing query in the first stage in your job, an Oracle stage I assume. You may also be able to put your 'specific condition' in there with it or it may need to be part of your subsequent processing in the 'middle' of the job. Without knowing what your particular condition is it's hard to say.
Then your target Oracle stage will perform any DML needed - in this case, the insert for each record sent to it.
NetappU wrote:Hi Hulett
I have to convert a batch job calling a PL sql into a single parallel job. i've sequence of insert statements like INSERT INTO FACT_CUSTOMER_GOALS(CUSTOMER_NAME, CUSTOMER_ID,CUSTOMER_TYPE,GOAL_PERIOD,GOALS,GEOGROUP,ACCOUNT_MANAGER)
VALUES (TRIM(x.END_USER_NAME), DECODE(IS_NUM(x.CUSTOMER_ID), 1, NULL, X.CUSTOMER_ID), 'END_USER', 'May'||' '||X.YEAR_CD, NVL(TRIM(x.MAY_GOALS), 0), TRIM(x.GEOGROUP), TRIM(x.ACCOUNT_MANAGER));
which is put in a loop and this has to be performed for a specific condition...
How to apply the same in a ds job to create a parallel job?
Can u plz...give me at least a high level detail about the same.
Which ds Fearture can help me reduce the number of stages in this case.
First off, private email is just that - for private conversations. This is where your questions go so everyone can answer and anyone can benefit.
Second, solve the problem. Don't worry about 'reduce the number of stages', that's not really a valid metric. Use what you need to do what you need to do. Kind of sappy, I know, but hopefully you get the point.
Thirdly, I don't have the time for a proper answer so a short one will have to do - from me. There will be others chiming in here now that your questions are here.
Whatever cursor fed your processing loop in the 'batch job' you need to put that same sourcing query in the first stage in your job, an Oracle stage I assume. You may also be able to put your 'specific condition' in there with it or it may need to be part of your subsequent processing in the 'middle' of the job. Without knowing what your particular condition is it's hard to say.
Then your target Oracle stage will perform any DML needed - in this case, the insert for each record sent to it.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers