I have developed one job, and now I should exec one more user define sql sentence after the job. The sentense is not row based and it's bulk update and insert. The How can I do it? Which stage can I use?
Thanks for your attention!
One more sql sentence should be exec
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 50
- Joined: Tue Jan 02, 2007 1:40 am
Thanks for your reply, Hemant_Kulkarni!
your meaning is that I should write some post logic Routine?
Now in production env, I do like below.
I create a new job, includig one flat file stage and one odbc stage. the file stage read one file with only one row, and the odbc using user defined sql query. Then I add the job to the jobseq. If the previous job is finished ok, it will run.
Although the method is can do, but I think it's not a good method. It need one more permanent file.
Hemant_Kulkarni wrote:you can call multiple sql statements in "After" part of the Target.
your meaning is that I should write some post logic Routine?
If I add an stage which exec stored procedure, the stage need input column. And there are two mang rows, so how can I guarantee the stored procedure only exec one time?Hemant_Kulkarni wrote:or you can write a stored procedure for the same and use the stored procedure in datastage jobs.
Now in production env, I do like below.
I create a new job, includig one flat file stage and one odbc stage. the file stage read one file with only one row, and the odbc using user defined sql query. Then I add the job to the jobseq. If the previous job is finished ok, it will run.
Although the method is can do, but I think it's not a good method. It need one more permanent file.
Loucas
-
- Participant
- Posts: 12
- Joined: Wed Apr 19, 2006 7:41 am
Hi
U can use 'After SQL' in the target stage..That is enough for this situation
Your method is perfectly fine, just needs a little tweak. I wouldn't use 'After' anything for this, what would you do if only the after part failed? Rerun everything?
Take your Sequential to ODBC job and remove the Sequential stage. Add a stage variable, it is required to satisfy the compiler but you don't need to use it. Biggest thing to not forget is a constraint or the job will generate rows forever. For you "@OUTROWNUM=1" will work and deliver a single row to the ODBC stage. Hard-code the values it needs in the transformer, or use Job Parameters there if they are a run time variable.
Take your Sequential to ODBC job and remove the Sequential stage. Add a stage variable, it is required to satisfy the compiler but you don't need to use it. Biggest thing to not forget is a constraint or the job will generate rows forever. For you "@OUTROWNUM=1" will work and deliver a single row to the ODBC stage. Hard-code the values it needs in the transformer, or use Job Parameters there if they are a run time variable.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Long time no logon, Thanks for your advice!
chulett wrote:Your method is perfectly fine, just needs a little tweak. I wouldn't use 'After' anything for this, what would you do if only the after part failed? Rerun everything?
Take your Sequential to ODBC job and remove the Sequential stage. Add a stage variable, it is required to satisfy the compiler but you don't need to use it. Biggest thing to not forget is a constraint or the job will generate rows forever. For you "@OUTROWNUM=1" will work and deliver a single row to the ODBC stage. Hard-code the values it needs in the transformer, or use Job Parameters there if they are a run time variable.
Loucas