Before and After job subroutine

sheema · Post by **sheema** » Mon Jan 22, 2007 11:21 am

I have a job where i need to run a update statement on table xxx before the job runs and another update statement on table xxx after the job is completed.
I have put the update sttements in Before and After Job-sub routines respectively under the ExecTCL option.but i see a error

Output from UniVerse command was

"Table xxx: does not exist".

But i have that table existing in my schema.

Is this the way i am supposed to do or any other better options to execute these update statements.

Thanks
Sheema

DSguru2B · Post by **DSguru2B** » Mon Jan 22, 2007 11:23 am

Are you trying to update a Repository Table? If no then this is not the correct way. Have two jobs to do that. One before this present job in question and one after to do the before after updates respectively.

sheema · Post by **sheema** » Mon Jan 22, 2007 11:24 am

No,it is a table in my schema.

DSguru2B · Post by **DSguru2B** » Mon Jan 22, 2007 11:26 am

Read my previous reply again, i updated it. I was a little late in hitting submit but o well. Its there.

ArndW · Post by **ArndW** » Mon Jan 22, 2007 11:26 am

Is this "xxx" a DataStage hashed file accessed as a table or is it in your source/target database? If the latter it would explain why you are getting this error message.

sheema · Post by **sheema** » Mon Jan 22, 2007 11:28 am

It is a target table .
ok,i had that doubt whether i can update tables which are not repository tables.so should i have 2 jobs which run before and after the job respectively.
Is there any way that i can do it in a single job .

Thanks
sheema

narasimha · Post by **narasimha** » Mon Jan 22, 2007 11:39 am

Are you sure you want to execute your update statements via a DataStage Engine shell.
I asked this as you are using ExecTCL.

What is the database in question here?

sheema · Post by **sheema** » Mon Jan 22, 2007 11:44 am

my target database is oracle.

kcbland · Post by **kcbland** » Mon Jan 22, 2007 11:51 am

You need to use the before/after SQL tabs in your OCI stage. The before/after routine calls allow you to either run DataStage engine commands (ExecTCL) or operating system commands (ExecSH/ExecDOS) or custom written routines.

sheema · Post by **sheema** » Mon Jan 22, 2007 11:59 am

ok,kenneth than should i type in my update statements as it is in the before and after tabs of ORAOCI stage.or should i follow any other format.

narasimha · Post by **narasimha** » Mon Jan 22, 2007 12:02 pm

As Ken specified the before/after SQL tabs in your OCI stage will be ideal.
You can also type in the update commands directly there !

sheema · Post by **sheema** » Mon Jan 22, 2007 12:04 pm

Thanks every one for all the help.
I will try that .

Thanks
sheema