One Star

Best Practices for managing database connections with Joblets

I've recently refactored part of an existing job into a Joblet (several tOracleInput components where included in the Joblet). When I did this, I noticed that there was a database connection component (tOracleConnection) that was not included inside the Joblet and it was still in the parent job. The Joblet worked fine though - it seems that the tOracleInput components inside the Joblet are still able to use the tOracelConnection object from the parent. However, when you look at these components the "Use Existing Connection" box is checked, but there is nothing in the component list (because the connection component does not exist inside the joblet).
It seems to me that it's a bad idea to have the connection component in the parent job, and then re-use that inside the joblet because there is no way to specify which connection component should be used. If a new job needs to re-use this joblet, and the new job contains more than one connection component, I think there will be unpredictable results - because the joblet would have no way of knowing which connection to use.
Has anyone else had a similar concern? If so, how would you recommend designing a Joblet? Would it be best to always include both the Connection component and the various "tOracleInput" components inside the same Joblet to avoid this confusion? Is there any advantage to using the connection in the parent job inside the Joblet?
Any thoughts or feedback is appreciated.
One Star

Re: Best Practices for managing database connections with Joblets

Include a connection component in the joblet, just as you have done.
In both the connection component in the parent, and in the joblet, check the "register a shared db connection" box
In the parent job, and assign a value , like "CONN1" , "CONN2", etc, for each shared name.
In the parent job, create a context variable context.SharedConnectionName.
In the joblet, use context.SharedConnectionName for the shared name. You do not have to setup a context in the joblet, as it inherits contexts automatically from the job that calls it.
Before the joblet is called, set context.SharedConnectionName to the appropriate value in the parent job.
When the joblet runs, ir will be "synched up" with the parent to use the context.SharedConnectionName shared connection.

One Star

Re: Best Practices for managing database connections with Joblets

Thanks for this trick.
Five Stars

Re: Best Practices for managing database connections with Joblets

I'm trying to reproduce this issue but no way. Could you post some sample code or screenshots?