Accessing tables from a routine

[arun_im4u]

Hello,

I am writing a routine which will run before/after each job to capture audit logs for that job. The before routine will have to access a staging audit table retrieve latest info from it and the after routine will insert/update the audit logs to the table after each job run.

Any suggestions would be helpful.

Thanks

[chulett]

I wouldn't even consider doing all that 'after job' in a routine. I'd suggest the routine simply lands the data ready to be consumed and then have a 'regular' job that reads all of those files and does the actual load of them.

[arun_im4u]

That was my proposal too, but the client would like to explore the possibility of logging the audit to the table as each job completes. So, I would like to know if there is a way I can write to a table after each job run from an after job routine, rather than run a separate audit job after each job completes.

Thanks

[chulett]

Keep in mind the fact that you wouldn't *need* to run a separate audit job after each job completes, but could do so once after all jobs have run.

For sql from routines, you'd need to leverage ODBC via the BCI or BASIC Call Interface, not something for the faint hearted. Never mind the fact that the branded ODBC drivers that ship with the product are only for use in the GUI and "don't work" from a routine after 30 days. From what I understand, you'd need to purchase 3rd party drivers to implement this. Legally. Search the forums for BCI to get some ideas about what that would involve.