DSXchange

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!

you can call multiple sql statements in "After" part of the Target

or you can write a stored procedure for the same and use the stored procedure in datastage jobs.

Thanks for your reply, Hemant_Kulkarni!

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?

Hemant_Kulkarni wrote:or you can write a stored procedure for the same and use the stored procedure in datastage jobs.

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?

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.

Any advice?

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.

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.